35.读书笔记收获不止Oracle之嵌套循环表连接与索引

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

35.读书笔记收获不止Oracle之嵌套循环表连接与索引

                  表连接的研究中，索引是非常重要的一部分，对提升表连接性能起到至关重要的作用。

1.  嵌套循环与索引

SQL>alter session set statistics_level=all;

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

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstatslast'));

PLAN_TABLE_OUTPUT

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

SQL_ID   2w8kmgu3tmxhq,child number 0

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

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

and t1.n=19

 

Plan hash value: 1967407726

 

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

-----

 

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

 

PLAN_TABLE_OUTPUT

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

ers |

 

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

-----

 

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

014 |

 

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

014 |

 

 

PLAN_TABLE_OUTPUT

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

|*  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 |    1

006 |

 

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

-----

 

 

Predicate Information (identified byoperation id):

 

PLAN_TABLE_OUTPUT

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

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

 

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

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

 

 

22 rows selected.

1.1      不用HINT

SQL>select * from t1,t2 where t1.id=t2.t1_id and t1.n=19;

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

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

SQL_ID   g7rb3y8bmguur,child number 0

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

select * from t1,t2 where t1.id=t2.t1_idand t1.n=19

 

Plan hash value: 1838229974

 

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

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

 

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

ers | OMem |  1Mem | Used-Mem |

 

PLAN_TABLE_OUTPUT

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

 

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

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

 

|   0| SELECT STATEMENT   |      |           1 |              |         1 |00:00:00.06 |    1

013 |           |          |             |

 

|*  1|  HASH JOIN              |         |           1 |           1 |          1 |00:00:00.06 |    1

013 |       960K|      960K| 385K (0)|

 

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

 

PLAN_TABLE_OUTPUT

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

  7 |          |          |             |

 

|   3|   TABLE ACCESS FULL| T2   |         1 |          100K|      100K|00:00:00.01 |    1

006 |           |          |             |

 

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

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

 

 

Predicate Information (identified byoperation id):

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

 

PLAN_TABLE_OUTPUT

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

 

   1- access("T1"."ID"="T2"."T1_ID")

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

 

 

21 rows selected.

没有HINT，就走Hash Join。其实两者的消耗应该是差不多的。

 

1.2      T1表键索引后

SQL>create index t1_n on t1(n);

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

 

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

PLAN_TABLE_OUTPUT

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

SQL_ID   4n9w3nnj61xk0,child number 0

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

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

and t1.n=19

 

Plan hash value: 2987075831

 

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

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

 

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

 

PLAN_TABLE_OUTPUT

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

  A-Time   | Buffers |

 

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

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

 

|   0| SELECT STATEMENT                                     |       |         1 |           |     1 |

00:00:00.01 |        1009 |

 

|   1|  NESTED LOOPS                                           |       |         1 |         1 |     1 |

00:00:00.01 |        1009 |

 

 

PLAN_TABLE_OUTPUT

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

|   2|   TABLE ACCESS BY INDEX ROWID BATCHED|T1   |                     1 |         1 |     1 |

00:00:00.01 |            3 |

 

|*  3|    INDEX RANGE SCAN                              | T1_N |         1 |         1 |     1 |

00:00:00.01 |            2 |

 

|*  4|   TABLE ACCESS FULL                                | T2  |        1 |         1 |     1 |

00:00:00.01 |        1006 |

 

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

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

 

PLAN_TABLE_OUTPUT

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

 

 

Predicate Information (identified byoperation id):

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

 

   3- access("T1"."N"=19)

   4- filter("T1"."ID"="T2"."T1_ID")

 

 

23 rows selected.

 

22 rows selected.

T1表走的是索引了。

1.3      T2表建索引

SQL>create index t2_t1_id on t2(t1_id);

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

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

PLAN_TABLE_OUTPUT

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

SQL_ID   2w8kmgu3tmxhq,child number 0

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

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

and t1.n=19

 

Plan hash value: 342856344

 

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

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

 

| Id | Operation                                                            | Name      | Starts | E-Rows | A-R

 

PLAN_TABLE_OUTPUT

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

ows |       A-Time    | Buffers | Reads  |

 

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

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

 

|   0| SELECT STATEMENT                                      |                  |             1|               |

  1|00:00:00.03 |               7 |           4 |

 

|   1|  NESTED LOOPS                                            |                  |             1|            1 |

  1|00:00:00.03 |               7 |           4 |

 

 

PLAN_TABLE_OUTPUT

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

|   2|   NESTED LOOPS                                                            |                  |             1|            1 |

  1|00:00:00.03 |               6 |           4 |

 

