【oracle 性能优化】组合索引查询。

在Oracle中可以创建组合索引，即同时包含两个或两个以上列的索引。组合索引的使用存在着一定的局限，只有在谓词中出现全部索引列时才能使用效率最高的index unique scan, 否则谓词中必须包含前导列，否则会走Index full scan或者FTS。

SQL> create index idx_test on yangtest (object_type,object_name);

索引已创建。

SQL> exec dbms_stats.gather_table_stats(user,'YANGTEST',cascade=>true);

PL/SQL 过程已成功完成。

已用时间:  00: 00: 20.78

SQL> select object_type,count(*) from yangtest group by object_type order by 2;

OBJECT_TYPE           COUNT(*)

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

EDITION                      1

RULE                         1

MATERIALIZED VIEW            1 

SCHEDULE                     2 

WINDOW GROUP                 4

DIRECTORY                    5 

UNDEFINED                    6 

LOB PARTITION                7 

RESOURCE PLAN                7 

CONTEXT                      7 

WINDOW                       9

CLUSTER                     10

JOB                         11 

EVALUATION CONTEXT          11 

INDEXTYPE                   11

JOB CLASS                   13

CONSUMER GROUP              14

RULE SET                    17

PROGRAM                     18

QUEUE                       33

OPERATOR                    57

XML SCHEMA                  91

TABLE PARTITION            108 

INDEX PARTITION            128

PROCEDURE                  131

LIBRARY                    179

TYPE BODY                  224

SEQUENCE                   227 

FUNCTION                   296

JAVA DATA                  324

TRIGGER                    482

LOB                        760 

JAVA RESOURCE              833 

PACKAGE BODY              1206

PACKAGE                   1267

TABLE                     2543 

TYPE                      2616

INDEX                     3194 

VIEW                      4749 

JAVA CLASS               22103 

SYNONYM                  26670 

已选择41行。

已用时间:  00: 00: 00.09

1、当使用基于规则的优化器（RBO）时，只有当组合索引的前导列出现在SQL语句的where子句中时，才会使用到该索引；

SQL> set autot trace

SQL> select /*+ rule */ * from yangtest where object_type='JOB';

已选择11行。

已用时间:  00: 00: 00.07

执行计划

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

Plan hash value: 2067289980 

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

| Id  | Operation                   | Name     | 

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

|   0 | SELECT STATEMENT            |          |

|   1 |  TABLE ACCESS BY INDEX ROWID| YANGTEST |

|*  2 |   INDEX RANGE SCAN          | IDX_TEST |

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

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_TYPE"='JOB')

Note       

-----      

   - rule based optimizer used (consider using cbo)

统计信息

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

          1  recursive calls

          0  db block gets 

         13  consistent gets

          0  physical reads 

          0  redo size   

       2310  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory) 

          0  sorts (disk) 

         11  rows processed 

-- 而让CBO自己选择时，却选择了TFS，从信息统计里面可以看出consistent gets 是前者的100倍。CBO 也不一定很聪明。

SQL> select * from yangtest where object_type='JOB';

已选择11行。

已用时间:  00: 00: 00.03

执行计划

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

Plan hash value: 911235955 

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

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

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

|   0 | SELECT STATEMENT  |          |  1668 |   164K|   275   (1)| 00:00:04 | 

|*  1 |  TABLE ACCESS FULL| YANGTEST |  1668 |   164K|   275   (1)| 00:00:04 | 

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

Predicate Information (identified by operation id):  

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

   1 - filter("OBJECT_TYPE"='JOB')  

统计信息

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

        264  recursive calls     

          0  db block gets   

       1050  consistent gets   

          0  physical reads    

          0  redo size        

       2006  bytes sent via SQL*Net to client 

        416  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client 

          0  sorts (memory) 

          0  sorts (disk) 

         11  rows processed

由于使用了组合索引的前导列并且访问了表中的少量记录，Oracle明智地选择了索引扫描。那么，如果我们访问表中的大量数据时，Oracle会选择什么样的访问路径呢？看下面的测试：

SQL> select * from yangtest where object_type='SYNONYM';

已选择26670行。

已用时间:  00: 00: 01.42

执行计划

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

Plan hash value: 911235955

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

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

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

|   0 | SELECT STATEMENT  |          |  1668 |   164K|   275   (1)| 00:00:04 | 

|*  1 |  TABLE ACCESS FULL| YANGTEST |  1668 |   164K|   275   (1)| 00:00:04 | 

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

Predicate Information (identified by operation id):  

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

   1 - filter("OBJECT_TYPE"='SYNONYM')

