29.读书笔记收获不止Oracle之表的循环嵌套连接

连接的几个类型，循环嵌套连接、哈希连接、合并排序连接。

哈希连接不算排序，由PGA中的HASH_AREA_SIZE参数来控制，而排序合并连接则是由PGA中的SORT_AREA_SIZE参数控制的。

表连接总体的比例情况如下：循环嵌套连接70%，哈希连接20%，合并排序连接10%左右。

一句老话：什么时候选择什么技术。

1. 嵌套循环的表访问次数

sql> drop table t1 cascade constraintspurge;

sql> drop table t2 cascade constraintspurge;

sql>create table t1(

id number not null,

nnumber,

contents varchar2(4000)

);

sql>create table t2(

id number notnull,

t1_id numbernot null,

contents varchar2(4000)

);

sql> execute dbms_random.seed(0);

PL/sql procedure successfully completed.

sql> insert into t1 selectrownum,rownum,dbms_random.string('a',50) from dual connect by level <=100order by dbms_random.random;

100 rows created.

sql> insert into t2 select rownum,dbms_random.string('b',50)from dual connect by level <=100000 order by dbms_random.random;

100000 rows created.

sql> commit;

Commit complete.

sql> select count(*) from t1;

COUNT(*)

----------

100

sql> select count(*) from t2;

COUNT(*)

----------

100000

然后开始测试连接：

Set linesize 1000

Alter session set statistics_level=all;

sql>SELECT /*+leading(t1) use_nl(t2)*/ * from t1,t2 wheret1.id=t2.id;

..省略一些记录

sql> select * fromtable(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT

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

sql_ID 5383kbnkfw56a,child number 1

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

SELECT /*+leading(t1) use_nl(t2)*/ * fromt1,t2 where t1.id=t2.id

Plan hash value: 1967407726

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

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

| 0| SELECT STATEMENT | | 1 | | 100 |00:00:00.36 | 100K|

| 1| NESTED LOOPS | | 1 | 100| 100 |00:00:00.36 | 100K|

PLAN_TABLE_OUTPUT

| 2| TABLE ACCESS FULL| T1 | 1 | 100 | 100 |00:00:00.01 | 14 |

|* 3| TABLE ACCESS FULL| T2 |100 | 1 | 100 |00:00:00.36 | 100K|

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

Predicate Information (identified byoperation id):

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

3 -filter("T1"."ID"="T2"."ID")

Note

-----

PLAN_TABLE_OUTPUT

-dynamic statistics used: dynamic sampling (level=2)

24 rows selected.

我们发现两个表都被访问了100次。

1.1再次执行

sql>SELECT /*+leading(t1) use_nl(t2)*/ * from t1,t2 wheret1.id=t2.id and t1.n in(17,19);

sql> select * fromtable(dbms_xplan.display_cursor(null,'allstats last'));

PLAN_TABLE_OUTPUT

sql_ID 6wsrr2xgdphay,child number 0

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

SELECT /*+leading(t1) use_nl(t2)*/ * fromt1,t2 where t1.id=t2.id and

t1.n in(17,19)

Plan hash value: 1967407726

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

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

| 0| SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 2019 |

PLAN_TABLE_OUTPUT

| 1| NESTED LOOPS | | 1 | 2 | 2 |00:00:00.01 | 2019 |

|* 2| TABLE ACCESS FULL| T1 | 1 | 2 | 2 |00:00:00.01 | 8 |

|* 3| TABLE ACCESS FULL| T2 | 2 | 1 | 2 |00:00:00.01 | 2011 |

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

Predicate Information (identified byoperation id):

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

2- filter(("T1"."N"=17 OR "T1"."N"=19))

3- filter("T1"."ID"="T2"."ID")

PLAN_TABLE_OUTPUT

Note

-----

-dynamic statistics used: dynamic sampling (level=2)

26 rows selected.

发现T1表访问了1次，T2表访问了2次。

1.2第三次执行

sql>SELECT /*+leading(t1) use_nl(t2)*/ * from t1,t2 wheret1.id=t2.id and t1.n=19;

sql> select * fromtable(dbms_xplan.display_cursor(null,'allstats last'));

PLAN_TABLE_OUTPUT

sql_ID 1py8ysk8rdtbc,t2 where t1.id=t2.id and

t1.n=19

Plan hash value: 1967407726

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

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

| 0| SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 1014 |

PLAN_TABLE_OUTPUT

| 1| NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 1014 |

|* 2| TABLE ACCESS FULL| T1 | 1 | 1 | 1 |00:00:00.01 | 8 |

|* 3| TABLE ACCESS FULL| T2 | 1 | 1 | 1 |00:00:00.01 | 1006 |

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

Predicate Information (identified byoperation id):

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

2- filter("T1"."N"=19)

3- filter("T1"."ID"="T2"."ID")

PLAN_TABLE_OUTPUT

Note

-----

-dynamic statistics used: dynamic sampling (level=2)

26 rows selected.

这次是T1表访问1次，T2表访问1次。

1.3第四次执行

sql>SELECT /*+leading(t1) use_nl(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 cn7hxw5rjsx56,t2 where t1.id=t2.id and

t1.n=999999999

Plan hash value: 1967407726

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

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

| 0| SELECT STATEMENT | | 1 | | 0 |00:00:00.01 | 7 |

PLAN_TABLE_OUTPUT

| 1| NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 7 |

|* 2| TABLE ACCESS FULL| T1 | 1 | 1 | 0 |00:00:00.01 | 7 |

|* 3| TABLE ACCESS FULL| T2 | 0 | 1 | 0 |00:00:00.01 | 0 |

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

Predicate Information (identified byoperation id):

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

2- filter("T1"."N"=999999999)

3- filter("T1"."ID"="T2"."ID")

PLAN_TABLE_OUTPUT

Note

-----

-dynamic statistics used: dynamic sampling (level=2)

26 rows selected.

发现T2表访问0次，T1表访问1次。

1.4连接访问次数

T1表查询返回多少记录，T2表就访问多少次。

HINT 的 /*+leading(t1)use_nl(t2)*/含义,use_nl表示强制用嵌套循环连接。Leading(t1)表示先访问t1表，就是t1作为驱动表。

在嵌套循环连接中，驱动表返回多少条记录，被驱动表就访问多少次。

29.读书笔记收获不止Oracle之表的循环嵌套连接

1. 嵌套循环的表访问次数

1.1再次执行

1.2第三次执行

1.3第四次执行

1.4连接访问次数

猜你在找的Oracle相关文章