SQL訪問路徑和表連線

常用的SQL訪問路徑和表連線
資料訪問路徑：指明瞭oracle通過何種方法去訪問需要的資料，常用的可有以下三種

1. 全表掃描（Full Table Scans, FTS）,為實現全表掃描，Oracle讀取表中所有的行，並檢查每一行是否滿足語句的WHERE限制條件。Oracle順序地讀取分配給表的每個資料塊，直到讀到表的最高水線處(high water mark, HWM，標識表的當前使用的最後一個資料塊)。Oracle在全表掃描的時候，會一次讀入多個db block，每次讀取塊數由引數 db_block_multiblock_read_count設定，這極大的減少了I/O總次數，提高了系統的吞吐量，所以利用多塊讀的方法可以十分高效地實現全表掃描，而且只有在全表掃描的情況下才能使用多塊讀操作。在這種訪問模式下，每個資料塊只被讀一次。但很多情況下，我們只要獲取表中很少一部分資料，而表恰恰又很大，這樣通過全表掃描來檢索資料，效率就很低了，為此，oracle提供了通過索引來訪問資料的方法。
     SQL>  explain plan for select count(job) from emp;
SQL> select * from table(dbms_xplan.display);
--------------------------------------------------------------------
| Id  | Operation                          |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                    |     1      |     6     |  9619 |
|   1 |  SORT AGGREGATE        |                    |     1      |     6     |            |
|   2 |   TABLE ACCESS FULL   | EMP           |    10M |    57M |  9619 |

2. 通過ROWID的表存取（Table Access by ROWID或rowid lookup），where條件中直接使用rowid限制，或者第一步先訪問index，獲取rowid後再訪問表。獲取少量資料的時候，這種方法效率極高，因為rowid包含了相對檔案號，塊號，塊內行號，物件號，通過這些資訊，可以直接定位到資料檔案中讀取需要的塊。

     SQL> explain plan for select a.empno,ename from emp a where rowid='AAAHW7AABAAAMUiAAA';
SQL>  select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id  | Operation                                   |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                         |     1    |      16 |     1 |
|   1 |  TABLE ACCESS BY USER ROWID| EMP   |     1    |    16   |     1 |
             

3. 索引掃描（Index Scan）
     索引唯一掃描(index unique scan)
   索引範圍掃描(index range scan)
   索引全掃描(index full scan)
     索引快速掃描(index fast full scan)
     索引跳躍掃描(index skip scan)

表的連線join

表連線(Join)是一種試圖將兩個表結合在一起的謂詞，一次只能連線2個表，表連線也可以被稱為表關聯。
目前為止，無論連線操作符如何，典型的連線型別共有3種：
 排序合併連線(Sort Merge Join (SMJ) )
 巢狀迴圈(Nested Loops (NL) )
 雜湊連線(Hash Join)

排序合併連線(Sort Merge Join, SMJ)
內部連線過程：
1) 首先生成row source1需要的資料，然後對這些資料按照連線操作關聯列(如A.col3)進行排序。
2) 隨後生成row source2需要的資料，然後對這些資料按照與sort source1對應的連線操作關聯列(如B.col4)進行排序。
3) 最後兩邊已排序的行被放在一起執行合併操作，即將2個row source按照連線條件連線起來
下面是連線步驟的圖形表示：
 
                                   MERGE
                                  /         \
                             SORT    SORT
                                |            |
                     Row Source 1      Row Source 2

SQL>  explain plan for select /*+ user_nl(a,b) */ a.empno,ename,job from emp a,dept b where a.deptno
>b.deptno;
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
| Id  | Operation                         |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                   |     4      |    84    |     5 |
|   1 |  MERGE JOIN          |                             |     4      |    84    |     5 |
|   2 |   INDEX FULL SCAN    | PK_DEPT    |     4      |    12    |     1 |
|*  3 |   SORT JOIN          |                               |    21     |   378  |     4 |
|   4 |    TABLE ACCESS FULL  | EMP          |    21     |   378  |     2 |

SQL> explain plan for select /*+ use_nl(a,b) */ a.empno,ename,job from emp a,dept b where a.deptno>b
.deptno;
SQL> select * from table(dbms_xplan.display);
----------------------------------------------------------------------------------------------------
| Id  | Operation                          |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                    |     4      |    84    |     9 |
|   1 |  NESTED LOOPS        |                         |     4      |    84    |     9 |
|   2 |   INDEX FULL SCAN    | PK_DEPT     |     4      |    12    |     1 |
|*  3 |   TABLE ACCESS FULL  | EMP           |     1      |    18    |     2 |

