30.读书笔记收获不止Oracle之表的哈希连接
来看下哈希连接的表访问次数
实验表还是和上篇中的一样。
执行如下:
SQL> SELECT /*+leading(t1) use_hash(t2)*/ * from t1,t2 wheret1.id=t2.id;
SQL>select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8r4tqu5rnv8m0,child number 0
-------------------------------------
SELECT /*+leading(t1) use_hash(t2)*/ * fromt1,t2 where t1.id=t2.id
Plan hash value: 1838229974
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | | 100 |00:00:00.01 | 1018 | | | |
|* 1| HASH JOIN | | 1 | 100 | 100 |00:00:00.01 | 1018 | 960K| 960K|1235K (0)|
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2| TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 7 | | | |
| 3| TABLE ACCESS FULL| T2 | 1 | 118K| 100K|00:00:00.01 | 1011 | | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- access("T1"."ID"="T2"."ID")
Note
-----
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-dynamic statistics used: dynamic sampling (level=2)
24 rows selected.
T2表职位访问1次。在HASH连接中,驱动表和被驱动表都只会访问0次或者1次。
1. 实验1
SQL> SELECT /*+leading(t1) use_hash(t2)*/ * from t1,t2 wheret1.id=t2.id and t1.n=999999999;
SQL>select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1m02nqfdvdpqk,child number 0
-------------------------------------
SELECT /*+leading(t1) use_hash(t2)*/ * fromt1,t2 where t1.id=t2.id and
t1.n=999999999
Plan hash value: 1838229974
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 7 | | | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 1| HASH JOIN | | 1 | 1 | 0 |00:00:00.01 | 7 | 683K| 683K| 173K (0)|
|* 2| TABLE ACCESS FULL| T1 | 1 | 1 | 0 |00:00:00.01 | 7 | | | |
| 3| TABLE ACCESS FULL| T2 | 0 | 118K| 0 |00:00:00.01 | 0 | | | |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- access("T1"."ID"="T2"."ID")
2- filter("T1"."N"=999999999)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
-dynamic statistics used: dynamic sampling (level=2)
26 rows selected.
T1返回0,T2表也是访问0.
2. 实验2
SQL> SELECT /*+leading(t1) use_hash(t2)*/ * from t1,t2 wheret1.id=t2.id and 1=2;
SQL>select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dv8w4w999knbg,child number 0
-------------------------------------
SELECT /*+leading(t1) use_hash(t2)*/ * fromt1,t2 where t1.id=t2.id and
1=2
Plan hash value: 487071653
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
----------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | | 0 |00:00:00.01 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 1| FILTER | | 1 | | 0 |00:00:00.01 |
|* 2| HASH JOIN | | 0 | 100 | 0|00:00:00.01 |
| 3| TABLE ACCESS FULL| T1 | 0 | 100 | 0 |00:00:00.01 |
| 4| TABLE ACCESS FULL| T2 | 0 | 118K| 0 |00:00:00.01 |
----------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- filter(NULL IS NOT NULL)
2- access("T1"."ID"="T2"."ID")
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
-dynamic statistics used: dynamic sampling (level=2)
27 rows selected.
在HASH连接中,驱动表和被驱动表都只会访问0次或者1次。