AND_EQUAL和组合索引优化一条SQL

最新推荐文章于 2021-04-04 20:42:07 发布
最新推荐文章于 2021-04-04 20:42:07 发布
阅读量1k 收藏
点赞数
分类专栏： oracle SQL优化 文章标签： 优化 access join table filter merge
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wadekobe9/article/details/7740320
版权 
在系统负载高的时候抓出一条SQL，1分23秒左右出152行
select a.Case_ID,
       a.Case_Type_ID,
       a.Case_Subject,
       a.Case_Content,
       a.Create_Employee_ID,
       a.Case_Create_Time,
       a.Customer_ID,
       a.Dept_ID,
       a.Lock_Employee_ID,
       a.Order_ID,
       a.Order_Type_ID,
       a.Pri,
       a.Customer_Name,
       a.Operate_Time,
       a.Case_Require_Finish_Time,
       a.Case_Status,
       b.Employee_ID,
       b.Case_Op_Finish_Time,
       c.Case_Type_Name,
       d.Employee_Name,
       e.Employee_Name as Lock_Employee,
       f.Dept_Name,
       g.Position_Name,
       h.Dept_Name as Create_Dept_Name,
       F_GETURGENCYORDER(a.Order_ID) IsUrgency
  from t_S_Case a
  left join t_S_Case_Operation_Record b
    on a.Case_ID = b.Case_Op_ID
  left join t_S_Employee d
    on d.employee_id = a.create_employee_id
  left join t_S_Employee e
    on e.employee_id = a.Lock_Employee_ID
  left join t_S_Department f
    on a.Dept_ID = f.dept_id
  left join t_S_Department h
    on d.Dept_ID = h.dept_id
 inner join t_SD_Case_Type c
    on a.Case_Type_ID = c.Case_Type_ID
  left join t_SD_Emplyee_Position g
    on g.Position_ID = a.Position_ID
 where a.Case_Status = 0
   and a.Position_ID IN (6017)
 order by IsUrgency desc, a.Case_Create_Time, a.Pri Desc
 
 Select * From Table(dbms_xplan.display)
 
 
 Plan hash value: 3249879879
 
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                           | 19273 |  5778K|       |  4401   (4)| 00:00:53 |
|   1 |  SORT ORDER BY                       |                           | 19273 |  5778K|    12M|  4401   (4)| 00:00:53 |
|*  2 |   HASH JOIN RIGHT OUTER              |                           | 19273 |  5778K|       |  3121   (5)| 00:00:38 |
|   3 |    TABLE ACCESS FULL                 | T_S_EMPLOYEE              |  2018 | 28252 |       |     8   (0)| 00:00:01 |
|*  4 |    HASH JOIN RIGHT OUTER             |                           | 19273 |  5514K|       |  3111   (5)| 00:00:38 |
|   5 |     TABLE ACCESS FULL                | T_S_DEPARTMENT            |    84 |  1764 |       |     5   (0)| 00:00:01 |
|*  6 |     HASH JOIN RIGHT OUTER            |                           | 19273 |  5119K|       |  3105   (5)| 00:00:38 |
|   7 |      TABLE ACCESS FULL               | T_S_EMPLOYEE              |  2018 | 36324 |       |     8   (0)| 00:00:01 |
|*  8 |      HASH JOIN RIGHT OUTER           |                           | 19273 |  4780K|       |  3096   (5)| 00:00:38 |
|   9 |       TABLE ACCESS FULL              | T_S_DEPARTMENT            |    84 |  1764 |       |     5   (0)| 00:00:01 |
|* 10 |       HASH JOIN                      |                           | 19273 |  4385K|       |  3090   (5)| 00:00:38 |
|  11 |        TABLE ACCESS FULL             | T_SD_CASE_TYPE            |    17 |   323 |       |     6   (0)| 00:00:01 |
|* 12 |        HASH JOIN OUTER               |                           | 19273 |  4027K|  3920K|  3082   (5)| 00:00:37 |
|  13 |         MERGE JOIN OUTER             |                           | 19273 |  3688K|       |   754   (1)| 00:00:10 |
|* 14 |          TABLE ACCESS BY INDEX ROWID | T_S_CASE                  | 19273 |  3425K|       |   752   (1)| 00:00:10 |
|* 15 |           INDEX RANGE SCAN           | IDX_T_S_CASE_STATUS       | 51528 |       |       |    33   (4)| 00:00:01 |
|* 16 |          SORT JOIN                   |                           |     1 |    14 |       |     2  (50)| 00:00:01 |
|  17 |           TABLE ACCESS BY INDEX ROWID| T_SD_EMPLYEE_POSITION     |     1 |    14 |       |     1   (0)| 00:00:01 |
|* 18 |            INDEX UNIQUE SCAN         | PK_T_SD_EMPLYEE_POSITION  |     1 |       |       |     1   (0)| 00:00:01 |
|  19 |         TABLE ACCESS FULL            | T_S_CASE_OPERATION_RECORD |   589K|    10M|       |  1257   (6)| 00:00:16 |
--------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("E"."EMPLOYEE_ID"(+)="A"."LOCK_EMPLOYEE_ID")
   4 - access("D"."DEPT_ID"="H"."DEPT_ID"(+))
   6 - access("D"."EMPLOYEE_ID"(+)="A"."CREATE_EMPLOYEE_ID")
   8 - access("A"."DEPT_ID"="F"."DEPT_ID"(+))
  10 - access("A"."CASE_TYPE_ID"="C"."CASE_TYPE_ID")
  12 - access("A"."CASE_ID"="B"."CASE_OP_ID"(+))
  14 - filter("A"."POSITION_ID"=6017)
  15 - access("A"."CASE_STATUS"=0)
  16 - access("G"."POSITION_ID"(+)="A"."POSITION_ID")
       filter("G"."POSITION_ID"(+)="A"."POSITION_ID")
  18 - access("G"."POSITION_ID"(+)=6017)

