引言：hints翻譯成中文就是提示，暗示的意思，它在資料庫中作用就是更改SQL語句的執行方式，你可以使用hints強制sql按照你所設定的方式執行sql，一般用來做效能診斷和調優，不建議在開發中使用。

1.寫一條SQL，使它透過全表掃描方式的效率優於索引訪問，分別給出各自的執行計劃。

LEO1@LEO1> create table leo1 as select * from dba_objects; 建立leo1表

Table created.

LEO1@LEO1> create index idx_leo1 on leo1(object_id); 在這個object_id列上建立索引

Index created.

LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO1',cascade=>true); 分析表和索引

PL/SQL procedure successfully completed.

LEO1@LEO1> select count(*) from leo1; 表上有71958行記錄

COUNT(*)

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

71958

LEO1@LEO1> select /*+ full(leo1) */ * from leo1 where object_id>100;

71859 rows selected.

Execution Plan

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

Plan hash value: 2716644435

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

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

| 0 | SELECT STATEMENT | | 71862 | 6807K| 287 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| LEO1 | 71862 | 6807K| 287 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_ID">100)

Statistics

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

1 recursive calls

0 db block gets

5762 consistent gets 5762次一致性讀

0 physical reads

0 redo size

3715777 bytes sent via SQL*Net to client

53214 bytes received via SQL*Net from client

4792 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

71859 rows processed

LEO1@LEO1> select /*+ index(leo1 idx_leo1) */ * from leo1 where object_id>100;

71859 rows selected.

Execution Plan

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

Plan hash value: 1434365503

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

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

| 0 | SELECT STATEMENT | | 71862 | 6807K| 1232 (1)| 00:00:15 |

| 1 | TABLE ACCESS BY INDEX ROWID| LEO1 | 71862 | 6807K| 1232 (1)| 00:00:15 |

|* 2 | INDEX RANGE SCAN | IDX_LEO1 | 71862 | | 160 (0)| 00:00:02 |

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID">100)

Statistics

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

1 recursive calls

0 db block gets

10735 consistent gets 10735次一致性讀

0 physical reads

0 redo size

8241805 bytes sent via SQL*Net to client

53214 bytes received via SQL*Net from client

4792 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

71859 rows processed

小結：上面的比較來看訪問相同記錄行，全表掃描並不總是效能最差的。為什麼會這樣呢，這要看提取的記錄數佔總記錄數的比例是大還是小。一般來講小於總體20%時走索引的效率高（並不絕對），如果你檢索的記錄數很大，其實不用先掃描索引塊在訪問資料塊，直接全掃描資料塊反而效率更高。因為走索引訪問一個資料塊需要2次IO，走全表掃描訪問一個資料塊需要1次IO，代價顯而易見了！

2.自己構造三條關聯查詢的SQL，分別適用於nested loop join，hash join,merge join 關聯,對於每條sql語句，分別透過hint產生其它兩種關聯方式的執行計劃，並比較效能差異。

表關聯-Nested Loop Join 巢狀迴圈關聯

LEO1@LEO1> create table a as select * from dba_objects; a是一張大表

Table created.

LEO1@LEO1> create table b as select * from dba_objects where rownum<99; b是一張小表（外部表）

Table created.

LEO1@LEO1> create index idx_a on a(object_id); 在a上建一個索引，鍵值重複率較低

Index created.

LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','a',cascade=>true); a表和索引都分析一下

PL/SQL procedure successfully completed.

LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','b',cascade=>true); b表也分析一下

PL/SQL procedure successfully completed.

LEO1@LEO1> set autotrace trace explain;

LEO1@LEO1> select a.* from a,b where a.object_id=b.object_id;

Execution Plan

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

Plan hash value: 3337251606

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

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

| 0 | SELECT STATEMENT | | 98 | 9800 | 199 (0)| 00:00:03 |

| 1 | NESTED LOOPS | | | | | |

| 2 | NESTED LOOPS | | 98 | 9800 | 199 (0)| 00:00:03 |

| 3 | TABLE ACCESS FULL | B | 98 | 294 | 3 (0)| 00:00:01 |

|* 4 | INDEX RANGE SCAN | IDX_A | 1 | | 1 (0)| 00:00:01 |

| 5 | TABLE ACCESS BY INDEX ROWID| A | 1 | 97 | 2 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - access("A"."OBJECT_ID"="B"."OBJECT_ID") 謂詞條件

