oracle 雜湊查詢與巢狀查詢跟表的先後關係測試

本次測試資料庫兩種連線型別雜湊連線與巢狀連線跟表的順序的關係；

構造兩個數量級別懸殊的兩個堆疊比表，透過hint來執行，實現不同的表的連線順序，檢查相應的執行計劃；

1、表的資訊：

表A1：100萬條記錄；

表A2：10條記錄；

2、執行的語句：

select  *  from wu.test4 b ,wu.test2 a   where b.object_id=a.object_id

3、以上兩個表均收集了統計資訊；

1、  使用A1（大表）做構造表，A2（小表）做探測表；（資料庫先執行A1,然後執行A2）

2、  使用A2做構造表，A1做探測表；（資料庫先執行A2,然後執行A1）

PLAN_TABLE_OUTPUT
Plan hash value: 3406100614

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 174 | 17748 | | 10339 (1)| 00:02:05 |
|* 1 | HASH JOIN | | 174 | 17748 | 118M| 10339 (1)| 00:02:05 |
| 2 | TABLE ACCESS FULL| A1 | 1133K| 105M| | 4429 (1)| 00:00:54 |
| 3 | TABLE ACCESS FULL| A2 | 11 | 44 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A1"."OBJECT_ID"="A2"."OBJECT_ID")

總結耗時：125S

PLAN_TABLE_OUTPUT

Plan hash value: 1713954154

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 174 | 17748 | 4439 (1)| 00:00:54 |
|* 1 | HASH JOIN | | 174 | 17748 | 4439 (1)| 00:00:54 |
| 2 | TABLE ACCESS FULL| A2 | 11 | 44 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| A1 | 1133K| 105M| 4429 (1)| 00:00:54 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A1"."OBJECT_ID"="A2"."OBJECT_ID")

總結總耗時：54S

3、  使用A1（大表）做構造表，A2（小表）做探測表；（資料庫先執行A1,然後執行A2）

4、  使用A2做構造表，A1做探測表；（資料庫先執行A2,然後執行A1）

指令碼：如下

EXPLAIN PLAN FOR

 select   /*+leading(A2 A1) use_Nl(A1)  use_Nl(A2)*/*

 from A1,A2

 WHERE A1.OBJECT_ID=A2.OBJECT_ID

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |   176 | 19712 | 51589   (2)| 00:10:20 |

|   1 |  NESTED LOOPS      |      |   176 | 19712 | 51589   (2)| 00:10:20 |

|   2 |   TABLE ACCESS FULL| A2   |    11 |   154 |     2   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| A1   |    16 |  1568 |  4690   (2)| 00:00:57 |

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

Predicate Information (identified by operation id):

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

   3 - filter("A1"."OBJECT_ID"="A2"."OBJECT_ID")

總結：耗時620S

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |   176 | 19712 |   316K  (1)| 01:03:17 |

|   1 |  NESTED LOOPS      |      |   176 | 19712 |   316K  (1)| 01:03:17 |

|   2 |   TABLE ACCESS FULL| A1   |  1147K|   107M|  4691   (2)| 00:00:57 |

|*  3 |   TABLE ACCESS FULL| A2   |     1 |    14 |     0   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   3 - filter("A1"."OBJECT_ID"="A2"."OBJECT_ID")

總結：耗時3600S以上；

1、  無論進行雜湊連線還是巢狀迴圈連線的時候，選擇結果小的表都要先執行；

2、  連線條件沒有選擇性的時候，雜湊連線的速度會比巢狀迴圈連線的速度快，因為雜湊連線只要執行一次就可以了，而巢狀迴圈連線需要執行多次（由外表的結果決定）

相關原理: 1． 一張小表被hash在記憶體中。因為資料量小，所以這張小表的大多數資料已經駐入在記憶體中，剩下的少量資料被放置在臨時表空間中；

2． 每讀取大表的一條記錄，就和小表中記憶體中的資料進行比較，如果符合，則立即輸出資料（也就是說沒有讀取臨時表空間中的小表的資料）。而如果大表的資料與小表中臨時表空間的資料相符合，則不直接輸出，而是也被儲存臨時表空間中。

3． 當大表的所有資料都讀取完畢，將臨時表空間中的資料以其輸出。 如果小表的資料量足夠小（小於hash area size），那所有資料就都在記憶體中了，可以避免對臨時表空間的讀寫。