【读书笔记】【收获，不止Oracle】嵌套循环与索引

最新推荐文章于 2024-04-20 18:58:30 发布
最新推荐文章于 2024-04-20 18:58:30 发布
阅读量516 收藏
点赞数
分类专栏： Oracle 读书笔记 文章标签： oracle 读书笔记
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u011877833/article/details/38931005
版权

1.1 使用HINT强制嵌套循环方式连接两个表

select /*+leading(t1) use_nl(t2) */*
from t1,t2
where t1.id = t2.t1_id
and t1.n = 19;
1.2 执行结果 

SCOTT@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fuqy42vhj5n6k, child number 0
-------------------------------------
select /*+leading(t1) use_nl(t2) */* from t1,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   | Buffers |
-------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS      |      |      1 |      1 |      1 |00:00:00.01 |    1014 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

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

Note

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
   - dynamic sampling used for this statement


25 rows selected.

结果可知：无索引的情况下 Buffers产生了1014。

2.1 不使用HINT时，oracle本身会选择哈希连接 

select *
from t1,t2
where t1.id = t2.t1_id
and t1.n = 19;
2.2 查看执行结果 

SCOTT@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  agmm8r3nv4gcg, child number 0
-------------------------------------
select * from t1,t2 where t1.id = t2.t1_id and t1.n = 19

Plan hash value: 1838229974

----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |      1 |      1 |      1 |00:00:00.83 |    1013 |   741K|   741K|  306K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   3 |   TABLE ACCESS FULL| T2   |      1 |    102K|    100K|00:00:00.20 |    1006 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."ID"="T2"."T1_ID")
   2 - filter("T1"."N"=19)

Note
-----

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   - dynamic sampling used for this statement


24 rows selected.

使用HINT时，Buffers 产生了 1014

Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
|   1 |  NESTED LOOPS      |      |      1 |      1 |      1 |00:00:00.01 |    1014 |

不使用HINT时，Buffers 产生了 1020  。即使产生的buffers比NESTED LOOPS多，编译器还是选择了HASH JOIN。 

| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|*  1 |  HASH JOIN         |      |      1 |      1 |      1 |00:00:00.83 |    1013 |   741K|   741K|  306K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      1 |00:00:00.01 |       7 |       |       |          |

3.1 在t1的n列上建立索引 
SCOTT@ orcl>create index t1_n on t1(n);

Index created

select /*+leading(t1) use_nl(t2) */*
from t1,t2
where t1.id = t2.t1_id
and t1.n = 19;
3.2 查看执行结果 

SCOTT@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fuqy42vhj5n6k, child number 0
-------------------------------------
select /*+leading(t1) use_nl(t2) */* from t1,t2 where t1.id = t2.t1_id and t1.n = 19

Plan hash value: 76617097

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                |      |      1 |      1 |      1 |00:00:00.01 |    1009 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |      1 |      1 |      1 |00:00:00.01 |       3 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("T1"."N"=19)
   4 - filter("T1"."ID"="T2"."T1_ID")

Note

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----
   - dynamic sampling used for this statement


25 rows selected.

在t1的n列上建立索引之后，产生的Buffers：1012

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                |      |      1 |      1 |      1 |00:00:00.01 |    1009 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |      1 |      1 |      1 |00:00:00.01 |       3 |
没有建索引之前产生的Buffers：1014 

Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |					
-------------------------------------------------------------------------------------					
|   1 |  NESTED LOOPS      |      |      1 |      1 |      1 |00:00:00.01 |    1014 |

建立索引易后，性能方面有了少许的提升。

两者之间的差异在于t1表，

无索引：TABLE ACCESS FULL 8

有索引：INDEX RANGE SCAN 2


4.1 在t2的t1_id上建立索引，继续提升性能 
SCOTT@ orcl>create index t2_t1_id on t2(t1_id);

Index created

select /*+leading(t1) use_nl(t2) */*
from t1,t2
where t1.id = t2.t1_id
and t1.n = 19;
4.2 查看执行计划 

SCOTT@ orcl>select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fuqy42vhj5n6k, child number 0
-------------------------------------
select /*+leading(t1) use_nl(t2) */* from t1,t2 where t1.id = t2.t1_id and t1.n = 19

Plan hash value: 2669480776

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID  | T2       |      1 |      1 |      1 |00:00:00.01 |       7 |      1 |
|   2 |   NESTED LOOPS                |          |      1 |      1 |      3 |00:00:00.01 |       6 |      1 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   3 |    TABLE ACCESS BY INDEX ROWID| 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.01 |       3 |      1 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

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


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement


26 rows selected.
之前建立的索引 
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                |      |      1 |      1 |      1 |00:00:00.01 |    1009 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   |      1 |      1 |      1 |00:00:00.01 |       3 |

新增的索引之后，buffers从1012降到了7。性能有了巨大提升。 

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID  | T2       |      1 |      1 |      1 |00:00:00.01 |       7 |      1 |
|   2 |   NESTED LOOPS                |          |      1 |      1 |      3 |00:00:00.01 |       6 |      1 |


总结：

适合Nested Loop连接的场合：

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

最理想的情况：驱动表结果集仅返回1条或几条记录，而被驱动表也只匹配了1条或几条记录。即使两个表的数据量巨大，查询速度也是很迅速的。

2.遇到一些不等值查询导致哈希和排序合并连接被限制使用，不得已才使用Nested Loop连接