資料訪問：全表掃描小表b拿出一條記錄，去大表a中匹配（索引掃描a表），巢狀迴圈遍歷a，如果找到匹配記錄，就去a表rowid所在的資料塊上取出，最後需要的就是a表裡面整個資料。

使用場景：1.外部表是一張小表例 b表

2.關聯的表是一張大表，並在關聯欄位上建立索引，最好是主鍵例 a 表

3.索引鍵值重複率低

Hash Join

LEO1@LEO1> select /*+ use_hash(a,b) */ a.* from a,b where a.object_id=b.object_id;

98 rows selected.

Execution Plan

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

Plan hash value: 4090908061

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

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

| 0 | SELECT STATEMENT | | 98 | 9800 | 291 (1)| 00:00:04 |

|* 1 | HASH JOIN | | 98 | 9800 | 291 (1)| 00:00:04 |

| 2 | TABLE ACCESS FULL| B | 98 | 294 | 3 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| A | 71955 | 6816K| 287 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")

Statistics

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

176 recursive calls

0 db block gets

1060 consistent gets

2 physical reads

0 redo size

5504 bytes sent via SQL*Net to client

590 bytes received via SQL*Net from client

8 SQL*Net roundtrips to/from client

5 sorts (memory)

0 sorts (disk)

98 rows processed

MERGE Join

LEO1@LEO1> select /*+ use_merge(a,b) */ a.* from a,b where a.object_id=b.object_id;

98 rows selected.

Execution Plan

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

Plan hash value: 3307526271

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

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

| 0 | SELECT STATEMENT | | 98 | 9800 | 1238 (1)| 00:00:15 |

| 1 | MERGE JOIN | | 98 | 9800 | 1238 (1) | 00:00:15 |

| 2 | TABLE ACCESS BY INDEX ROWID| A | 71955 | 6816K| 1234 (1) | 00:00:15 |

| 3 | INDEX FULL SCAN | IDX_A | 71955 | | 160 (0) | 00:00:02 |

|* 4 | SORT JOIN | | 98 | 294 | 4 (25)| 00:00:01 |

| 5 | TABLE ACCESS FULL | B | 98 | 294 | 3 (0) | 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - access("A"."OBJECT_ID"="B"."OBJECT_ID")

filter("A"."OBJECT_ID"="B"."OBJECT_ID")

Statistics

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

1 recursive calls

0 db block gets

22 consistent gets

0 physical reads

0 redo size

5388 bytes sent via SQL*Net to client

590 bytes received via SQL*Net from client

8 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

98 rows processed

比較小結：大家從cost值上看到走nested loops要比後2個關聯方式代價小，說明CBO最佳化器的選擇還是正確的。

表關聯-Hash Join 雜湊關聯

應用場景：1.一個是大表，一個是小表，兩個表進行關聯操作

2.當兩個表沒有索引時進行關聯，使用hash方式匹配效率較高

3.如果兩個表有索引又進行了hash關聯，那麼雜湊完後，結果只受雜湊列表影響，不受索引影響了

LEO1@LEO1> drop table a purge; 刪除a表重新建

Table dropped.

LEO1@LEO1> drop table b purge; 刪除b表重新建

Table dropped.

LEO1@LEO1> create table a as select * from dba_objects; a是一張大表，無索引

Table created.

LEO1@LEO1> create table b as select * from dba_objects where rownum<1000; b是一張小表，無索引

Table created.

LEO1@LEO1> select a.* from a,b where a.object_id=b.object_id; a表b表進行關聯操作

999 rows selected. 返回999行

Execution Plan

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

Plan hash value: 4090908061

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

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

| 0 | SELECT STATEMENT | | 999 | 214K| 294 (1)| 00:00:04 |

|* 1 | HASH JOIN | | 999 | 214K| 294 (1)| 00:00:04 |

| 2 | TABLE ACCESS FULL| B | 999 | 12987 | 6 (0)| 00:00:01 | b表小代價也小

| 3 | TABLE ACCESS FULL| A | 83813 | 16M| 287 (1)| 00:00:04 | a表大代價相對也大

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

Predicate Information (identified by operation id):

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

1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

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

532 recursive calls

0 db block gets

1261 consistent gets

1038 physical reads

0 redo size

51276 bytes sent via SQL*Net to client

1250 bytes received via SQL*Net from client

68 SQL*Net roundtrips to/from client

5 sorts (memory)

0 sorts (disk)

999 rows processed

