Oracle 左外串連的一些測試，Oracle串連測試

Oracle 左外串連的一些測試，Oracle串連測試

  為了更加深入左外串連，我們做一些測試，外串連的寫法有幾種形式，我們可以通過10053跟蹤到最終SQL轉換的形式。

--初始化資料

create table A

(
  id number,
  age number
);
create table b
(
  id number,
  age number
);
insert into A values(1,10);
insert into A values(2,20);
insert into A values(3,30);
insert into B values(1,10);
insert into B values(2,20);
commit;

--用10053找到最終轉換後的SQL

alter session set session_cached_cursors =0;
alter session set events '10053 trace name context forever, level  1';
explain plan for select * from A left join B on A.id = B.id and A.age > 5;
explain plan for select * from A left join B on A.id = B.id WHERE A.age > 5;
explain plan for select * from A left join B on A.id = B.id and b.age > 5;
explain plan for select * from A left join B on A.id = B.id where b.age > 5;
alter session set events '10053 trace name context off' ;


select * from A left join B on A.id = B.id and A.age > 5;
        ID        AGE         ID        AGE
---------- ---------- ---------- ----------
         1         10          1         10
         2         20          2         20
         3         30
--Final query after transformations:
SELECT "A"."ID" "ID", "A"."AGE" "AGE", "B"."ID" "ID", "B"."AGE" "AGE"
  FROM "GG_TEST"."A" "A", "GG_TEST"."B" "B"
 WHERE "A"."ID" = "B"."ID"(+)
   AND "A"."AGE" > CASE WHEN("B"."ID"(+) IS NOT NULL) THEN 5 ELSE 5 END


select * from A left join B on A.id = B.id WHERE A.age > 5;
        ID        AGE         ID        AGE
---------- ---------- ---------- ----------
         1         10          1         10
         2         20          2         20
         3         30
--Final query after transformations:
SELECT "A"."ID" "ID", "A"."AGE" "AGE", "B"."ID" "ID", "B"."AGE" "AGE"
  FROM "GG_TEST"."A" "A", "GG_TEST"."B" "B"
 WHERE "A"."AGE" > 5
   AND "A"."ID" = "B"."ID"(+);


select * from A left join B on A.id = B.id and b.age > 5; 
        ID        AGE         ID        AGE
---------- ---------- ---------- ----------
         1         10          1         10
         2         20          2         20
         3         30  
--Final query after transformations:
SELECT "A"."ID" "ID", "A"."AGE" "AGE", "B"."ID" "ID", "B"."AGE" "AGE"
  FROM "GG_TEST"."A" "A", "GG_TEST"."B" "B"
 WHERE "A"."ID" = "B"."ID"(+)
   AND "B"."AGE"(+) > 5

--這種形式你可以看到外串連失效，CBO還是非常聰明的
select * from A left join B on A.id = B.id where b.age > 5;
        ID        AGE         ID        AGE
---------- ---------- ---------- ----------
         1         10          1         10
         2         20          2         20
--Final query after transformations:
SELECT "A"."ID" "ID", "A"."AGE" "AGE", "B"."ID" "ID", "B"."AGE" "AGE"
  FROM "GG_TEST"."A" "A", "GG_TEST"."B" "B"
 WHERE "B"."AGE" > 5
   AND "A"."ID" = "B"."ID";


oracle的左串連與又串連怎用？

這個就是分主次表，兩個表匹配的時候，以那個表為主，上網百度一個教程看一下吧
 
對於ORACLE中左、右串連與左、右外串連的不同？

左串連和左外串連是一樣的。left join = left outer join。
T.TABLE_ID = TT.TABLE_ID(+)等同於left join
左串連是把左邊沒有與右邊相對應的資料一併顯示