多表连接，复合索引，10053

最新推荐文章于 2023-08-22 14:38:08 发布
最新推荐文章于 2023-08-22 14:38:08 发布
阅读量513 收藏
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/gudagudaga/article/details/40615735
版权
本文探讨了多表连接的查询优化，包括建立不同索引对查询性能的影响。首先展示了没有索引时的全表扫描，然后分别在locations2的city和departments2的location_id上创建索引，显著提高了查询效率。分析了执行计划，揭示了索引对于减少成本和提升响应时间的关键作用。
摘要由CSDN通过智能技术生成

1.多表连接

1.1建立所有索引的查询

SQL> SELECTemp.last_name,emp.first_name,j.job_title,d.department_name,l.city,l.state_province,l.postal_code,l.street_address,

 2 emp.email,emp.phone_number,emp.hire_date,emp.salary,mgr.last_name

 3  from hr.employeesemp,hr.employees mgr,hr.departments d,hr.locations l,hr.jobs j

 4  where l.city=:loc

 5  andemp.manager_id=mgr.employee_id

 6  andemp.department_id=d.department_id

 7  and d.location_id=l.location_id

 8  and emp.job_id=j.job_id;

 

Execution Plan

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

Plan hash value: 1518052872

 

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

| Id  | Operation                        | Name              | Rows | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT                 |                   |    15 | 2580 |    12   (9)| 00:00:01 |

|*  1 | HASH JOIN                      |                   |    15 | 2580 |    12   (9)| 00:00:01 |

|*  2 |  HASH JOIN                     |                   |    15 | 2400 |     8   (0)| 00:00:01 |

|   3 |   TABLE ACCESS BY INDEX ROWID   |EMPLOYEES         |     4 |  264 |     1   (0)| 00:00:01 |

|   4 |    NESTED LOOPS                |                   |    15 | 1995 |     5   (0)| 00:00:01 |

|   5 |     NESTED LOOPS                |                   |     4 |  268 |     3   (0)| 00:00:01 |

|   6 |      TABLE ACCESS BY INDEX ROWID| LOCATIONS         |    1 |    48 |     2  (0)| 00:00:01 |

|*  7 |       INDEX RANGE SCAN          |LOC_CITY_IX       |     1 |      |     1   (0)| 00:00:01 |

|   8 |      TABLE ACCESS BY INDEX ROWID| DEPARTMENTS       |    4 |    76 |     1  (0)| 00:00:01 |

|*  9 |       INDEX RANGE SCAN          |DEPT_LOCATION_IX  |     4 |      |     0   (0)| 00:00:01 |

|* 10 |      INDEX RANGE SCAN            | EMP_DEPARTMENT_IX |    10 |      |     0   (0)| 00:00:01 |

|  11 |   TABLE ACCESS FULL             |JOBS              |    19 |  513 |     3   (0)| 00:00:01 |

|  12 |  TABLE ACCESS FULL              |EMPLOYEES         |   107 | 1284 |     3   (0)| 00:00:01 |

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

 

