Oracle中组合索引怎么用

这篇文章给大家分享的是有关Oracle中组合索引怎么用的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。

专注于为中小企业提供成都网站建设、成都网站设计服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业花溪免费做网站提供优质的服务。我们立足成都,凝聚了一批互联网行业人才,有力地推动了上千企业的稳健成长,帮助中小企业通过网站建设实现规模扩充和转变。

Oracle中组合索引的使用

关键词:组合索引 前导列  基于规则的优化器(RBO)  基于成本的优化器(CBO)  提示(hint)

在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)。

[@more@]

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

我们创建测试表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优化器作出更好的选择。

感谢各位的阅读!关于“Oracle中组合索引怎么用”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,让大家可以学到更多知识,如果觉得文章不错,可以把它分享出去让更多的人看到吧!


标题名称:Oracle中组合索引怎么用
本文URL:http://scyanting.com/article/iieeoh.html