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

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

1. 实验1

2. 实验2

猜你在找的Oracle相关文章