巢狀迴圈(Nested Loops, NL)
內部連線過程：
1）Oracle 優化器根據基於規則RBO或基於成本CBO的原則，選擇兩個表中的一個作為驅動表，並指定其為外部表，再將另外一個表指定為內部表。
2） Oracle從外部表中讀取第一行,然後和內部表中的資料逐一進行對比，所有匹配的記錄放在結果集中。
3） Oracle讀取外部表中的第二行，再和內部表中的資料逐一進行對比，所有匹配的記錄新增到結果集中。重複上述步驟，直到外部表中的所有紀錄全部處理完。最後產生滿足要求的結果集。
SQL>  explain plan for select a.empno,ename,job from emp a,dept b where a.deptno=b.deptno;
SQL> select * from table(dbms_xplan.display);
-------------------------------------------------------------------------------------------
| Id  | Operation                          |  Name       | Rows       | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                        |    14      |   294 |     2 |
|   1 |  NESTED LOOPS             |                        |    14      |   294 |     2 |
|*  2 |   TABLE ACCESS FULL  | EMP              |    14      |   252 |     2 |
|*  3 |   INDEX UNIQUE SCAN  | PK_DEPT     |     1      |     3    |       |

雜湊連線(Hash Join, HJ)
 
這種連線是在oracle 7.3以後引入的，從理論上來說比NL與SMJ更高效，而且只用在CBO優化器中。
1） 較小的row source被用來在記憶體中構建hash table
2） 第2個row source中的每一條紀錄被用來被hansed，並與第一個row source生成hash table進行匹配
3） 匹配成功的記錄放到結果集中
當hash table比較大而不能全部容納在記憶體中時，這種查詢方法更為有用。這種連線方法也有NL連線中所謂的驅動表的概念，被構建為hash table與bitmap的表為驅動表，當被構建的hash table與bitmap能被容納在記憶體中時，這種連線方式的效率極高。要使雜湊連線有效，需要設定HASH_JOIN_ENABLED=TRUE，預設情況下該引數為TRUE，另外，不要忘了還要設定hash_area_size引數，以使雜湊連線高效執行，因為雜湊連線會在該引數指定大小的記憶體中執行，過小的引數會使雜湊連線的效能比其他連線方式還要低。
SQL> explain plan for select a.empno,ename,B.DNAME,job from emp a,dept b where a.deptno=b.deptno;
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
| Id  | Operation                           |  Name       | Rows  | Bytes   | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                     |  9999K|   295M|  9744 |
|*  1 |  HASH JOIN                      |                     |  9999K|   295M|  9744 |
|   2 |   TABLE ACCESS FULL  | DEPT          |     4      |    52    |     2 |
|   3 |   TABLE ACCESS FULL  | EMP            |    10M  |   171M|  9619 |

在哪種情況下用哪種連線方法比較好：

排序合併連線(Sort Merge Join, SMJ)：
對於非等值連線，這種連線方式的效率是比較高的，因此主要用於不等價連線，如、 >=，但是不包括 <>，如果在關聯的列上都有索引，效果更好。對於將2個較大的row source做連線，或者連線列缺乏可使用或可選擇性的索引，該連線方法比NL連線要好一些。但是如果sort merge返回的row source過大，則又會導致使用過多的rowid在表中查詢資料時，資料庫效能下降，因為過多的I/O。所有的表都需要排序。它為最優化的吞吐量而設計，並且在結果沒有全部找到前不返回資料。
巢狀迴圈(Nested Loops, NL)：
如果driving row source(外部表)比較小，並且在inner row source(內部表)上 有唯一索引，或有高選擇性非唯一索引時，使用這種方法可以得到較好的效率。
 NESTED LOOPS有其它連線方法沒有的的一個優點是：可以先返回已經 連線的行，而不必等待所有的連線操作理完才返回資料， 這可以實現快速的響應時間。

雜湊連線(Hash Join, HJ)：
這種方法是在oracle7後來引入的，使用了比較先進的連線理論， 一般來說，其效率應該好於其它2種連線，但是這種連線只能用在 CBO優化器中，而且需要設定合適的hash_area_size引數， 才能取得較好的效能。
 在2個較大的row source之間連線時會取得相對較好的效率，在一個 row source較小時則能取得更好的效率。
只能用於等值連線中

 

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