====================================================================
 分析：
 Select Count(*) From t_S_Case Where Case_Status = 0         --56869
 Select Count(*) From t_S_Case Where Position_ID IN (6017)  --783131
 这两个列上面都有索引，执行计划用的是Case_Status上面的索引，我的第一
 反应是将这个两个列建立个组合索引，但是后面发现case_status已经存在
 于另外一个组合索引里面了，要同时用上这个两个索引访问一张表，只能用
 AND_EQUAL的Hints了，CBO中一般是不会在同一张表上选中两个索引一起去
 访问的。
 
====================================================================
加Hints的SQL，6秒出结果
select  /*+ AND_EQUAL(a IDX_T_S_CASE_STATUS IDX_T_S_CASE_POSITION) */
       a.Case_ID,
       a.Case_Type_ID,
       a.Case_Subject,
       a.Case_Content,
       a.Create_Employee_ID,
       a.Case_Create_Time,
       a.Customer_ID,
       a.Dept_ID,
       a.Lock_Employee_ID,
       a.Order_ID,
       a.Order_Type_ID,
       a.Pri,
       a.Customer_Name,
       a.Operate_Time,
       a.Case_Require_Finish_Time,
       a.Case_Status,
       b.Employee_ID,
       b.Case_Op_Finish_Time,
       c.Case_Type_Name,
       d.Employee_Name,
       e.Employee_Name as Lock_Employee,
       f.Dept_Name,
       g.Position_Name,
       h.Dept_Name as Create_Dept_Name,
       F_GETURGENCYORDER(a.Order_ID) IsUrgency
  from t_S_Case a
  left join t_S_Case_Operation_Record b
    on a.Case_ID = b.Case_Op_ID
  left join t_S_Employee d
    on d.employee_id = a.create_employee_id
  left join t_S_Employee e
    on e.employee_id = a.Lock_Employee_ID
  left join t_S_Department f
    on a.Dept_ID = f.dept_id
  left join t_S_Department h
    on d.Dept_ID = h.dept_id
 inner join t_SD_Case_Type c
    on a.Case_Type_ID = c.Case_Type_ID
  left join t_SD_Emplyee_Position g
    on g.Position_ID = a.Position_ID
 where a.Case_Status = 0
   and a.Position_ID IN (6017)
 order by IsUrgency desc, a.Case_Create_Time, a.Pri Desc