|   3|    TABLE ACCESS BY INDEX ROWID BATCHED|T1       |             1 |            1 |

  1|00:00:00.01 |               3 |           0 |

 

|*  4|     INDEX RANGE SCAN                            | T1_N       |             1 |            1 |

  1|00:00:00.01 |               2 |           0 |

 

|*  5|    INDEX RANGE SCAN                               | T2_T1_ID |             1 |            1 |

  1|00:00:00.03 |               3 |           4 |

 

PLAN_TABLE_OUTPUT

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

 

|   6|   TABLE ACCESS BY INDEX ROWID        |T2             |             1 |            1 |

  1|00:00:00.01 |               1 |           0 |

 

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

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

 

 

Predicate Information (identified byoperation id):

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

 

 

PLAN_TABLE_OUTPUT

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

   4- access("T1"."N"=19)

   5- access("T1"."ID"="T2"."T1_ID")

25 rows selected.

T2表也走索引了。现在不用HINT，ORACLE也会自己走索引了，因为代价已经很低了。

如下：

SQL>select * from t1,t2 where t1.id=t2.t1_id and t1.n=19;

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

PLAN_TABLE_OUTPUT

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

SQL_ID   g7rb3y8bmguur,child number 0

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

select * from t1,t2 where t1.id=t2.t1_idand t1.n=19

 

Plan hash value: 342856344

 

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

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

 

| Id | Operation                                                            | Name      | Starts | E-Rows | A-R

ows |       A-Time    | Buffers |

 

PLAN_TABLE_OUTPUT

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

 

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

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

 

|   0| SELECT STATEMENT                                      |                  |             1|               |

  1|00:00:00.01 |               6 |

 

|   1|  NESTED LOOPS                                            |                  |             1|            1 |

  1|00:00:00.01 |               6 |

 

|   2|   NESTED LOOPS                                                            |                  |             1|            1 |

 

PLAN_TABLE_OUTPUT

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

  1|00:00:00.01 |               5 |

 

|   3|    TABLE ACCESS BY INDEX ROWID BATCHED|T1       |             1 |            1 |

  1|00:00:00.01 |               2 |

 

|*  4|     INDEX RANGE SCAN                            | T1_N       |             1 |            1 |

  1|00:00:00.01 |               1 |

 

|*  5|    INDEX RANGE SCAN                               | T2_T1_ID |             1 |            1 |

  1|00:00:00.01 |               3 |

 

 

PLAN_TABLE_OUTPUT

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

|   6|   TABLE ACCESS BY INDEX ROWID        |T2             |             1 |            1 |

  1|00:00:00.01 |               1 |

 

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

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

 

 

Predicate Information (identified byoperation id):

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

 

   4- access("T1"."N"=19)

 

PLAN_TABLE_OUTPUT

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

   5- access("T1"."ID"="T2"."T1_ID")

 

Note

-----

   -this is an adaptive plan

28 rows selected.

2.  最实用NL连接的场景

两表关联返回的记录不多。

遇到一些不等值查询，只能试用NL连接。

当然最好：驱动表的限制条件所在的列有索引，被驱动表的连接条件所在的列也有索引。

 

