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

最新推荐文章于 2017-11-06 18:17:19 发布
最新推荐文章于 2017-11-06 18:17:19 发布
阅读量502 收藏
点赞数
分类专栏： 收获不止Oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/notbaron/article/details/78460344
版权

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,

    nnumber,

   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,rownum,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

 

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

| Id | Operation                     | Name | Starts | E-Rows | A-Rows |         A-Time   | Buffers |

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

|   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,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

 

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

| Id | Operation                     | Name | Starts | E-Rows | A-Rows |         A-Time   | Buffers |

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

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

PLAN_TABLE_OUTPUT

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

SQL_ID   1py8ysk8rdtbc,child number 0

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

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

t1.n=19

 

Plan hash value: 1967407726

 

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

| Id | Operation                     | Name | Starts | E-Rows | A-Rows |         A-Time   | Buffers |

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

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

PLAN_TABLE_OUTPUT

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

SQL_ID   cn7hxw5rjsx56,child number 0

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

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

t1.n=999999999

 

Plan hash value: 1967407726

 

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

| Id | Operation                     | Name | Starts | E-Rows | A-Rows |         A-Time   | Buffers |

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

|   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作为驱动表。

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

badman250
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
oracle 表连接 - nested loop 嵌套循环连接
dataminer 的博客
12-09 1万+
一. nested loop 原理 nested loop 连接(循环嵌套连接)指的是两个表连接时, 通过两层嵌套循环来进行依次的匹配, 最后得到返回结果集的表连接方法.  假如下面的 sql 语句中表 T1 和 T2 的连接方式是循环嵌套连接, T1 是驱动表 select * from T1, T2 where T1.id = T2.id and T1.nam
Oracle学习笔记(嵌套表、可变数组)
04-07
Oracle学习笔记(嵌套表、可变数组),有具体的代码案例供大家参考
31.读书笔记收获不止Oracle之表的合并排序连接
badman250的专栏
11-06 447
31.读书笔记收获不止Oracle之表的合并排序连接 测试如下SQL语句 SQL>Alter session set statistics_level=all; SQL>select /*+ordered use_merge(t2)*/ *from t1,t2 where t1.id=t2.t1_id; SQL> select * fromtable(dbms_xplan.display_
35.读书笔记收获不止Oracle之嵌套循环表连接与索引
badman250的专栏
11-06 608
35.读书笔记收获不止Oracle之嵌套循环表连接与索引                   表连接的研究中，索引是非常重要的一部分，对提升表连接性能起到至关重要的作用。 1.  嵌套循环与索引 SQL>alter session set statistics_level=all; SQL>select /*+leading(t1) use_nl(t2)*/ * from t1,t2 wh
oracle 嵌套循环 yu 哈希连接
being21的专栏
06-13 2883
    嵌套循环    哈希连接    因为oracle基于成本的优化器一般最长使用的就是这两种连接方式，而性能在这里也可以很好的调节。hash连接需要耗费大量的cpu和内存，而嵌套循环则会少很多。在大数据量的双表连接中，哈希连接明显要有更好的性能。但是当一个小数量级的表和一个大数量级别的表连接的时候，优化就很难处理了。我在oracle这里现在最大的问题就在这里。这是一个实例。
Oracle的表连接方法（二）嵌套循环连接
cuishan5183的博客
06-02 886
Oracle表连接方法有四种： ● 排序合并连接（Sort Merge Join） ● 嵌套循环连接（Nested Loops Join） ● 哈希连接（Hash Join） ● 笛卡尔积（Cart...
Oracle数据库表连接笔记.doc
06-03
Oracle数据库中的表连接是数据库查询中的核心操作，它允许从多个表中合并数据，以满足复杂的业务需求。Oracle数据库提供了多种连接方法，包括嵌套循环连接、排序合并连接、集群连接、笛卡尔连接和散列连接，以及特定...
Oracle学习笔记（二）——嵌套循环连接
biww620的博客
06-19 7677
连接方式 Oracle的表连接方式有以下三种： 嵌套循环链接（Nested Loops Join）哈希链接（Hash Join）排序合并（Merge Sort Join） 连接涉及到两个表A和B，通俗的讲嵌套循环链接相当于遍历A中的每一条记录（满足A表条件），然后再在B表中遍历记录直至找到匹配的记录，等同于两层循环。而哈希链接和排序合并，可以看作是先各自处理自身的记录（排序或ha
18.读书笔记收获不止Oracle之 索引SUM和AVG优化
badman250的专栏
11-05 1857
18.读书笔记收获不止Oracle之 索引SUM和AVG优化 看下索引在sum()和avg之类的聚合语句中的优化。 drop table t purge; SQL> set timing off SQL> set autotrace off SQL> create table t as select * from dba_objects; SQL> create index idx1_
21.读书笔记收获不止Oracle之 索引回表效率
badman250的专栏
11-05 949
21.读书笔记收获不止Oracle之 索引回表效率 如果一直要执行回表，我们就需要关注回表的效率了。 1.  示例 SQL> create table t_colocated (id number,col2varchar2(100)); begin     for i in 1 .. 100000     loop                insert into t_coloc
20.读书笔记收获不止Oracle之 索引回表和优化
badman250的专栏
11-05 915
20.读书笔记收获不止Oracle之 索引回表和优化 1.  索引回表情况 看下索引回表。 drop table t purge; SQL> create table t as select * from dba_objects; SQL> create index inx1_object_id on t (object_id); SQL> set autotrace traceonl
15.读书笔记收获不止Oracle之 索引高度
badman250的专栏
11-03 902
15.读书笔记收获不止Oracle之 索引高度 我们用实际例子来看下索引的高度是否真的比较低。 构造一系列表T1到T7，记录数从5到500万依次以10倍的差额逐步增大。 SQL>Create table t1 as select rownum as id,rownum+1 as id2from dual connect by level Create table t2 as select
19.读书笔记收获不止Oracle之 索引MAX和MIN优化
badman250的专栏
11-05 901
19.读书笔记收获不止Oracle之 索引MAX和MIN优化 1.  示例一 SQL> drop table t purge; SQL> create table t as select * fromdba_objects; SQL> create index idx1_object_id on t(object_id); 使用MAX看看，能不能用到索引。 set autotrace
24.读书笔记收获不止Oracle之 索引的危害
badman250的专栏
11-05 814
24.读书笔记收获不止Oracle之 索引的危害            索引能带来好的一面，也必定会带来坏的的东西。 来看下： SQL> create table t_no_idx as select *from dba_objects; Table created. SQL> insert into t_no_idx select * fromt_no_idx; SQL> inser
17.读书笔记收获不止Oracle之 索引存储列值
badman250的专栏
11-05 796
17.读书笔记收获不止Oracle之 索引存储列值 下面来看下索引的第二个特点，索引存储列值及rowid的特性。 1.  简单示例 SQL>create table t as select * from dba_objects; Create index idx1_object_id on t (object_id); Select count(*) from t;
Oracle数据库查询深入理解：多表查询与连接操作
"Oracle学习笔记，李兴华老师的Oracle教程，涵盖了从基础到高级的Oracle知识，包括多表查询、连接操作等重要概念。" Oracle数据库是企业级的关系型数据库管理系统，广泛应用于数据存储和管理。在Oracle学习笔记中，...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值