Plan hash value: 3864087155
 
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                           | 19273 |  5778K|       |  9431   (2)| 00:01:54 |
|   1 |  SORT ORDER BY                       |                           | 19273 |  5778K|    12M|  9431   (2)| 00:01:54 |
|*  2 |   HASH JOIN RIGHT OUTER              |                           | 19273 |  5778K|       |  8151   (2)| 00:01:38 |
|   3 |    TABLE ACCESS FULL                 | T_S_EMPLOYEE              |  2018 | 28252 |       |     8   (0)| 00:00:01 |
|*  4 |    HASH JOIN RIGHT OUTER             |                           | 19273 |  5514K|       |  8142   (2)| 00:01:38 |
|   5 |     TABLE ACCESS FULL                | T_S_DEPARTMENT            |    84 |  1764 |       |     5   (0)| 00:00:01 |
|*  6 |     HASH JOIN RIGHT OUTER            |                           | 19273 |  5119K|       |  8136   (2)| 00:01:38 |
|   7 |      TABLE ACCESS FULL               | T_S_EMPLOYEE              |  2018 | 36324 |       |     8   (0)| 00:00:01 |
|*  8 |      HASH JOIN RIGHT OUTER           |                           | 19273 |  4780K|       |  8126   (2)| 00:01:38 |
|   9 |       TABLE ACCESS FULL              | T_S_DEPARTMENT            |    84 |  1764 |       |     5   (0)| 00:00:01 |
|* 10 |       HASH JOIN                      |                           | 19273 |  4385K|       |  8120   (2)| 00:01:38 |
|  11 |        TABLE ACCESS FULL             | T_SD_CASE_TYPE            |    17 |   323 |       |     6   (0)| 00:00:01 |
|* 12 |        HASH JOIN OUTER               |                           | 19273 |  4027K|  3920K|  8113   (2)| 00:01:38 |
|  13 |         MERGE JOIN OUTER             |                           | 19273 |  3688K|       |  5784   (1)| 00:01:10 |
|* 14 |          TABLE ACCESS BY INDEX ROWID | T_S_CASE                  | 19273 |  3425K|       |  5782   (1)| 00:01:10 |
|  15 |           AND-EQUAL                  |                           |       |       |       |            |          |
|* 16 |            INDEX RANGE SCAN          | IDX_T_S_CASE_STATUS       | 51528 |       |       |    33   (4)| 00:00:01 |
|* 17 |            INDEX RANGE SCAN          | IDX_T_S_CASE_POSITION     |   762K|       |       |   413   (4)| 00:00:05 |
|* 18 |          SORT JOIN                   |                           |     1 |    14 |       |     2  (50)| 00:00:01 |
|  19 |           TABLE ACCESS BY INDEX ROWID| T_SD_EMPLYEE_POSITION     |     1 |    14 |       |     1   (0)| 00:00:01 |
|* 20 |            INDEX UNIQUE SCAN         | PK_T_SD_EMPLYEE_POSITION  |     1 |       |       |     1   (0)| 00:00:01 |
|  21 |         TABLE ACCESS FULL            | T_S_CASE_OPERATION_RECORD |   589K|    10M|       |  1257   (6)| 00:00:16 |
--------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("E"."EMPLOYEE_ID"(+)="A"."LOCK_EMPLOYEE_ID")
   4 - access("D"."DEPT_ID"="H"."DEPT_ID"(+))
   6 - access("D"."EMPLOYEE_ID"(+)="A"."CREATE_EMPLOYEE_ID")
   8 - access("A"."DEPT_ID"="F"."DEPT_ID"(+))
  10 - access("A"."CASE_TYPE_ID"="C"."CASE_TYPE_ID")
  12 - access("A"."CASE_ID"="B"."CASE_OP_ID"(+))
  14 - filter("A"."CASE_STATUS"=0 AND "A"."POSITION_ID"=6017)
  16 - access("A"."CASE_STATUS"=0)
  17 - access("A"."POSITION_ID"=6017)
  18 - access("G"."POSITION_ID"(+)="A"."POSITION_ID")
       filter("G"."POSITION_ID"(+)="A"."POSITION_ID")
  20 - access("G"."POSITION_ID"(+)=6017)


