1、Oracle驅動表Oracle驅動表（driving table/outer table）也叫做外部表，也叫外層表，是在多表關聯查詢中首先遍歷的表，驅動表的每一行都要到另一個表中尋找相應的記錄，然后計算返回最 終數據。驅動表的概念只在nested loops和hash join時存在。原則：驅動表一般是小表，但不絕對，看下邊驅動表一般是通過where條件篩選后剩余行數較少的表。如果表的一條記錄很長，占用幾個數據塊也適合做驅動表CBO和RBO中，對于驅動表的選擇是不同的，CBO中通過對統計信息的參考進行計算 來選擇驅動表，而RBO中按照既定原則選擇驅動表。RBO中，from后邊最右邊的表為驅動表

2、（from后邊表從右向左遍歷，where條件從下 向上遍歷）涉及驅動表的查詢，連接條件的索引很重要，驅動表連接字段可以沒有索引，但是被驅 動表需要被掃描驅動表經過篩選后剩余條數的遍數，所以被驅動表的連接字段上有一條 索引是非常重要的。分析：假設a表10行記錄，b表1000行記錄，兩個表都有id列，查詢時使用id列進行關聯 Select * from a,b where a.id=b.id and a.id=100;A表作為驅動表比較合適，假設a.id=100只有1行，即使全表掃描a表也就幾個塊，假設a 表占用10個塊。B表的id假如非唯一，如果b表的id列有索引，b表占用100個塊，每個塊10

3、行記錄，id 列索引占用10個塊，并且id為100有2條記錄，在兩個塊中 那么這條語句的成本（以塊計算，下同）：A表（10個塊）*b表索引（10個塊）+b表id為100的2個塊=102個塊如果b表沒有索引，成本為：A表（10個塊）*b表（100個塊）=1000個塊如果a，b表都沒有索引，可以看出不管哪個表作為驅動表，語句的執行成本都是一樣的。如果a，b表id列都有索引，a表id列索引占2個塊，成本為：A表id列索引（2個塊）*b表id列索引（10個塊）+ b表id為100的2個塊=22個塊如果B表的記錄很長，可以作為驅動表的情況比較復雜，大家可以自己想象適合的場景。可以看出，在連接中，如果連接

4、列有索引是多么的重要。實驗支撐SQL create table a(id,name) as select object_id,object_name from all_objects where rownum SQL create table b as select * from all_objects ;Table created.SQL select count(*) from a;COUNT(*)199SQL select count(*) from bSQLCOUNT(*)89083SQLSQL exec dbms_stats.gather_table_stats(TEST,A);PL

5、/SQL procedure successfully completed.SQLSQL exec dbms_stats.gather_table_stats(TEST,B);PL/SQL procedure successfully completed.兩個表都沒有索引Select count(*) from a,b where a.id=b.object_idAnd a.id=53執行計劃：(B表驅動)SQL Select count(*) from a,b where a.id=b.object_idAnd a.id=53/COUNT(*)Execution PlanPlan hash

6、value: 319234518| Id| Operation| Name | Rows| Bytes | Cost (%CPU)| Time|0| SELECT STATEMENT |1 |9 |420(1)| 00:00:01|1| SORT AGGREGATE|1 |9 |* 2| HASH JOIN|1 |9 |420(1)| 00:00:01|* 3| TABLE ACCESS FULL| B |1 |5 |417(1)| 00:00:01 |* 4| TABLE ACCESS FULL| A |1 |4 |3(0)| 00:00:01 |Predicate Information

7、(identified by operation id):- access(A.ID=B.OBJECT_ID)- filter(B.OBJECT_ID=53)4-filter(A.ID=53)Statistics101506005425432recursive callsdb block getsconsistent getsphysical readsredo sizebytes sent via SQL*Net to clientbytes received via SQL*Net from clientSQL*Net roundtrips to/from client0 sorts (m

8、emory)0 sorts (disk)rows processedSQLA表作為驅動表SQL Select /*+ ordered use_nl(a) */count(*) from a,b where a.id=b.object_id 2 And a.id=53;COUNT(*)1row selected.Execution PlanPlan hash value: 1397777030| Id| Operation| Name | Rows| Bytes | Cost (%CPU)| Time|0| SELECT STATEMENT |1 |9 |420(1)| 00:00:01|1|

