Oracle中组合索引的使用详解

最新推荐文章于 2020-09-09 10:19:19 发布
最新推荐文章于 2020-09-09 10:19:19 发布
阅读量3k 收藏
点赞数
分类专栏： Oracle 文章标签： Oracle

在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';     -- 转载修改，觉得原文作者使用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优化器作出更好的选择。

白开水Jerry
关注
专栏目录
联合索引，组合索引，详细应用实例
weixin_37668112的博客
11-11 1526
概念：联合索引也称组合索引，这里详细介绍分析以及应用实例
Oracle 索引命中详解
Hz_enginer的博客
06-21 3476
整理一下Oracle数据查询索引命中情况，仅供参考，有问题欢迎指出修正： 创建测试表： CREATE TABLE TEST_HZ ( A VARCHAR(20), B VARCHAR(20), C VARCHAR(20) )； 插入数据： 本人比较懒，然后就直接建序列插数据 create sequence seq_test_hz_a start with 1 inc...
索引覆盖与回表
AF_0228的博客
09-09 5657
懂的越多，不懂的越多 上回书说到：什么情况下，索引下推没办法提高sql查询效率？ 表 info 主键 id 名称 name 值 value 别名 realname 对于info表，我们现在有（name,value）联合索引
MySQL理解索引、添加索引的原则
weixin_30808693的博客
10-13 615
索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关的行，还需要考虑每次读入数据页的IO开销。而如果采取索引，则可以根据索引指向的页以及记录在页中的位置，迅速地读取目标页进而获取目标记录。 大多数情况下都（默认）采用B树来构建索引。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引。B树是平衡多叉树，每个节点存放多少个值...
复合索引（组合索引）
weixin_34233421的博客
07-22 177
用户可以在多个列上建立索引，这种索引叫做复合索引(组合索引)。   1.何时是用复合索引 在where条件中字段用索引，如果用多字段就用复合索引。比如在查询地址的时候，经常要求输入省，市信息，如果同时在省，市上建立索引，将会提高查询速度。   2.对于复合索引，在查询使用时，最好将条件顺序按找索引的顺序，这样效率最高。如： IDX1:create index idx1 on table...
oracle索引介绍(图文详解)
12-16
在Oracle数据库中，索引是一种优化查询性能的关键工具。它们加快了数据检索速度，使得数据库管理系统（DBMS）能够更快地定位到所需的数据行。本文将深入探讨Oracle索引的类型、结构以及如何创建和使用。 首先，让...
oracle中distinct的用法详解
09-10
当你在查询中使用 `SELECT DISTINCT column_name FROM table`，Oracle将比较所有指定列的值，并返回唯一的非重复值。例如，如果你有一个名为 `table` 的表，其中包含 `id` 和 `name` 两列，使用 `SELECT DISTINCT ...
oracle 索引组织表的深入详解
12-15
Oracle 索引组织表（Index-Organized Table, IOT）是一种特殊的表类型，它将数据和索引紧密地结合在一起，以提供快速的数据访问。这种设计使得IOT特别适用于那些通过主键频繁进行查询的场景，因为它利用了主键的排序...
ORACLE中的的HINT详解
09-09
在Oracle数据库中，HINT是一种强大的工具，允许数据库管理员和开发人员对SQL查询的执行计划进行微调，以提高性能或满足特定的需求。本文将深入探讨Oracle HINT的使用和功能。 1. **HINT的基本概念** HINT是Oracle...
组合索引
11-02 4402
组合索引（concatenated index）：由多个列构成的索引， 创建组合索引create index idx_detp on detp（col1，col2,col3，....），则我们称idx_emp索引为组合索引。 在组合索引中有一个重要的概念：引导列（leading column），在上面的例子中，col1列为引导列。 当我们进行查询时where 限制条件必须有引导列. 使
Oracle全局分区索引详解与优化策略
在Oracle 9i之前，只有当WHERE子句中明确指定了索引第一列的值时，才能使用组合索引。然而，从9i开始，引入了跳跃式索引扫描，使得即使在WHERE子句中未指定第一列的值，系统也可能利用索引进行查询优化。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值