====================================================================
快是快了，但是SQL是拼出来的，不一定每次就是这两个列，还有一种方法，
将原来的那个组合索引删除，原来的组合索引由CASE_STATUS,DEPT_ID组成
建立下面组合索引
Create Index ind_zu_case_dept_pos On T_S_CASE(CASE_STATUS,DEPT_ID,POSITION_ID) Nologging Online 
索引建立建立好之后并没有用上，仍然用的是CASE_STATUS列上的IDX_T_S_CASE_STATUS，我将其也删除
Drop Index IDX_T_S_CASE_STATUS 
然后再次运行，组合索引就可以使用了，难到是组合索引的前导列不能再单独
存在索引。
最后的执行计划如下，然后是不到1秒出结果：
Plan hash value: 3915948668
 
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                           | 20799 |  6215K|       |  5225   (3)| 00:01:03 |
|   1 |  SORT ORDER BY                       |                           | 20799 |  6215K|    13M|  5225   (3)| 00:01:03 |
|*  2 |   HASH JOIN RIGHT OUTER              |                           | 20799 |  6215K|       |  3848   (4)| 00:00:47 |
|   3 |    TABLE ACCESS FULL                 | T_S_EMPLOYEE              |  2018 | 28252 |       |     8   (0)| 00:00:01 |
|*  4 |    HASH JOIN RIGHT OUTER             |                           | 20799 |  5930K|       |  3838   (4)| 00:00:47 |
|   5 |     TABLE ACCESS FULL                | T_S_DEPARTMENT            |    84 |  1764 |       |     5   (0)| 00:00:01 |
|*  6 |     HASH JOIN RIGHT OUTER            |                           | 20799 |  5504K|       |  3832   (4)| 00:00:46 |
|   7 |      TABLE ACCESS FULL               | T_S_EMPLOYEE              |  2018 | 36324 |       |     8   (0)| 00:00:01 |
|*  8 |      HASH JOIN RIGHT OUTER           |                           | 20799 |  5138K|       |  3822   (4)| 00:00:46 |
|   9 |       TABLE ACCESS FULL              | T_S_DEPARTMENT            |    84 |  1764 |       |     5   (0)| 00:00:01 |
|* 10 |       HASH JOIN                      |                           | 20799 |  4712K|       |  3816   (4)| 00:00:46 |
|  11 |        TABLE ACCESS FULL             | T_SD_CASE_TYPE            |    17 |   323 |       |     6   (0)| 00:00:01 |
|* 12 |        HASH JOIN OUTER               |                           | 20799 |  4326K|  4208K|  3809   (4)| 00:00:46 |
|  13 |         MERGE JOIN OUTER             |                           | 20799 |  3960K|       |  1466   (1)| 00:00:18 |
|  14 |          TABLE ACCESS BY INDEX ROWID | T_S_CASE                  | 20799 |  3676K|       |  1464   (1)| 00:00:18 |
|* 15 |           INDEX RANGE SCAN           | IND_ZU_CASE_DEPT_POS      | 21600 |       |       |    41   (3)| 00:00:01 |
|* 16 |          SORT JOIN                   |                           |     1 |    14 |       |     2  (50)| 00:00:01 |
|  17 |           TABLE ACCESS BY INDEX ROWID| T_SD_EMPLYEE_POSITION     |     1 |    14 |       |     1   (0)| 00:00:01 |
|* 18 |            INDEX UNIQUE SCAN         | PK_T_SD_EMPLYEE_POSITION  |     1 |       |       |     1   (0)| 00:00:01 |
|  19 |         TABLE ACCESS FULL            | T_S_CASE_OPERATION_RECORD |   589K|    10M|       |  1257   (6)| 00:00:16 |
--------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("E"."EMPLOYEE_ID"(+)="A"."LOCK_EMPLOYEE_ID")
   4 - access("D"."DEPT_ID"="H"."DEPT_ID"(+))
   6 - access("D"."EMPLOYEE_ID"(+)="A"."CREATE_EMPLOYEE_ID")
   8 - access("A"."DEPT_ID"="F"."DEPT_ID"(+))
  10 - access("A"."CASE_TYPE_ID"="C"."CASE_TYPE_ID")
  12 - access("A"."CASE_ID"="B"."CASE_OP_ID"(+))
  15 - access("A"."CASE_STATUS"=0 AND "A"."POSITION_ID"=6017)
       filter("A"."POSITION_ID"=6017)
  16 - access("G"."POSITION_ID"(+)="A"."POSITION_ID")
       filter("G"."POSITION_ID"(+)="A"."POSITION_ID")
  18 - access("G"."POSITION_ID"(+)=6017)