统计信息

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

          1  recursive calls 

          0  db block gets  

       2769  consistent gets 

          0  physical reads  

          0  redo size 

    1228701  bytes sent via SQL*Net to client 

      19963  bytes received via SQL*Net from client 

       1779  SQL*Net roundtrips to/from client 

          0  sorts (memory)     

          0  sorts (disk)    

      26670  rows processed 

测试一下是使用RULE 的优化器。

SQL> select /*+ rule */ * from yangtest where object_type='SYNONYM';

已选择26670行。

已用时间:  00: 00: 01.56

执行计划

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

                    

Plan hash value: 2067289980 

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

| Id  | Operation                   | Name     | 

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

|   0 | SELECT STATEMENT            |          |

|   1 |  TABLE ACCESS BY INDEX ROWID| YANGTEST |

|*  2 |   INDEX RANGE SCAN          | IDX_TEST |

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

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_TYPE"='SYNONYM')

Note       

-----      

   - rule based optimizer used (consider using cbo)

统计信息

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

          1  recursive calls  

          0  db block gets  

      23543  consistent gets --明显比cbo的执行计划的多10倍。

          0  physical reads  

          0  redo size      

    3235078  bytes sent via SQL*Net to client   

      19963  bytes received via SQL*Net from client

       1779  SQL*Net roundtrips to/from client   

          0  sorts (memory)    

          0  sorts (disk)    

      26670  rows processed  

  从以上结果可以看出，在访问大量数据的情况下，使用索引确实会导致更高的执行成本，这从statistics部分的逻辑读取数（consistent gets）就可以看出，使用索引导致的逻辑读取数是不使用索引导致的逻辑读的10倍还多。因此，Oracle明智地选择了全表扫描而不是索引扫描。

下面，让我们来看看where子句中没有索引前导列的情况：

SQL> select * from yangtest where object_name ='EMP';

已用时间:  00: 00: 00.01

执行计划

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

Plan hash value: 4208055961

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

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

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

|   0 | SELECT STATEMENT            |          |     2 |   202 |    45   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| YANGTEST |     2 |   202 |    45   (0)| 00:00:01 | 

|*  2 |   INDEX SKIP SCAN           | IDX_TEST |     2 |       |    43   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):   

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

   2 - access("OBJECT_NAME"='EMP') 

       filter("OBJECT_NAME"='EMP') 

统计信息

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

          1  recursive calls  

          0  db block gets  

         35  consistent gets 

          1  physical reads 

          0  redo size    

       1337  bytes sent via SQL*Net to client

        416  bytes received via SQL*Net from client  

          2  SQL*Net roundtrips to/from client  

          0  sorts (memory)            

          0  sorts (disk)     

          1  rows processed  

SQL> select * from yangtest where object_name ='YANGTEST';

未选定行

已用时间:  00: 00: 00.01

执行计划

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

Plan hash value: 4208055961 

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

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

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

|   0 | SELECT STATEMENT            |          |     2 |   202 |    45   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| YANGTEST |     2 |   202 |    45   (0)| 00:00:01 |

|*  2 |   INDEX SKIP SCAN           | IDX_TEST |     2 |       |    43   (0)| 00:00:01 |

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

Predicate Information (identified by operation id): 

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

   2 - access("OBJECT_NAME"='YANGTEST')  

       filter("OBJECT_NAME"='YANGTEST')  

统计信息

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

          1  recursive calls   

          0  db block gets     

         27  consistent gets   

          0  physical reads     

          0  redo size         

       1124  bytes sent via SQL*Net to client

        405  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          0  sorts (memory)      

          0  sorts (disk)       

          0  rows processed    

 没有使用前导列，Oracle正确地选择了索引跳跃扫描。我们再来看看如果不使用索引跳跃扫描，该语句的成本：

SQL> select /*+ NO_INDEX(YANGTEST,IDX_TEST)*/ * from yangtest where object_name ='DEPT';

已用时间:  00: 00: 00.01

执行计划

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

Plan hash value: 911235955     

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

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

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

|   0 | SELECT STATEMENT  |          |     2 |   202 |   275   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| YANGTEST |     2 |   202 |   275   (1)| 00:00:04 |

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

Predicate Information (identified by operation id):  

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

   1 - filter("OBJECT_NAME"='DEPT') 

统计信息

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

          1  recursive calls    

          0  db block gets     

       1011  consistent gets  --是使用索引跳跃扫描的50倍左右

          0  physical reads     

          0  redo size         

       1335  bytes sent via SQL*Net to client 

        416  bytes received via SQL*Net from client 

          2  SQL*Net roundtrips to/from client 

          0  sorts (memory)    

          0  sorts (disk)      

          1  rows processed     