資料訪問：全表掃描a、b表，先把小表b做雜湊後build到記憶體中，在對大表a做雜湊，然後從大表a中取資料到小表b中比較，最後把匹配的資料返回給使用者，這種雜湊匹配效率高。（我們也可以叫做2個資料集的比較，雜湊完後oracle會把資料分佈到一個個雜湊區，然後是大資料集雜湊區與小資料集雜湊區比較，也就是n對n比較，不像nested loops 是1：n比較，因此效能好）

Nested loops

LEO1@LEO1> select /*+ use_nl(a,b) */ a.* from a,b where a.object_id=b.object_id;

999 rows selected.

Execution Plan

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

Plan hash value: 4193326952

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

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

| 0 | SELECT STATEMENT | | 999 | 214K| 285K (1) |00:57:09 |

| 1 | NESTED LOOPS | | 999 | 214K| 285K (1) | 00:57:09 |

| 2 | TABLE ACCESS FULL| B | 999 | 12987 | 6 (0) | 00:00:01 |

|* 3 | TABLE ACCESS FULL| A | 1 | 207 | 286 (1) | 00:00:04 |

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

Predicate Information (identified by operation id):

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

3 - filter("A"."OBJECT_ID"="B"."OBJECT_ID") 謂詞條件filter（過濾）就代表是全表掃描

Note

-----

- dynamic sampling used for this statement (level=2) 動態取樣，級別越高，採集資料越多，結果越精確，但消耗資源也越多

Statistics

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

0 recursive calls

0 db block gets

1029120 consistent gets 巢狀迴圈比較，比hash多出了816倍一致性讀

0 physical reads

0 redo size

51276 bytes sent via SQL*Net to client

1250 bytes received via SQL*Net from client

68 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

999 rows processed

Merge Join

LEO1@LEO1> select /*+ use_merge(a,b) */ a.* from a,b where a.object_id=b.object_id;

999 rows selected.

Execution Plan

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

Plan hash value: 3028542103

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

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

| 0 | SELECT STATEMENT | | 999 | 214K| | 4066 (1)| 00:00:49 |

| 1 | MERGE JOIN | | 999 | 214K| | 4066 (1)| 00:00:49 |

| 2 | SORT JOIN | | 999 | 12987 | | 7 (15)| 00:00:01 |

| 3 | TABLE ACCESS FULL| B | 999 | 12987 | | 6 (0)| 00:00:01 |

|* 4 | SORT JOIN | | 83813 | 16M| 39M| 4059 (1)| 00:00:49 |

| 5 | TABLE ACCESS FULL| A | 83813 | 16M| | 287 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

4 - access("A"."OBJECT_ID"="B"."OBJECT_ID")

filter("A"."OBJECT_ID"="B"."OBJECT_ID")

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

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

7 recursive calls

0 db block gets

1141 consistent gets

0 physical reads

0 redo size

51142 bytes sent via SQL*Net to client

1250 bytes received via SQL*Net from client

68 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

999 rows processed

資料訪問：先對a、b表進行整體排序，在逐條進行比較，cost值比hash join大了13倍，Rows列比hash join多返回了兩行，這些都證明了merge Join 沒有hash join效能好。

表關聯-Merge Join 合併關聯

Merge join場合：如果2個表都是經過整體排序後的，那麼它們在關聯的時候就會走Merge join。

我們還用如上的a、b表做測試比較

LEO1@LEO1> select * from (select * from a order by object_id) a,(select * from b order by object_id) b where a.object_id=b.object_id;

999 rows selected.

Execution Plan

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

Plan hash value: 2924767385

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

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

| 0 | SELECT STATEMENT | | 83813 | 33M| | 4066 (1)| 00:00:49 |

| 1 | MERGE JOIN | | 83813 | 33M| | 4066 (1)| 00:00:49 |

| 2 | VIEW | | 83813 | 16M| | 4059 (1)| 00:00:49 |

| 3 | SORT ORDER BY | | 83813 | 16M| 19M| 4059 (1)| 00:00:49 |

| 4 | TABLE ACCESS FULL| A | 83813 | 16M| | 287 (1)| 00:00:04 |

|* 5 | SORT JOIN | | 999 | 201K| | 7 (15)| 00:00:01 |

| 6 | TABLE ACCESS FULL | B | 999 | 201K| | 6 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

5 - access("A"."OBJECT_ID"="B"."OBJECT_ID")

filter("A"."OBJECT_ID"="B"."OBJECT_ID")

Note

-----

- dynamic sampling used for this statement (level=2)

Statistics

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

291 recursive calls

0 db block gets

1169 consistent gets

0 physical reads

0 redo size

85714 bytes sent via SQL*Net to client

