文档章节

Oracle中组合索引的使用详解

Zero零_度
 Zero零_度
发布于 2014/12/15 20:23
字数 1906
阅读 22
收藏 0

在Oracle中可以创建组合索引,即同时包含两个或两个以上列的索引。在组合索引的使用方面,Oracle有以下特点:

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

    2、 在使用Oracle9i之前的基于成本的优化器(CBO)时, 只有当组合索引的前导列出现在SQL语句的where子句中时,才可能会使用到该索引,这取决于优化器计算的使用索引的成本和使用全表扫描的成本,Oracle会自动选择成本低的访问路径(请见下面的测试1和测试2);

    3、 从Oracle9i起,Oracle引入了一种新的索引扫描方式——索引跳跃扫描(index skip scan),这种扫描方式只有基于成本的优化器(CBO)才能使用。这样,当SQL语句的where子句中即使没有组合索引的前导列,并且索引跳跃扫描的成本低于其他扫描方式的成本时,Oracle就会使用该方式扫描组合索引(请见下面的测试3);

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

    关于以上情况,我们分别测试如下:

    我们创建测试表T,该表的数据来源于Oracle的数据字典表all_objects,表T的结构如下:

SQL> desc t

名称 是否为空? 类型

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

OWNER NOT NULL VARCHAR2(30)

OBJECT_NAME NOT NULL VARCHAR2(30)

SUBOBJECT_NAME VARCHAR2(30)

OBJECT_ID NOT NULL NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(18)

CREATED NOT NULL DATE

LAST_DDL_TIME NOT NULL DATE

TIMESTAMP VARCHAR2(19)

STATUS VARCHAR2(7)

TEMPORARY VARCHAR2(1)

GENERATED VARCHAR2(1)

SECONDARY VARCHAR2(1)


表中的数据分布情况如下:

SQL> select object_type,count(*) from t group by object_type;

OBJECT_TYPE COUNT(*)

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

CONSUMER GROUP 20

EVALUATION CONTEXT 10

FUNCTION 360

INDEX 69

LIBRARY 20

LOB 20

OPERATOR 20

PACKAGE 1210

PROCEDURE 130

SYNONYM 16100

TABLE 180

TYPE 2750

VIEW 8600

已选择13行。

SQL> select count(*) from t;

COUNT(*)

----------

29489

    我们在表T上创建如下索引并对其进行分析:

SQL> create index indx_t on t(object_type,object_name);

索引已创建。

SQL> ANALYZE TABLE T COMPUTE STATISTICS

2 FOR TABLE

3 FOR ALL INDEXES

4 FOR ALL INDEXED COLUMNS

5 /

表已分析。

现在让我们编写几条SQL语句来测试一下Oracle优化器对访问路径的选择:

    测试1)

SQL> set autotrace traceonly

SQL> SELECT * FROM T WHERE OBJECT_TYPE='LOB';

已选择20行。

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=22 Card=20 Bytes=1740)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=22 Card=20 Bytes=1740)

2 1 INDEX (RANGE SCAN) OF 'INDX_T' (NON-UNIQUE) (Cost=2 Card=20)

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


    测试2)

SQL> SELECT * FROM T WHERE OBJECT_TYPE='SYNONYM';

已选择16100行。

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=16100 Bytes=1400700)

1 0 TABLE ACCESS (FULL) OF 'T' (Cost=38 Card=16100 Bytes=1400700)

Statistics

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

0 recursive calls

0 db block gets

1438 consistent gets

13 physical reads

0 redo size

941307 bytes sent via SQL*Net to client

12306 bytes received via SQL*Net from client

1075 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

16100 rows processed

    很明显,即使使用了组合索引的前导列,但是由于访问了表中的大量数据,Oracle选择了不使用索引而直接使用全表扫描,因为优化器认为全表扫描的成本更低,但事实是不是真的这样的?我们通过增加提示(hint)来强制它使用索引来看看:


SQL> SELECT/**//*+ INDEX (T INDX_T)*/ * FROM T WHERE OBJECT_TYPE='SYNONYM';

已选择16100行。

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16180 Card=16100 Bytes=1400700)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=16180 Card=16100 Bytes=1400700)

2 1 INDEX (RANGE SCAN) OF 'INDX_T' (NON-UNIQUE) (Cost=80 Card=16100)

Statistics

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

0 recursive calls

0 db block gets

17253 consistent gets

16 physical reads

0 redo size

298734 bytes sent via SQL*Net to client

12306 bytes received via SQL*Net from client

1075 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

16100 rows processed

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

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


    测试3)


SQL> select * from t where object_name= 'DEPT';

已选择10行。

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=14 Bytes=1218)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=29 Card=14 Bytes=1218)

2 1 INDEX (SKIP SCAN) OF 'INDX_T' (NON-UNIQUE) (Cost=14 Card=14)

Statistics

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

0 recursive calls

0 db block gets

24 consistent gets

0 physical reads

0 redo size

1224 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10 rows processed

    OK!由于只查询了10条数据,即使没有使用前导列,Oracle正确地选择了索引跳跃扫描。我们再来看看如果不使用索引跳跃扫描,该语句的成本:

SQL> select/**//*+ NO_INDEX(T INDX_T)*/ * from t where object_name= 'DEPT';