9、SORT AGGREGATE|1 |9 |* 2| HASH JOIN|1 |9 |420(1)| 00:00:01|* 3| TABLE ACCESS FULL| A |1 |4 |3(0)| 00:00:01 |* 4| TABLE ACCESS FULL| B |1 |5 |417(1)| 00:00:01 |Predicate Information (identified by operation id):- access(A.ID=B.OBJECT_ID)3-filter(A.ID=53)- filter(B.OBJECT_ID=53)Statistics1 recursive c

10、alls 0 db block gets1506 consistent gets 0 physical reads 0 redo sizebytes sent via SQL*Net to clientbytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client0 sorts (memory) 0 sorts (disk)rows processedSQL發現上面兩個語句的代價是一樣的/*+ Ordered use_nl(table_name) */-使用 hint 強制表作為驅動表，只使用 /*+use_

11、nl(table1,table2)是無法強制驅動表順序的，另外，這里使用的use_nl，但是走的是hash join，說明在沒有索引的情況下，oracle優化器更傾向hash join，因為nested loop并不一定 會提前返回數據。執行計劃下，hash loop下第一個表為驅動表表B object_id列有索引的情況SQL create index id_b_object_id on b(object_id);Index created.SQL exec dbms_stats.gather_table_stats(ownname = TEST,TABNAME = B,CASCADE =

12、TRUE);PL/SQL procedure successfully completed.SQL執行計劃：SQL Select count(*) from a,b where a.id=b.object_idAnd a.id=53;COUNT(*)row selected.Execution PlanPlan hash value: 3168189658| Id | Operation| Name| Rows|-| Bytes | Cost (%CPU)|Time| 0 | 00:00:01 | 1 | 1SELECT STATEMENT|SORT AGGREGATE|-1|1|9|9|4(

13、0)| |2 |00:00:01MERGE JOIN CARTESIAN|1|9|4(0)|* 3 | 00:00:01TABLE ACCESS FULL | A|1|4|3(0)|4 |00:00:01BUFFER SORT|1|5|1(0)|* 5 | 00:00:01INDEX RANGE SCAN | ID_B_OBJECT_ID | |1|5|1(0)|Predicate Information (identified by operation id):3-filter(A.ID=53)5- access(B.OBJECT_ID=53)Statistics92 recursive c

14、alls0 db block gets134 consistent gets23 physical reads0 redo sizebytes sent via SQL*Net to clientbytes received via SQL*Net from client2 SQL*Net roundtrips to/from client12 sorts (memory)0 sorts (disk)rows processedSQL發現執行計劃并沒有使用nested loop和hash join，不過走索引后，執行代價明顯減少。Merge join發生了排序，如果內存夠用還好，不夠用就比較耗

15、時了。強制hashA表驅動SQL Select /*+ use_hash(a,b) */count(*) from a,b where a.id=b.object_idAnd a.id=53;COUNT(*)11 row selected.Execution PlanPlan hash value: 895278611-| Id| Operation| Name| Rows| Bytes| Cost(%CPU)| Time-|0 |1SELECT STATEMENT |1 |9 |4(0)| 00:00:01|11| SORT AGGREGATE|1|9|* 2| HASH JOIN|1|9|

16、4(0)|00:00:01 |*13 |TABLE ACCESS FULL| A|1 |4 |3(0)| 00:00:01|*|4 |INDEX RANGE SCAN | ID_B_OBJECT_ID |1 |5 |1(0)| 00:00:01Predicate Information (identified by operation id):- access(A.ID=B.OBJECT_ID)- filter(A.ID=53)- access(B.OBJECT_ID=53)Statisticsrecursive calls0 db block getsconsistent gets0 phy