SQL> select * from yangtest where object_name like 'T%';

已选择136行。

已用时间:  00: 00: 00.04

执行计划

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

Plan hash value: 911235955     

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

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

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

|   0 | SELECT STATEMENT  |          |   925 | 93425 |   275   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| YANGTEST |   925 | 93425 |   275   (1)| 00:00:04 |

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

Predicate Information (identified by operation id): 

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

   1 - filter("OBJECT_NAME" LIKE 'T%') 

统计信息

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

          1  recursive calls   

          0  db block gets     

       1020  consistent gets    

          0  physical reads    

          0  redo size         

       8900  bytes sent via SQL*Net to client 

        515  bytes received via SQL*Net from client 

         11  SQL*Net roundtrips to/from client  

          0  sorts (memory)    

          0  sorts (disk)      

        136  rows processed 

 这次只选择了136条数据，跟表YANGTEST中总的数据量29489条相比，显然只是很小的一部分，但是Oracle还是选择了全表扫描，有1020 个逻辑读。这种情况下，如果我们强制使用索引.结果如下

SQL> select /*+ INDEX(YANGTEST,IDX_TEST)*/ * from yangtest where object_name like 'T%';

已选择136行。

已用时间:  00: 00: 00.06

执行计划

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

Plan hash value: 972231820     

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

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

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

|   0 | SELECT STATEMENT            |          |   925 | 93425 |  1084   (1)| 00:00:14 |

|   1 |  TABLE ACCESS BY INDEX ROWID| YANGTEST |   925 | 93425 |  1084   (1)| 00:00:14 |

|*  2 |   INDEX FULL SCAN           | IDX_TEST |   925 |       |   424   (1)| 00:00:06 |

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

Predicate Information (identified by operation id): 

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

   2 - access("OBJECT_NAME" LIKE 'T%')

       filter("OBJECT_NAME" LIKE 'T%')

统计信息

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

          1  recursive calls    

          0  db block gets     

        537  consistent gets   

          0  physical reads    

          0  redo size         

      14700  bytes sent via SQL*Net to client

        515  bytes received via SQL*Net from client

         11  SQL*Net roundtrips to/from client

          0  sorts (memory)    

          0  sorts (disk)      

        136  rows processed    

通过添加提示（hint），我们强制Oracle使用了索引扫描（index full scan），执行了335个逻辑读，比使用全表扫描的时候少了一些。

    由此可见，Oracle优化器有时会做出错误的选择，因为它再“聪明”，也不如我们SQL语句编写人员更清楚表中数据的分布，在这种情况下，通过使用提示（hint），我们可以帮助Oracle优化器作出更好的选择。

时间： 2024-08-03 19:09:49

【oracle 性能优化】组合索引查询。的相关文章

Oracle性能优化基本方法详解

Oracle性能优化基本方法包括一下几个步骤,包括: 1)设立合理的Oracle性能优化目标. 2)测量并记录当前的Oracle性能. 3)确定当前Oracle性能瓶颈(Oracle等待什么.哪些SQL语句是该等待事件的成分). 4)把等待事件记入跟踪文件. 5)确定当前的OS瓶颈. 6)优化所需的成分(应用程序.数据库教程.I/O.争用.OS等). 7)跟踪并实施更改控制过程. 8)测量并记录当前性能 9)重复步骤3到7,直到满足优化目标 下面来一一详述. 1.设立合理的Oracle性能优化目

《Oracle数据库性能优化方法论和最佳实践》——1.4　Oracle性能优化工作的分类