1250 bytes received via SQL*Net from client

68 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

999 rows processed

小結：結合上面的結果由於需要先排序，則返回的行數又多了，從而增加了等待時間和代價，通常merge join的效果並不是很好因為代價太大了。

表關聯-leading( ) 指定表訪問的順序

LEO1@LEO1> create table c as select * from dba_objects where rownum<100; 建立c表

Table created.

LEO1@LEO1> select /*+ leading(c b a) */ * from a,b,c where a.object_id=b.object_id and b.object_id=c.object_id; 利用上面a、b表做三表關聯

99 rows selected.

Execution Plan

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

Plan hash value: 455705007

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

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

| 0 | SELECT STATEMENT | | 99 | 61479 | 298 (2)| 00:00:04 |

|* 1 | HASH JOIN | | 99 | 61479 | 298 (2)| 00:00:04 |

|* 2 | HASH JOIN | | 99 | 40986 | 10 (10)| 00:00:01 |

| 3 | TABLE ACCESS FULL| C | 99 | 20493 | 3 (0)| 00:00:01 |

| 4 | TABLE ACCESS FULL| B | 999 | 201K| 6 (0)| 00:00:01 |

| 5 | TABLE ACCESS FULL | A | 83813 | 16M| 287 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

1 - access("A"."OBJECT_ID"="B"."OBJECT_ID")

2 - access("B"."OBJECT_ID"="C"."OBJECT_ID")

Note

-----

- dynamic sampling used for this statement (level=2)

小結：我們看到執行計劃中訪問表的順序（C->B->A）就是我們指定好的順序leading(c b a)，說明hints生效。

3.透過append hint來插入資料，演示它和普通插入資料的效能比較。

LEO1@LEO1> set timing on 顯示執行時間

LEO1@LEO1> insert into leo1 select * from leo1; 普通載入資料，會掃描空閒空間加以利用

71958 rows created.

Elapsed: 00:00:00.89 執行了00.89秒

Execution Plan

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

Plan hash value: 2716644435

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

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

| 0 | INSERT STATEMENT | | 71958 | 6816K| 287 (1)| 00:00:04 |

| 1 | LOAD TABLE CONVENTIONAL | LEO1 | | | | |

| 2 | TABLE ACCESS FULL | LEO1 | 71958 | 6816K| 287 (1)| 00:00:04 |

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

Statistics

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

518 recursive calls

15560 db block gets

3693 consistent gets 產生了3693次一致性讀

4 physical reads

13892928 redo size 產生了13892928大小redo日誌

843 bytes sent via SQL*Net to client

792 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

71958 rows processed

LEO1@LEO1> rollback; 回滾

Rollback complete.

Elapsed: 00:00:00.10

LEO1@LEO1> insert /*+ append */ into leo1 select * from leo1; 直接載入資料，不掃描空閒空間，直接定位HWM載入資料，效率高

71958 rows created.

Elapsed: 00:00:00.36 執行了00.36秒

Execution Plan

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

ERROR:

ORA-12838: cannot read/modify an object after modifying it in parallel（無法在並行模式下修改之後讀寫物件）

SP2-0612: Error generating AUTOTRACE EXPLAIN report 生成執行計劃報告時出錯

Statistics

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

340 recursive calls

2441 db block gets

2253 consistent gets 產生了2253次一致性讀

0 physical reads

2268672 redo size 產生了2268672大小redo日誌

829 bytes sent via SQL*Net to client

806 bytes received via SQL*Net from client

3 SQL*Net roundtrips to/from client

2 sorts (memory)

0 sorts (disk)

71958 rows processed

LEO1@LEO1> rollback; 回滾

Rollback complete.

Elapsed: 00:00:00.09

小結：從比較結果一眼看出，直接載入的效率要比普通載入高很多，時間上差不多快了一倍。原因有以下2點：

第一點：普通載入會掃描空閒空間，利用這些空閒空間插入資料，直接載入不掃描空閒空間直接定位到HWM直接載入資料，從而效率較高

第二點：可以看出普通載入的一致性讀和redo量都要大於直接載入，產生這些資料量也是要消耗資源的，所以普通載入沒有直接載入效能好。

4.用cardinality hint來模擬表中的資料，寫一條SQL語句並給出它的執行計劃。

名詞解釋：cardinality這個關鍵字在10g執行計劃裡被rows代替，實際上兩個詞指的是一個東西。

