本次主要講解執行計劃中表連線的幾個基礎概念

1、資料集處理
2、where後的條件型別(限制條件和連線條件、access和filter)
3、連線的型別

1、資料集處理

資料庫引擎支援的所有的連線方法都是每次只能處理兩個結果集。如下執行計劃所示，

explain plan for

select /*+ ordered use_nl(c,a,d) full(c) full(a) full(d) */count(1) from ht.c_cons c,ht.a_amt a,ht.c_cons_ds d where c.cons_no=a.cons_no and a.cons_no=d.cons_no;

select * from table(dbms_xplan.display);

Plan hash value: 327334001

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 15 | 1675K (1)| 05:35:01 |

| 1 | SORT AGGREGATE | | 1 | 15 | | |

| 2 | NESTED LOOPS | | 60586 | 887K| 1675K (1)| 05:35:01 |

| 3 | NESTED LOOPS | | 60586 | 591K| 670K (1)| 02:14:12 |

| 4 | TABLE ACCESS FULL| C_CONS | 10103 | 50515 | 17 (0)| 00:00:01 |

|* 5 | TABLE ACCESS FULL| A_AMT | 6 | 30 | 66 (0)| 00:00:01 |

|* 6 | TABLE ACCESS FULL | C_CONS_DS | 1 | 5 | 17 (0)| 00:00:01 |

----------------------------------------------------------------------------------

2、where後的條件型別

Oracle資料庫where後的條件型別分為：連線條件和限制條件2種型別。存在謂詞的操作步驟ID左邊有個*號。

謂詞型別分為：access和filter 2種。

條件型別：

連線條件： c.cons_no=a.cons_no

限制條件：and a.amt_ym='201701' and c.cons_name='wang1706'

謂詞型別：

ACCESS:

5 - access("C"."CONS_NAME"='wang1706')

6 - access("C"."CONS_NO"="A"."CONS_NO")

FILTER:

7 - filter("A"."AMT_YM"='201701')

ACCESS 和filter區別

FILTER: 訪問整個資料集，過濾不符合條件的行，只返回符合條件的行。該操作可能會做無用功。

ACCESS: 按照條件只訪問符合條件的行。官方解釋如下所示，

***************filter示例******************************

set autot trace

select count(1) from ht.c_cons c where org_name='guangdong';

Execution Plan

----------------------------------------------------------

Plan hash value: 605022138

-----------------------------------------------------------------------------

-----------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 9 | 17 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 9 | | |

|* 2 | TABLE ACCESS FULL| C_CONS | 842 | 7578 | 17 (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("ORG_NAME"='guangdong')

***************access示例****************************

select count(1) from ht.c_cons c where cons_no=101600;

Execution Plan

----------------------------------------------------------

Plan hash value: 1250298410

-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |

| 1 | SORT AGGREGATE | | 1 | 5 | | |

|* 2 | INDEX UNIQUE SCAN| SYS_C0011125 | 1 | 5 | 1 (0)| 00:00:01 |

-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("CONS_NO"=101600)

3、連線的型別

1 交叉連線

交叉連線(cross join),也叫笛卡爾乘積，是一種將一張表的所有記錄與另一張表的所有記錄進行組合的操作。

例如：

select count(1) from ht.c_cons c,ht.a_amt a where c.cons_no=a.cons_no;

select count(1) from ht.c_cons c,ht.a_amt a;

或

select count(1) from ht.c_cons c cross join ht.a_amt a;

2 條件連線

條件連線，交叉連線的一個子集，僅返回符合條件的結果。

select count(1) from ht.c_cons c,ht.a_amt a where c.cons_no between 600001 and 610001;

或

select count(1) from ht.c_cons c inner join ht.a_amt a on c.cons_no between 600001 and 610001;

或(關鍵字inner可被省略)

select count(1) from ht.c_cons c join ht.a_amt a on c.cons_no between 600001 and 610001;

3 等值連線

等值連線也稱為自然連線(natural join)，連線條件裡只有等於運算子。

select count(1) from ht.c_cons c,ht.a_amt a where c.cons_no=a.cons_no;

或

select count(1) from ht.c_cons c join ht.a_amt a on c.cons_no=a.cons_no;

4 自然連線

表跟自己連線

select emp.ename,mgr.ename from emp,emp mgr where emp.mgr=mgr.empno;

或

select emp.ename,mgr.ename from emp join emp mgr on emp.mgr = mgr.empno;

5 外連線

外連線是對條件連線的擴充套件，會返回一個表中的所有記錄，對於沒有匹配的相關記錄的欄位返回NULL。

左連線

select count(1) from ht.c_cons c,ht.a_amt a where c.cons_no=a.cons_no(+);

或

select count(1) from ht.c_cons c left join ht.a_amt a on c.cons_no=a.cons_no;

或

select count(1) from ht.c_cons c left outer join ht.a_amt a on c.cons_no=a.cons_no;

右連線

select count(1) from ht.c_cons c,ht.a_amt a where c.cons_no(+)=a.cons_no;

或

select count(1) from ht.c_cons c right join ht.a_amt a on c.cons_no=a.cons_no;

或

select count(1) from ht.c_cons c right outer join ht.a_amt a on c.cons_no=a.cons_no;

全連線

select count(1) from ht.c_cons c full join ht.a_amt a on c.cons_no=a.cons_no;

或

select count(1) from ht.c_cons c full outer join ht.a_amt a on c.cons_no=a.cons_no;

6 半連線

當一張表在另一張表找到匹配的記錄之後，半連線只返回其中一張表的記錄。

;

select cons_no,amt from ht.a_amt where cons_no in (

select cons_no from ht.c_cons where cons_name in (

'zhang360',

'zhang361',

'zhang362',

'zhang363'

)

) and AMT_YM='201701';

或

select cons_no,amt from ht.a_amt a where

exists( select 1 from ht.c_cons c where c.cons_no=a.cons_no

and c.cons_name in (

'zhang360',

'zhang361',

'zhang362',

'zhang363'

)

) and amt_ym='201701';

7 反連線

當一張表在另一張表找不到匹配的記錄之後，半連線只返回其中一張表的記錄。

select cons_no,amt from ht.a_amt where cons_no not in (

select cons_no from ht.c_cons where cons_name in (

'zhang360',

'zhang361',

'zhang362',

'zhang363'

)

) and AMT_YM='201701';

或

select cons_no,amt from ht.a_amt a where

not exists ( select 1 from ht.c_cons c where c.cons_no=a.cons_no

and c.cons_name in (

'zhang360',

'zhang361',

'zhang362',

'zhang363'

)

) and amt_ym='201701';

微課sql最佳化(13)、表的連線方法(2)-基礎概念

本次主要講解執行計劃中表連線的幾個基礎概念

1、資料集處理

2、where後的條件型別

3、連線的型別

相關文章