已选择10行。

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=14 Bytes=1218)

1 0 TABLE ACCESS (FULL) OF 'T' (Cost=38 Card=14 Bytes=1218)

Statistics

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

0 recursive calls

0 db block gets

375 consistent gets

17 physical reads

0 redo size

1224 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10 rows processed

    正如我们所料,不使用索引所导致的逻辑读(375)确实比使用索引的逻辑读多(24),达到10倍以上。

继续我们的测试,现在我们来看看Oracle不选择使用索引的情况:


    测试4)

SQL> select * from t where object_name LIKE 'DE%';

已选择180行。

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=38 Card=37 Bytes=3219)

1 0 TABLE ACCESS (FULL) OF 'T' (Cost=38 Card=37 Bytes=3219)

Statistics

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

0 recursive calls

0 db block gets

386 consistent gets

16 physical reads

0 redo size

12614 bytes sent via SQL*Net to client

624 bytes received via SQL*Net from client

13 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

180 rows processed

    这次只选择了180条数据,跟表T中总的数据量29489条相比,显然只是很小的一部分,但是Oracle还是选择了全表扫描,有386个逻辑读。这种情况下,如果我们强制使用索引,情况会怎样呢?

SQL> select/**//*+ INDEX(T INDX_T)*/ * from t where object_name LIKE 'DE%';

已选择180行。

Execution Plan

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

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=182 Card=37 Bytes=3219)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=182 Card=37 Bytes=3219)

2 1 INDEX (FULL SCAN) OF 'INDX_T' (NON-UNIQUE) (Cost=144 Card=37)

Statistics

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

0 recursive calls

0 db block gets

335 consistent gets

0 physical reads

0 redo size

4479 bytes sent via SQL*Net to client

624 bytes received via SQL*Net from client

13 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

180 rows processed

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

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

本文转载自:http://www.cnblogs.com/rootq/archive/2008/10/19/1314669.html

共有 人打赏支持
Zero零_度
粉丝 69
博文 1253
码字总数 256597
作品 0
程序员
私信 提问
Oracle中的索引详解

一、 ROWID的概念 存储了row在数据文件中的具体位置:64位 编码的数据,A-Z, a-z, 0-9, +, 和 /, row在数据块中的存储方式 SELECT ROWID, last_name FROM hr.employees WHERE department_i...

crazyinsomnia
2010/02/24
85.9K
3
【转】Oracle执行计划详解

简介: 本文全面详细介绍oracle执行计划的相关的概念,访问数据的存取方法,表之间的连接等内容。 并有总结和概述,便于理解与记忆! +++ 目录 --- 一.相关的概念 Rowid的概念 Recursive Sq...

王小明123
2014/04/30
0
0
Oracle Hints详解

在向大家详细介绍Oracle Hints之前,首先让大家了解下Oracle Hints是什么,然后全面介绍Oracle Hints,希望对大家有用。基于代价的优化器是很聪明的,在绝大多数情况下它会选择正确的优化器,...

Zero零_度
2015/05/07
0
0
Oracle性能优化,组合索引查询

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

郑加威
2017/03/01
0
0
Oracle语句优化30个规则详解

选用适合的Oracle优化器 Oracle的优化器共有3种: a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性) 设置缺省的优化器,可以通过对init.ora文件中OPTIMIZERMODE参数的各种声明,如...

风一样
2011/08/07
0
0

没有更多内容

加载失败,请刷新页面

加载更多

新项目技术栈落地(二)——SpringMVC+Spring和SpringBoot的选择

使用SpringBoot进行项目开发已经是大势所趋,但在这里还是要说明为什么选择SpringBoot,选择SpringBoot带来的好处和SpringBoot注意的一些问题。 首先SpringBoot并不是一门新技术而是spring开...

Skqing
25分钟前
1
0
如何使用apache的ab压力测试小工具传参数

前言: windows下安装的phpstudy软件里集成的apache带了ab工具,所以可以不用单独下载。其他的操作系统下的安装或部署这里就不介绍了! 一、 使用windows的cmd进入apache的根目录,输入ab查看...

小谜弟
26分钟前
1
0
angular6.1.0 运行时报错ERROR in node_modules/rxjs/internal/types.d.ts(81,44): error TS1005: ';' expected.

angular6.1.0 运行时报错ERROR in node_modules/rxjs/internal/types.d.ts(81,44): error TS1005: ';' expected. node_modules/rxjs/internal/types.d.ts(81,74): error TS1005: ';' expect......

Jack088
29分钟前
1
0
阿里面试题剖析,如何保证消息不被重复消费?

面试题 如何保证消息不被重复消费?或者说,如何保证消息消费的幂等性? 面试官心理分析 其实这是很常见的一个问题,这俩问题基本可以连起来问。既然是消费消息,那肯定要考虑会不会重复消费...

李红欧巴
29分钟前
1
0
基于 DataLakeAnalytics 的数据湖实践

随着软硬件各方面条件的成熟,数据湖(Data Lake)已经越来越受到各大企业的青睐, 与传统的数仓实践不一样的是,数据湖不需要专门的“入仓”的过程,数据在哪里,我们就从哪里读取数据进行分析...

zhaowei121
30分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部