建立索引的场合：

1.驱动表(t1)的限制条件(n)所在的列建索引(t1_n)

2.被驱动表(t2)的连接条件(t1_id)所在的列建索引(t2_t1_id)


凡人皆需侍奉
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Oracle学习笔记(索引)
04-24
Oracle学习笔记(索引)，有具体的代码案例，创建索引，删除索引，重建索引等等
35.读书笔记收获不止Oracle之嵌套循环表连接与索引
badman250的专栏
11-06 597
35.读书笔记收获不止Oracle之嵌套循环表连接与索引                   表连接的研究中，索引是非常重要的一部分，对提升表连接性能起到至关重要的作用。 1.  嵌套循环与索引 SQL>alter session set statistics_level=all; SQL>select /*+leading(t1) use_nl(t2)*/ * from t1,t2 wh
收获不止Oracle --读书笔记
wuxiaojun的专栏
07-04 615
本文基于以下思维导图知识点的梳理，给与说明 https://www.edrawsoft.cn/viewer/public/s/36020647239118 体系结构 学习oracle首先要了解其物理体系结构，也可以理解为oracle的整体架构，结合整理的思维导图，看出其体系结构可对应分为oracle实例和数据库文件。 ...
Oracle学习笔记（二）——嵌套循环连接
biww620的博客
06-19 7611
连接方式 Oracle的表连接方式有以下三种： 嵌套循环链接（Nested Loops Join）哈希链接（Hash Join）排序合并（Merge Sort Join） 连接涉及到两个表A和B，通俗的讲嵌套循环链接相当于遍历A中的每一条记录（满足A表条件），然后再在B表中遍历记录直至找到匹配的记录，等同于两层循环。而哈希链接和排序合并，可以看作是先各自处理自身的记录（排序或ha
《收获，不止Oracle》读书笔记
眼前一座山，踏平它
07-19 1029
本笔记真的是笔记，是在看书过程中记录到笔记本上的一些点，比较零散。 1、Oracle体系结构图： 图片来自：https://www.cnblogs.com/cccddd/p/7338543.html ① Oracle由实例和数据库组成，上半部的直角方框为实例instance，下半部的圆角方框为数据库databases。 ② 实例是由一个开辟的内存区SGA(System Global A...
oracle笔记其它数据库对象(序列索引同义词)
09-27
oracle笔记其它数据库对象(序列、索引、同义词)，有创建序列，创建索引，创建同义词的具体代码案例
SqlPlus 中行显示不全的问题（linesize和long的区别）
热门推荐
u011877833的专栏
10-01 1万+
现象：在使用dbms包中调用get_ddl函数查询的时候，sqlplus显示的结果不全
User has no SELECT privilege on V$SESSION（执行计划相关）
u011877833的专栏
08-25 3955
开启执行计划的时候，提示如下信息； User has no SELECT privilege on V$SESSION
【读书笔记】【收获，不止Oracle】不同连接类型表下，驱动顺序对查询性能的影响
u011877833的专栏
08-27 641
本次试验用于探究 嵌套循环，哈希连接，排序合并
【读书笔记】【收获，不止Oracle】哈希连接表(Hash)
u011877833的专栏
08-26 560
本次实验用于探究哈希连接表的访问次数。
Oracle学习笔记(20140419)（lsnrctl関連)
u011877833的专栏
04-19 454
不懂的一定要弄懂，网上多查查不要
【读书笔记】【收获，不止Oracle】不同类型下表连接限制的对比
u011877833的专栏
08-28 431
以下实验讨论不同连接类型下
Oracle EBS Interface/API(54)- GL日记账审批
陈显平的专栏-Oracle EBS
04-19 150
背景：客户化创建薪酬凭证或者银企付款入账日记账以后，用户希望自动提交审批流程，无需到系统标准功能点击审批，减少用户操作。
请解释Java中的Hibernate框架
yijiedsfrt的博客
04-19 406
2. **持久化类（Persistent Class）**：持久化类是Java应用程序中的任何Java类，可以被Hibernate用来创建，查询，更新或删除数据库表中的记录。1. **实体（Entity）**：在Hibernate中，实体是一个带有`@Entity`注解的Java类，它映射到数据库中的一个表。6. **映射（Mapping）**：映射是定义Java类和数据库表之间关系的过程。1. **配置Hibernate**：首先，需要配置Hibernate，包括数据库的URL、用户名、密码等信息。
oracle操作系统OS认证和密码文件认证
最新发布
qq_34595089的博客
04-20 911
Oracle登录认证方式主要涉及到如何验证用户身份以访问数据库。Oracle数据库提供了多种认证机制来确保数据的安全性和访问控制，每种方式都有其特定的使用场景和安全性考虑。以下是Oracle中常见的登录认证方式：1、基于操作系统的认证（OS认证）在基于操作系统的认证中，Oracle依赖底层操作系统的用户身份验证机制。当使用OS认证时，Oracle进程以操作系统用户的身份运行，并继承该用户的权限。这种认证方式通常用于本地连接，特别是在Unix/Linux系统上。
孟老师 oracle笔记
08-02
孟老师 oracle笔记中记录了他在学习和应用Oracle数据库管理系统过程中的重要知识点和经验总结。这些笔记对他个人来说是非常宝贵的学习工具，并且也可以分享给其他学习者。 首先，孟老师的笔记内容包括了Oracle...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值