Cardinality（基數）在執行計劃中表示每一步操作返回的記錄數，這個數是oracle估算出來的並不是真實返回的記錄數，CBO根據這個值計算權重，來選擇使用哪種方式來訪問資料。

作用：1.我們一般使用“cardinality”hints來比較不同數量返回值在執行計劃中效率。

2.當有特殊場景不容易模擬出來的時候，我們可以使用“cardinality”hints方式來輕鬆解決

LEO1@LEO1> select count(*) from leo1; leo1表有71958條記錄

COUNT(*)

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

71958

LEO1@LEO1> create table leo2 as select * from dba_objects; 建立leo2表

Table created.

LEO1@LEO1> insert into leo2 select * from leo2; 在插入一次，為了比leo1表記錄數多一倍，好做比較

71960 rows created.

LEO1@LEO1> create index idx_leo2 on leo2(object_id); 在object_id欄位上建立索引

Index created.

LEO1@LEO1> select count(*) from leo2; 現在有143920條記錄

COUNT(*)

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

143920

LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO2',cascade=>true); leo2和索引都做分析

PL/SQL procedure successfully completed.

LEO1@LEO1> set autotrace traceonly;

LEO1@LEO1> select * from leo1,leo2 where leo1.object_id=leo2.object_id;

143916 rows selected. 返回143916行

Execution Plan

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

Plan hash value: 2436308224

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

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

| 0 | SELECT STATEMENT | | 141K| 26M| | 2291 (1) | 00:00:28 |

|* 1 | HASH JOIN | | 141K| 26M| 7664K| 2291 (1) | 00:00:28 |

| 2 | TABLE ACCESS FULL| LEO1 | 71958 | 6816K| | 587 (1) | 00:00:08 |

| 3 | TABLE ACCESS FULL| LEO2 | 143K| 13M| | 588 (1) | 00:00:08 |

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

Predicate Information (identified by operation id):

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

1 - access("LEO1"."OBJECT_ID"="LEO2"."OBJECT_ID") 謂詞條件2個索引欄位相等

Statistics

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

1 recursive calls

0 db block gets

13672 consistent gets 全表掃描產生了13672個一致性讀

2134 physical reads

0 redo size

12630296 bytes sent via SQL*Net to client

106058 bytes received via SQL*Net from client

9596 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

143916 rows processed

我們強制指定leo1表返回100行，來看執行計劃如何選擇訪問資料的方式

LEO1@LEO1> select /*+ cardinality(leo1 100) */ * from leo1,leo2 where leo1.object_id=leo2.object_id;

143916 rows selected. 也返回143916行，返回值沒有按執行計劃走

Execution Plan

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

Plan hash value: 2751515442

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

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

| 0 | SELECT STATEMENT | | 197 | 38218 | 887 (1)| 00:00:11 |

| 1 | NESTED LOOPS | | | | | |

| 2 | NESTED LOOPS | | 197 | 38218 | 887 (1)| 00:00:11 |

| 3 | TABLE ACCESS FULL | LEO1 | 100 | 9700 | 587 (1)| 00:00:08 |

|* 4 | INDEX RANGE SCAN | IDX_LEO2 | 2 | | 1 (0)| 00:00:01 |

| 5 | TABLE ACCESS BY INDEX ROWID| LEO2 | 2 | 194 | 3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - access("LEO1"."OBJECT_ID"="LEO2"."OBJECT_ID")

Statistics

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

1 recursive calls

0 db block gets

177213 consistent gets 因為有索引掃描所以有177213個一致性讀

2134 physical reads 物理讀都是一樣的，說明只有記憶體IO增加了

0 redo size

7727088 bytes sent via SQL*Net to client

106058 bytes received via SQL*Net from client

9596 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

143916 rows processed

資料訪問：全表掃描小表leo1（因為強制指定返回100行就認為是小表）拿出一條記錄，去大表leo2中匹配（索引掃描leo2表，因為當檢索範圍較大時掃描索引的速度較快），巢狀迴圈遍歷leo2，如果找到匹配記錄，就去leo2表rowid所在的資料塊上取出，最後需要的就是leo2表裡面整個資料。

使用場景：1.外部表是一張小表例 leo1表因為記錄少會執行全表掃描

2.內部表是一張大表，並在關聯欄位上建立索引，當檢索範圍較大時掃描索引的速度較快

3. 當有特殊場景不容易模擬出來的時候，我們可以使用“cardinality”hints方式來輕鬆解決

hash join merge join nested loops lead cardinality append full index

2012.12.23
天津&winter
分享技術~成就夢想
Blog：

oracle hints的那點事

相關文章