30.读书笔记收获不止Oracle之表的哈希连接

发布时间：2020-12-12 14:16:33 所属栏目：百科来源：网络整理

导读：30.读书笔记收获不止Oracle之表的哈希连接来看下哈希连接的表访问次数实验表还是和上篇中的一样。执行如下： SQL SELECT /*+leading(t1) use_hash(t2)*/ * from t1,t2 wheret1.id=t2.id; SQLselect * fromtable(dbms_xplan.display_cursor(null,null,'all

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

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

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

| 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,'allstats last'));

PLAN_TABLE_OUTPUT

SQL_ID 1m02nqfdvdpqk,t2 where t1.id=t2.id and

t1.n=999999999

Plan hash value: 1838229974

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

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

| 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,'allstats last'));

PLAN_TABLE_OUTPUT

SQL_ID dv8w4w999knbg,t2 where t1.id=t2.id and

1=2

Plan hash value: 487071653

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

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

| 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次。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!