使用索引优化运行时

大佬教程收集整理的这篇文章主要介绍了使用索引优化运行时，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

如何解决使用索引优化运行时？

开发过程中遇到使用索引优化运行时的问题如何解决？下面主要结合日常开发的经验，给出你关于使用索引优化运行时的解决方法建议，希望对你解决使用索引优化运行时有所启发或帮助；

我有一个分层查询，目前大约 200 万行的执行时间约为 1.1 秒。 如您所见，它以日期分隔。 带索引

     SELECT *
      from ( SELECT * FROM zhr_struct_jmk4
      WHERE
       ID >= 9000000 AND ID <= 100000000
       AND SBEGD <= :iv_begda
        AND sendd >= :iv_endda ) a

      start with objID = '72002506'
      connect BY prior a.ID = a.sobID

数据是一个组织层次结构，职位和人员都以日期分隔。因此日期范围。我正在尝试根据给定日期构建层次结构。

以下是带有和不带有建议索引的两个执行计划。实际上，它现在使用索引运行得更慢了。

sql Statement
----------------------------------------------------------------------------------------------------------------------
SELECT
  *
FROM
  ( SELECT * FROM zhr_struct_jmk4 WHERE ID >= 9000000 AND ID <= 100000000 AND SBEGD <= :A0  AND send
  d >= :A1  ) a start with objID = '72002506' connect BY prior a.ID = a.sobID


Execution Plan

----------------------------------------------------------------------------------------------------------------------
System: HEV
Plan hash value: 1950817222

--------------------------------------------------------------------------------------------------------------
| ID  | Operation                               | name               | Rows  | Bytes | Cost (%cpu)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                    |     5 |  1375 |    26  (12)| 00:00:01 |
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|                    |       |       |            |          |
|   2 |   table ACCESS BY INDEX ROWID BATCHED   | ZHR_STRUCT_JMK4    |  2736 |   665K|    24   (5)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                     | ZHR_STRUCT_JMK4~ZZ |     1 |       |    24   (5)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

query Block name / Object Alias (IDentifIEd by operation ID):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$E029B2FF / ZHR_STRUCT_JMK4@SEL$5
   3 - SEL$E029B2FF / ZHR_STRUCT_JMK4@SEL$5

PreDicate information (IDentifIEd by operation ID):
---------------------------------------------------

   1 - access("ZHR_STRUCT_JMK4"."SOBID"=PRIOR "ZHR_STRUCT_JMK4"."ID")
       filter("ZHR_STRUCT_JMK4"."OBJID"='72002506')
   3 - access("SENDD">=:A1 AND "ID">=9000000 AND "SBEGD"<=:A0 AND "ID"<=100000000)
       filter("ID">=9000000 AND "ID"<=100000000 AND "SENDD">=:A1)

column Projection information (IDentifIEd by operation ID):
-----------------------------------------------------------

   1 - "ZHR_STRUCT_JMK4"."MANDT"[VARCHAR2,9],"ZHR_STRUCT_JMK4"."ID"[numbER,22],"ZHR_STRUCT_JMK4"."SBEGD"[VARCHAR2,24],"ZHR_STRUCT_JMK4"."SENDD"[VARCHAR2,"ZHR_STRUCT_JMK4"."SEQNR"[numbER,"ZHR_STRUCT_JMK4"."SLEVEL"[numbER,"ZHR_STRUCT_JMK4"."OTYPE"[VARCHAR2,6],"ZHR_STRUCT_JMK4"."OBJID"[VARCHAR2,135],"ZHR_STRUCT_JMK4"."PDOWN"[numbER,"ZHR_STRUCT_JMK4"."PNEXT"[numbER,"ZHR_STRUCT_JMK4"."PUP"[numbER,"ZHR_STRUCT_JMK4"."PPREV"[numbER,"ZHR_STRUCT_JMK4"."VRSIGN"[VARCHAR2,3],"ZHR_STRUCT_JMK4"."VRELAT"[VARCHAR2,"ZHR_STRUCT_JMK4"."VBEGDA"[VARCHAR2,"ZHR_STRUCT_JMK4"."vendDA"[VARCHAR2,"ZHR_STRUCT_JMK4"."SOBID"[numbER,PRIOR NulL[22],LEVEL[4]
   2 - "ZHR_STRUCT_JMK4".ROWID[ROWID,10],"ZHR_STRUCT_JMK4"."MANDT"[VARCHAR2,"ID"[numbER,"SBEGD"[VARCHAR2,"SENDD"[VARCHAR2,22]
   3 - "ZHR_STRUCT_JMK4".ROWID[ROWID,22]

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

无索引：

sql Statement
----------------------------------------------------------------------------------------------------------------------
SELECT
  *
FROM
  ( SELECT * FROM zhr_struct_jmk4 WHERE ID >= 9000000 AND ID <= 100000000 AND SBEGD <= :A0  AND send
  d >= :A1  ) a start with objID = '72002506' connect BY prior a.ID = a.sobID


Execution Plan

----------------------------------------------------------------------------------------------------------------------
System: HEV
Plan hash value: 3515252885

-----------------------------------------------------------------------------------------------------------
| ID  | Operation                               | name            | Rows  | Bytes | Cost (%cpu)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                 |     5 |  1375 |  4421  (12)| 00:00:01 |
|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|                 |       |       |            |          |
|*  2 |   table ACCESS FulL                     | ZHR_STRUCT_JMK4 |  2736 |   665K|  4418  (11)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

query Block name / Object Alias (IDentifIEd by operation ID):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$E029B2FF / ZHR_STRUCT_JMK4@SEL$5

PreDicate information (IDentifIEd by operation ID):
---------------------------------------------------

   1 - access("ZHR_STRUCT_JMK4"."SOBID"=PRIOR "ZHR_STRUCT_JMK4"."ID")
       filter("ZHR_STRUCT_JMK4"."OBJID"='72002506')
   2 - filter("ID">=9000000 AND "ID"<=100000000 AND "SBEGD"<=:A0 AND "SENDD">=:A1)

column Projection information (IDentifIEd by operation ID):
-----------------------------------------------------------

   1 - "ZHR_STRUCT_JMK4"."MANDT"[VARCHAR2,LEVEL[4]
   2 - "ZHR_STRUCT_JMK4"."MANDT"[VARCHAR2,22]

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

数据库版本：Oracle 19.9

解决方法

大佬总结

以上是大佬教程为你收集整理的使用索引优化运行时全部内容，希望文章能够帮你解决使用索引优化运行时所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。