1.4 Oracle性能优化工作的分类 在Oracle上进行性能优化时,不同场景下的优化工作方法和内容有很大的不同.下面从实践角度来展开优化工作的分类.1.4.1 上线优化或从未达到过性能期望的系统优化如果业务系统未进行充分的性能测试就上线,那么有相当一部分会出现性能问题,不会出现性能问题的系统往往建立在有强大硬件的基础之上.这类缺乏性能设计考虑的业务系统部分或全部具有以下特点.开发人员(业务系统)假设资源是无限的,可以任意使用,忘记了任何系统都是在一个资源受限的系统中运行业务.开发人员(业务系

《Oracle数据库性能优化方法论和最佳实践》——第1章 Oracle性能优化漫谈 1.1　从生活场景漫谈性能优化

第1章Oracle性能优化漫谈 1.1 从生活场景漫谈性能优化 Oracle数据库性能优化一直是一个让人既胆怯又兴奋的话题,在初级DBA眼里,这是一个神秘的领域,即使是资深的Oracle DBA,也可能无法描述清楚性能优化究竟要做什么,应达成什么目标.那么性能优化究竟是做什么的呢?简而言之,性能优化就是让我们的工作速度变快,快到让我们满意为止.自然,又有读者会问了,我们的工作是什么呢?什么程度才算快,是否可以衡量?看,头疼的问题又来了.1.1.1 从一个真实病例说起 下面是本人的真实经历,也许很

《Oracle数据库性能优化方法论和最佳实践》——1.7　Oracle性能优化的神话和误区

1.7 Oracle性能优化的神话和误区 Oracle性能优化工作是Oracle数据库科学最为神秘莫测的领域,自然也就会流传着各种传言和八卦.本书最主要的目的就是真正使Oracle性能优化成为一门严谨的科学,使任何阅读并且理解本书内容的读者可以比较简单地完成Oracle性能优化工作,使自己在其他人面前成为"巫师"或"神秘的对象".1.7.1 艺术和科学 从百度.Google等网站搜索"性能优化艺术",会出现大量的条目,部分Oracle性能优化的图

《Oracle性能优化与诊断案例精选》——第2章 回首向来萧瑟处，也无风雨也无晴

第2章 回首向来萧瑟处,也无风雨也无晴 Oracle性能优化与诊断案例精选--我的十年Oracle DBA奋斗路(侯圣文) 题记 迄今为止,我觉得这辈子最幸运的两件事,一件是遇见了我太太,另一件就是结识了Oracle.没有早一步也没有晚一步,刚巧赶上了,在最适合谈恋爱的年纪谈了一场没有分手的恋爱,在最适合干事业的年纪做了一份不曾放弃的事业.

《Oracle性能优化与诊断案例精选》导读

前言 Oracle性能优化与诊断案例精选数据驱动,成就未来最近两年来,很多朋友经常会问我,接下来会不会继续写书,会写一本什么样的书. 其实我也一直在思考,什么样的作品能够以最小的篇幅带来超越时间的价值,尽可能地帮助那些准备进入和刚刚进入这个领域的广大技术人员. 本书缘起2015年年底,我在成都和老熊聊天的时候,忽然有了一个想法,如果我能够将云和恩墨的专家团队聚集起来,让每个人都把自己最宝贵的经验方法.经典案例呈现出来,那累计超过100年的从业经验一定可以帮助很多人更深层次地了解数据库技术. 于是

《Oracle性能优化与诊断案例精选》——第1章　三十八载，Oracle伴我同行 1.1　缘起边陲，恰同学风华正茂

第1章 三十八载,Oracle伴我同行 Oracle性能优化与诊断案例精选--记我的职业成长之路(盖国强) 题记 2015年是Oracle公司38周年,2015年我38岁.在Oracle庆祝38岁生日之际,谨以此文作为回顾,记录我的Oracle技术之路.同时也希望可以给走在技术道路上的朋友们以借鉴. 1.1 缘起边陲,恰同学风华正茂 2000年大学毕业时,我在第一份工作中第一次接触到Oracle数据库.那时我作为一个程序员,参与了一个大型企业ERP系统的开发进程.也就是从那时开始,我由网络配置一

《Oracle数据库性能优化方法论和最佳实践》——第2章 Oracle性能优化方法论的发展 2.1　基于局部命中率分析的优化方法论

第2章 Oracle性能优化方法论的发展 Oracle数据库在开发和使用过程中对数据库的性能优化极为重视,几乎在每个版本的更新中都会对可优化的数据库做出改善.不仅如此,Oracle数据库还会使用优化方法来指导性能优化,会不断推出新的性能优化方法论,并依据优化方法论持续完善其可观察的性能优化体系.从Oracle 6到现在的Oracle 12c,经历了Oracle 7.Oracle 8.Oracle 8i.Oracle 9i & R2.Oracle 10gR1 & R2.Oracle 11gR

《oracle性能优化科学与艺术》的读后感

这段时间放假,没事买了一本<oracle性能优化科学与艺术>看看,这本书的主要内容并没有太多的涉及优化的东西,更多是讲解如何处理在oracle优化过程中的人际关系问题,它把整个优化过程分为几个步骤:医师(定义问题)-> 侦探(调查)-> 病理学者(分离原因)-> 艺术家(提出解决问题)-> 魔术师(实施). 很好地概括优化性能的一些过程.自己看后最大的感受如何处理优化过程的人际关系问题,如何找到与说服开发人员接受一些改变.