Predicate Information(identified by operation id):

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

 

   1 -access("EMP"."MANAGER_ID"="MGR"."EMPLOYEE_ID")

   2 -access("EMP"."JOB_ID"="J"."JOB_ID")

   7 -access("L"."CITY"=:LOC)

   9 -access("D"."LOCATION_ID"="L"."LOCATION_ID")

  10 -access("EMP"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

 

1.2没有索引的查询

SQL> SELECTemp.last_name,emp.first_name,j.job_title,d.department_name,l.city,l.state_province,l.postal_code,l.street_address,

  2 emp.email,emp.phone_number,emp.hire_date,emp.salary,mgr.last_name

  3  fromhr.employees2 emp,hr.employees2 mgr,hr.departments2 d,hr.locations2 l,hr.jobs2j

  4 where l.city=:loc

  5  andemp.manager_id=mgr.employee_id

  6  andemp.department_id=d.department_id

  7  andd.location_id=l.location_id

  8  andemp.job_id=j.job_id;

 

Execution Plan

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

Plan hash value: 1871751231

 

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

| Id  | Operation               | Name         | Rows | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT        |              |     1 |  277 |    17  (12)| 00:00:01 |

|*  1 | HASH JOIN              |              |     1 |  277 |    17  (12)| 00:00:01 |

|*  2 |  HASH JOIN             |              |     1 |  250 |    13  (8)| 00:00:01 |

|*  3 |   HASH JOIN            |              |     1 |  224 |    10  (10)| 00:00:01 |

|   4 |    MERGE JOIN CARTESIAN|             |    25 |  4525 |    6   (0)| 00:00:01 |

|*  5 |     TABLE ACCESS FULL  |LOCATIONS2   |     1 |   74 |     3   (0)| 00:00:01 |

|   6 |     BUFFER SORT        |              |   107 | 11449 |     3  (0)| 00:00:01 |

|   7 |      TABLE ACCESS FULL | EMPLOYEES2  |   107 | 11449 |     3  (0)| 00:00:01 |

|   8 |    TABLE ACCESS FULL   | DEPARTMENTS2|    27 | 1161 |     3   (0)| 00:00:01 |

|   9 |   TABLE ACCESS FULL    | JOBS2        |   19 |   494 |     3  (0)| 00:00:01 |

|  10 |  TABLE ACCESS FULL     |EMPLOYEES2   |   107 | 2889 |     3   (0)| 00:00:01 |

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

 

Predicate Information(identified by operation id):

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

 

   1 -access("EMP"."MANAGER_ID"="MGR"."EMPLOYEE_ID")

   2 -access("EMP"."JOB_ID"="J"."JOB_ID")

   3 -access("EMP"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"AND

             "D"."LOCATION_ID"="L"."LOCATION_ID")

   5 -filter("L"."CITY"=:LOC)

全部通过全表扫描进行搜索

1.3建立第一个索引

根据之前的执行计划，可以在locations2的city上简历索引

SQL> create indexl_city_ind on locations2(city);

Execution Plan

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

Plan hash value: 2475540708

 

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

| Id  | Operation                       | Name         | Rows | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT                |              |     1 |  277 |    16  (13)| 00:00:01 |

|*  1 | HASH JOIN                      |              |     1 |  277 |    16  (13)| 00:00:01 |

|*  2 |  HASH JOIN                    |              |     1 |  250 |    12   (9)| 00:00:01 |

|*  3 |   HASH JOIN                   |              |     1 |  224 |     9  (12)| 00:00:01 |

|   4 |    MERGE JOIN CARTESIAN        |              |    25 | 4525 |     5   (0)| 00:00:01 |

|   5 |     TABLE ACCESS BY INDEX ROWID| LOCATIONS2  |     1 |    74 |    2   (0)| 00:00:01 |

|*  6 |      INDEX RANGE SCAN          | L_CITY_IND   |    1 |       |     1  (0)| 00:00:01 |

|   7 |     BUFFER SORT                |              |   107 | 11449 |     3  (0)| 00:00:01 |

|   8 |      TABLE ACCESS FULL         |EMPLOYEES2   |   107 | 11449 |     3  (0)| 00:00:01 |

|   9 |    TABLE ACCESS FULL           |DEPARTMENTS2 |    27 |  1161 |    3   (0)| 00:00:01 |

|  10 |   TABLE ACCESS FULL            |JOBS2        |    19 |  494 |     3   (0)| 00:00:01 |

|  11 |  TABLE ACCESS FULL             |EMPLOYEES2   |   107 | 2889 |     3  (0)| 00:00:01 |

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

 

Predicate Information(identified by operation id):

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

 

   1 - access("EMP"."MANAGER_ID"="MGR"."EMPLOYEE_ID")

   2 -access("EMP"."JOB_ID"="J"."JOB_ID")

   3 -access("EMP"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"AND

             "D"."LOCATION_ID"="L"."LOCATION_ID")

   6 -access("L"."CITY"=:LOC)

1.4建立第二个索引：

根据对上一个执行计划的分析，可以在deparments2的LOCATION_ID上建立索引

SQL> create indexdepart_id_ind on DEPARTMENTS2(LOCATION_ID);

Execution Plan

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

Plan hash value: 2878015583

 

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

| Id  | Operation                        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT                 |               |     1 |  277 |    13   (8)| 00:00:01 |

|*  1 | HASH JOIN                      |               |     1 |  277 |    13   (8)| 00:00:01 |

|*  2 |  HASH JOIN                     |               |     1 |  251 |    10  (10)| 00:00:01 |

最低0.47元/天 解锁文章
gudagudaga
关注
10053事件分析
数据库
04-22 1046
10053内容： 参数区=>初始化参数，隐含参数，这些参数可以左右oracle工作方式 sql区=>执行的sql语句，是否使用绑定变量，是否进行了转换操作 系统信息区=>操作系统统计信息，cpu主频cpu执行事件io寻址时间、单块读时间、多块读时间 数据访问方式=>访问方式不一样计算代价的方法也不一样，全表扫描走索引多表关联代价都不同 关联查询=>把每张表都作为驱动表去组合，择优选择“代
表连接和索引总结
qq_37440335的博客
07-16 2288
   nest loops hash join sort merge join 原理 先返回第一个表的结果集，然后依次循环地用第一个表的结果集中的一条结果，去匹配第二个表中的数据。因此，第一个表取出多少条数据，第二个表就会被访问多少次。 先分别在两个表先hash运算后，缓存起来，使这些数据相对有序（但本身不排序），然后再通过比较相同的hash值整体进行表连接，所以每一个表最多将被访问一次...
Mysql的多表查询和索引
最新发布
Raccon_的博客
08-22 898
当两个表查询时，从第一张表中取出一行和第二张表的每一行进行组合返回结果含有两张表的所有列，一共返回的记录数第一张表行数*第二张表的行数（
复合索引的优点和注意事项
weixin_34310369的博客
01-20 191
概念:     单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上;     用户可以在多个列上建立索引,这种索引叫做复合索引(组合索引);     复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引;     同时有两个概念叫做窄索引和宽索引,窄索引是指索引列为1-2列的索引,宽索引也就是索引列超过2列的索引;     设计索引的一个重要原则就是能用窄索引不用宽索引,因为窄...
sql多表联查，索引
mymk的博客
12-27 3955
sql多表联查，索引
mysql 建复合索引_mysql 建立复合索引
weixin_42627459的博客
01-18 7222
[size=medium]为什么使用数据索引能提高效率[/size]数据索引的存储是有序的 在有序的情况下，通过索引查询一个数据是无需遍历索引记录的 极端情况下，数据索引的查询效率为二分法查询效率，趋近于 log2(N)对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a...
mysql 建复合索引_关于mysql建立索引 复合索引 索引类型
weixin_28996657的博客
01-18 4528
这两天有个非常强烈的感觉就是自己在一些特别的情况下还是hold不住，脑子easy放空或者说一下子不知道怎么去分析问题了，比方，问“hash和btree索引的差别”，这非常难吗。仅仅要掌握了这两种数据结构稍加分析就能得出答案，结果是一下子不知道从何说起。进入正题吧。这两者有啥差别。1. hash索引查找数据基本上能一次定位数据。当然有大量碰撞的话性能也会下降。而btree索引就得在节点上挨着查找了，...
mysql 唯一索引和复合索引 区别_MySQL复合唯一索引分析
weixin_39822184的博客
01-19 1610
MySQL复合唯一索引分析关于复合唯一索引(unique key 或 unique index)，网上搜索不少人说：”这种索引起到的关键作用是约束，查询时性能上没有得到提高或者查询时根本没有走索引列“。也有人说：“查询时使用到了索引和普通索引一样“。那么问题到底是怎样的呢？测试准备工作准备建表语句，插入数据等工作：-- 建表(注：a,b是复合唯一索引)create table test0(id b...
oracle复合索引列顺序,复合索引顺序选择性问题（一）
weixin_29759387的博客
04-04 3647
索引是我们经常选择的数据表检索优化方案之一。其中，复合索引是我们经常选择的策略。那么，构建索引列的顺序上，有何种差异和需要注意的方面呢？下面我们通过实验来进行说明。实验环境说明准备数据表和实验环境。索引列的差异，主要体现在选择性上，我们通过构建不同选择性的列来进行试验。SQL> conn scott/tiger@orcl;Connected to Oracle DatabaseEnterpr...
mysql复合索引abcac可以索引吗_MySQL联合索引
weixin_35940111的博客
01-19 6601
在一次查询中，MySQL只能使用一个索引。在真实项目中，SQL语句中的WHERE子句里通常会包含多个查询条件还会有排序、分组等。若表中索引过多，会影响INSERT及UPDATE性能，简单说就是会影响数据写入性能。因为更新数据的同时，也要同时更新索引。最实际的好处当然是查询速度快，性能好。MYSQL中常用的强制性操作(例如强制索引)https://www.jb51.net/article/49807...
复合索引（组合索引）
weixin_54707282的博客
05-21 6184
用户可以在多个列上建立索引，这种索引叫做复合索引(组合索引)。
mysql join 组合索引,图文详解MySQL中两表关联的连接表如何创建索引
weixin_32958777的博客
03-17 822
本文介绍了MySQL中两表关联的连接表是如何创建索引的相关内容，分享出来供大家参考学习，下面来看看详细的介绍：问题介绍创建数据库的索引，可以选择单列索引，也可以选择创建组合索引。遇到如下这种情况，用户表(user)与部门表(dept)通过部门用户关联表(deptuser)连接起来，如下图所示：表间关系问题就是，在这个关联表中该如何建立索引呢？针对该表，有如下四种选择:针对于user_uuid建立单...
多表关联如何建立索引
热门推荐
weixin_42080504的博客
10-22 1万+
我是用的三张表进行关联的，一大两小。下面看一下三张表的具体结构。 三张图对应三张表，然后下面是我写的查询sql select a.*, b.*, c.* from statjiankong_etl a left join ibnr b on a.anadate = b.anadate and a.STARTYEAR = b.startyy and a.comcode2 =...
如何判断使用复合索引还是使用多个单列索引
冷箫轻笛(coolingpipe)的砖篮
06-05 3349
问：比如说列A,B,C,D都是查询条件，且可能以任意组合出现，概率也基本相同。这个时候是应该使用复合索引，还是应该为每个列单独建立索引？希望能够介绍一下选择这2种方式的原则，谢谢答：看看 ABCD 四个列各自的长度.如果长度太长, 一般不用考虑将它放在复合索引中, 因为单独用它的概率还是比较高, 所以可以考虑单独为它建立索引考虑各列的数据的重复情况如果数据重复非常严重(例如, DI
Oracle事件之10053 跟踪的trace文件相关解释
demonson的专栏
03-29 1947
一. 10053事件 　　当一个SQL出现性能问题的时候，可以使用SQL_TRACE 或者 10046事件来跟踪SQL. 通过生成的trace来了解SQL的执行过程。 我们在查看一条SQL的执行计划的时候，只能看到CBO 最终告诉我们的执行计划结果，但是不知道CBO 是根据什么来做的。 如果遇到了执行计划失真，如：一个SQL语句，很明显oracle应该使用索引，但是执行计划却没有使用索引...
Oracle多表连接优化策略
Oracle数据库在处理多表查询时，其效率与单表查询相比有显著差异，主要因为Oracle对简单表提供了高速缓冲（Cache Buffering）优化，但这一优化并不直接适用于复杂的多表连接查询。为了提高多表查询的性能，数据库...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值