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

orastar發表於2020-03-04
SQL

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

  • 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
----------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   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
-----------------------------------------------------------------------------
| Id  | Operation       | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   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
-----------------------------------------------------------------------------------
| Id  | Operation       | Name      | Rows  | Bytes | Cost (%CPU)| Time      |
-----------------------------------------------------------------------------------
|   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';


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31442014/viewspace-2678272/，如需轉載，請註明出處，否則將追究法律責任。

相關文章