17、sical reads0 redo sizebytes sent via SQL*Net to clientbytes received via SQL*Net from clientSQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processedSQL-強制使用hash join, a表默認變為了驅動表，執行代價很低，符合要求B表驅動SQL Select /*+ ordered use_hash(b) */count(*) from a,b where a.id=b.object_id 2 And

18、a.id=53;COUNT(*)11 row selected.Execution PlanPlan hash value: 895278611| Id | Operation| Name| Rows | Bytes | Cost (%CPU)| Time|0 | SELECT STATEMENT |11 |9 |4(0)| 00:00:01|1 | SORT AGGREGATE|1|1|9|* 2 | HASH JOIN|1|9|4(0)|00:00:01 |* 3 | TABLE ACCESS FULL| A|11 |4 |3(0)| 00:00:01|* 4 |INDEX RANGE S

19、CAN | ID_B_OBJECT_ID |1 |5 |1(0)| 00:00:01|Predicate Information (identified by operation id):- access(A.ID=B.OBJECT_ID)- filter(A.ID=53)- access(B.OBJECT_ID=53)Statistics1recursive calls0db block gets5consistent gets0physical reads0redo size542bytes sent via SQL*Net to client543bytes received via S

20、QL*Net from client2SQL*Net roundtrips to/from client0sorts (memory)0sorts (disk)1rows processedSQL發現有索引，并且有統計信息的情況下，無法強制B表作為驅動表，oracle對hint進行了忽 略。刪除統計信息試試：SQL EXEC dbms_stats.delete_table_stats(user,B,cascade_parts = TRUE);PL/SQL procedure successfully completedSQL EXEC dbms_stats.delete_table_stats

21、(user,A,cascade_parts =TRUE);PL/SQL procedure successfully completedSQL-測試發現仍然不能將B表作為驅動表，修改optimizer_mode為rule alter session set optimizer_mode=rule;SQL Select /*+ ordered use_nl(b) */count(*) from a,b where a.id=b.object_id 2 And object_id=53;-發現仍然不能將B表作為驅動表強制 nested loopSQL Select /*+ ordered use_

22、nl(b) */count(*) from a,b where a.id=b.object_id 2 And object_id=53;COUNT(*)11 row selected.Execution PlanPlan hash value: 1183094437| Id| Operation|Name| Rows| Bytes-| Cost (%CPU)| Time|0 |1SELECT STATEMENT |1 |26 |4(0)| 00:00:01|1| SORT AGGREGATE|1|26 |2 | NESTED LOOPS00:00:01 |1|26 |4(0)|* 3 |1TA

23、BLE ACCESS FULL|A|1 |13 |3(0)| 00:00:01|* 4 |INDEX RANGE SCAN| ID_B_OBJECT_ID |1 |13 |1(0)| 00:00:01Predicate Information (identified by operation id):- filter(A.ID=53)4- access(OBJECT_ID=53)Note-dynamic statistics used: dynamic sampling (level=2)Statistics10 recursive calls0 db block gets73 consist

24、ent getsphysical reads0 redo sizebytes sent via SQL*Net to clientbytes received via SQL*Net from clientSQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processedSQL-代價和hash join差不多，另外，即使強制B表作為驅動表，仍然不能將B表作為驅動表。兩個都有索引的情況SQL create index id_a_id on a(id);Index created.SQL exec dbms

25、_stats.gather_table_stats(user,A,CASCADE=TRUE);PL/SQL procedure successfully completed.SQL exec dbms_stats.gather_table_stats(user,B,cascade = true);PL/SQL procedure successfully completed.SQLSQL Select /*+ ordered use_nl(b) */count(*) from a,b where a.id=b.object_id 2 And object_id=53;COUNT(*)11 row selected.Elapsed: 00:00:00.01Execution PlanPlan h