badman250
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Oracle学习笔记(嵌套表、可变数组)
04-07
Oracle学习笔记(嵌套表、可变数组),有具体的代码案例供大家参考
《收获，不止Oracle》读书笔记
weixin_30455067的博客
04-12 82
1、Oracle体系结构图： ①Oracle由实例和数据库组成，上半部的直角方框为实例instance，下半部的圆角方框为数据库databases。 ②实例是由一个开辟的内存区SGA(SystemGlobalArea)和一系列后台进程组成的，其中SGA最主要被划分为共享池（sharedpool）、数据缓冲区（dbcache）和日志缓冲区（logbuffer）三类。...
《收获，不止Oracle》读书笔记（一）：索引优化
Catharina官方博客
04-18 1927
最近在补数据库盲点之一：索引的优化，这里做下记录。我看的是《收获，不止Oracel》这本书，说实话，是因为豆瓣读书上评分很高，才选了这本，但是，好像不太适合我的感觉，行文风格有点啰嗦了，看来还是直接切入要害更适合我。 索引优化 索引的三大特点： 索引树的高度一般都比较低； 索引由索引列存储的值和rowid组成； 索引本身是有序的。 插一句，Oracle中追踪索引执行性能的方法：
收获不止Oracle --读书笔记
wuxiaojun的专栏
07-04 616
本文基于以下思维导图知识点的梳理，给与说明 https://www.edrawsoft.cn/viewer/public/s/36020647239118 体系结构 学习oracle首先要了解其物理体系结构，也可以理解为oracle的整体架构，结合整理的思维导图，看出其体系结构可对应分为oracle实例和数据库文件。 ...
《收获不止ORACLE 》笔记
波子汽水
11-30 782
sqlplus “/as sysdba” show parameter sga 了解SGA开辟多大空间 共享内存区 show parameter pga oracle11g 中memory_target 参数自动分配给SGA和PGA show parameter instance_name 实例名 archive log list 归档状态 show parameter
Oracle数据库表连接笔记.doc
06-03
Oracle数据库表连接笔记
《收获,不止Oracle》读后感_20170202
mush_me的专栏
02-06 234
书以故事的形式，讲解了oracle的体系结构、表、索引、表链接等等内容，故事中又套着小故事，很新颖的形式。 看完之后，收获良多，原来书还可以这样写，以及这样做培训效果会很好。 同时也非常佩服作者精心准备的案例及资料，既能说明技术范畴，代码又容易阅读。作者为此应该花费了不少心思。 书在讲解知识的同时，更加注重解决问题思路和方法的传授，这也是书的名称为“不止oracle”的原因。 作者希望大家...
读书笔记 --《收获,不止Oracle》（一）
08-04 166
本篇文章是《收获,不止Oracle》阅读笔记，关于本书读后感：例子生动活泼，比喻贴切，浅显易懂，复杂问题简单化。 1）由此及彼，手机分类 --> 数据库分类   2）各个角色掌握的知识重点   3）Oracle体系结构图     4）体系结构图说明     5）由Oracle体系结构图，联想现实生活中老余开店的故事    ...
《收获，不止Oracle》读书笔记（二）：表连接
Catharina官方博客
04-20 611
三种表连接类型： 嵌套循环连接（Nested Loop Join）： 日常使用占比70% 需要区分驱动表Leading（t1）与被驱动表use_nt(t2) 驱动表返回多少条记录，被驱动表就访问多少次 哈希连接（Hash Join）： 日常使用占比20% 区分驱动表Leading(t1)与被驱动表use_hash(t2) 驱动表与被驱动表都只会访问0次或1次 排序合并连接（Merge So
21.读书笔记收获不止Oracle之 索引回表效率
badman250的专栏
11-05 944
21.读书笔记收获不止Oracle之 索引回表效率 如果一直要执行回表，我们就需要关注回表的效率了。 1.  示例 SQL> create table t_colocated (id number,col2varchar2(100)); begin     for i in 1 .. 100000     loop                insert into t_coloc
14.读书笔记收获不止Oracle之 索引
badman250的专栏
11-03 595
14.读书笔记收获不止Oracle之 索引   1.  B树索引 索引和表一样，都是逻辑体系结构中的段的一种。当建一个T表，就产生一个T表的表SEGMENT。当在T表的某些列上建索引IDX_T，就产生一个IDX_T的索引SEGMENT。 索引建在表的具体列上，存在目的就是让表的查询更快，效率更高。表记录丢失关乎生死，而索引丢失只需重建即可。            索引是数据库学习中最实用
31.读书笔记收获不止Oracle之表的合并排序连接
badman250的专栏
11-06 444
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_
29.读书笔记收获不止Oracle之表的循环嵌套连接
badman250的专栏
11-06 493
29.读书笔记收获不止Oracle之表的循环嵌套连接 连接的几个类型，循环嵌套连接、哈希连接、合并排序连接。                   哈希连接不算排序，由PGA中的HASH_AREA_SIZE参数来控制，而排序合并连接则是由PGA中的SORT_AREA_SIZE参数控制的。                   表连接总体的比例情况如下：循环嵌套连接70%，哈希连接20%，合并排序
mysql联合索引的选择性
ckqq46580的博客
01-18 407
联合索引就是同时对表中多个列进行索引，联合索引会依次根据联合索引中的列排序存储。 下面通过下面的实验来探讨下联合索引的使用选择性： 版本：percona 5.6.27 create ta...
散列连接，索引，嵌套循环
五谷杂粮
07-26 737
1  深入理解嵌套循环执行计划    为什么要引入散列连接呢？假设两张表t1(c1 int,c2 int),t2(d1 int,d2 int)，查询语句为select c1,d1 from t1 inner join t2 on c1=d1。如果数据库没有实现散列连接、合并连接的话，只能选择使用嵌套循环。从上篇文章中我们可以得到，对于t1的每一条记录，都需要遍历t2的每一条记录。因此，当t...
三思笔记之一步一步学oracle pdf
最新发布
07-30
4. 学习数据库管理：理解数据库管理的概念和原则，包括如何创建和管理表、索引、视图、存储过程、触发器等。了解如何进行备份和恢复数据库，以及如何进行性能优化和故障处理等。 5. 熟悉PL/SQL编程：PL/SQL是Oracle...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值