wadekobe9
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
master and slave have equal MySQL server ids
01-19
代码如下:Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the –replicate-same-...
and_equal,index_join,index_combine
abc123098098的博客
09-10 105
and_equal,index_join,index_combine这三种都是oracle利用索引关联获得数据的方法,三者的目的都是为了最大限度的利用索引,减少回表的代价.但是三者的实现方法是有区别的,下面一一来分析. and_equal: 这种方式需要查询条件里面包括所有索引列,然后取得每个索引中得到的rowid列表,然后对这些列表做merge join,过滤出相同的rowid后再...
oracle 表combine,oracle index --and_equal與INDEX_COMBINE與index_join
weixin_42547431的博客
04-04 218
在我自己的實驗結果中，DB計算的cost全表竟然比標題中任何一種index join方法都要低很多倍，但實際執行速度卻要慢很多倍，當然這裡沒有考慮數據傾斜等因素因此，我找到自己問題的答案------為什麼在一個語句的表中，明明有多個索引可以同時使用(join)，增加效率及篩選，而oracle卻只能使用其中一個索引，這是因為oracle執行路徑的cost作怪，這時用hit可以明顯提高執行效率10g中...
oracle index --and_equal與INDEX_COMBINE與index_join
cizeb5816的博客
04-19 204
在我自己的實驗結果中，DB計算的cost全表竟然比標題中任何一種index join方法都要低很多倍，但實際執行速度卻要慢很多倍，當然這裡沒有考慮數據傾斜等因素 因此，我找到自己問題的答案------為什麼在一個語句的表中，明...
Oracle技术之Index Merge与and_equal 的变迁
weixin_34087503的博客
07-12 302
and_equal是Oracle支持的一种特定操作，可以将多个单列索引进行合并（Index Merge）输出查询结果。同时and_equal可以通过Hints来进行强制，最少指定两个索引，最多指定5个。以下的执行计划是常见的and_equal执行方式：SQL> select /*+ and_equal(t1 iu ii) */ username,password fr...
如何理解AND-EQUAL！
clt3617的博客
05-02 146
当我第一次看到hint相关内容中讲到AND_EQUAL时（The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that...
Matlab.zip_equal3zg_matlab源代码
09-15
多个matlab代码，十分适合刚接触matlab的同学
master and slave have equal MySQL server UUIDs 解决方法
12-15
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. 首先检查：mysql> show variables like ‘server_id...
05 实验五 数据存储和提供器.rar_equal1mb_安卓_安卓通讯录_通讯录
09-15
安卓通讯录 通过简单的介绍，告诉我们如何初步建设安卓通讯录
RD.zip_RD_equal9x8_rd算法_zip_点目标
09-21
RD算法，点目标
and_equal:
cqcj2023的博客
02-09 108
description:The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans...
BITMAP CONVERSION TO ROWIDS 导致sql变慢
weixin_34375251的博客
11-20 160
我们的sql 5分钟没有跑完，查看计划，走了BITMAP CONVERSION TO ROWIDS。 SQL>explainplanfor2selecta.username,3a.membertype,4a.providername,5a.tradename,6...
强制索引的方法
fei的专栏
02-04 5654
一般来说强制索引都是采用/*+INDEX(表名,索引名字)*/ 的方法,但是这种方法有个缺点，如果索引的名字改变了，就会导致程序重新改动，大大增加维护成本。   其实索引提示还可以使用列的方法进行,语法是/*+INDEX(表名,(索引列的列表))*/,这种方法即使索引的名字变了，也不会导致程序重新改动，除非索引列的顺序变化了。   看如下一个简单例子：   SQ
查看存储过程的执行计划
阿飞
07-06 8532
存储过程没有执行计划，查看的是存储过程中SQL语句的执行计划，这里用10046来看 ---------------------------------------------------- 创建存储过程 Create Or Replace Procedure Lee_Xc(Eno Number) Is Begin Select Empno, Ename, Dname From
常用的几种Hints优化一条SQL
阿飞
07-03 3475
环境：  oracle 10g R2  Select Count(*) From t_Ho_Order_Statistics --2032946 Select Count(*) From t_Ho_Order_Info       --2032946 其他都是小的维度表 统计信息已经检查过了，差不多10天前的（不过我10天前跑过这个SQL,出来的执行计划一样）， 这里，这里就把注意力集中在两个大表
子查询解嵌套导致SQL变慢
阿飞
07-03 3379
原SQL如下，跑4分钟左右 Select Guest_Id, t_To_Order_Tickets.Order_Id, t_To_Order_Info.Contact_Mobile, Guest_Name, Guest_Idno, Departure_Time, Trip_Num, Arrival_
assert_equal
最新发布
03-08
assert_equal是一种用于测试代码的断言函数它用于比较两个值是否相，并在值不相等时抛出异常来表示测试失败。通常在单元测试中使用assert_equal来验证代码的正确性。 assert_equal函数通常接受两个参数：期望值和...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值