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/，如需轉載，請註明出處，否則將追究法律責任。

SQL訪問路徑和表連線

相關文章