oracle联合索引在CBO下的分析

SQL> set autot on
SQL> desc yibin_test;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                               NOT NULL VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(18)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
对OBJECT_NAME  ,OBJECT_ID 建立主键联合索引,object_name在前

SQL> select index_name,table_name,column_name from user_ind_columns;

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
-------------------------------------------------------------------------------

IDX_TEST                       YIBIN_TEST
OBJECT_ID

IDX_TEST                       YIBIN_TEST
OBJECT_NAME

并且分析索引

SQL> analyze table object_test compute statistics;

Table analyzed

看下面 两个字段都列上时,不管后面字段摆放顺序 都会用上 INDEX (UNIQUE SCAN) 索引,

后面两个字段对调位置

SQL> select * from yibin_test where  object_id=97 and object_name='access$' ;

未选定行

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=76)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'YIBIN_TEST' (Cost=2 Card
          =1 Bytes=76)

   2    1     INDEX (UNIQUE SCAN) OF 'IDX_TEST' (UNIQUE) (Cost=1 Card=
          1)

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          1  physical reads
          0  redo size
        942  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

没调换位置

SQL> select * from yibin_test where  object_name='access$' and object_id=97
  2  ;

未选定行

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=76)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'YIBIN_TEST' (Cost=2 Card
          =1 Bytes=76)

   2    1     INDEX (UNIQUE SCAN) OF 'IDX_TEST' (UNIQUE) (Cost=1 Card=
          1)

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        942  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL>

单独把object_name列出来 会用上索引范围扫描

SQL> select * from yibin_test where  object_name='access$' ;

未选定行

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=1 Bytes=76)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'YIBIN_TEST' (Cost=3 Card
          =1 Bytes=76)

   2    1     INDEX (RANGE SCAN) OF 'IDX_TEST' (UNIQUE) (Cost=2 Card=1
          )

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        942  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

而只用后面这个时，不会用上索引，走全表扫描

SQL> select * from yibin_test where  object_id=97 ;

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------

SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED    LAST_DDL_T TIMESTAMP           STATUS  T G S
---------- ---------- ------------------- ------- - - -
SYS
ACCESS$
                                       97             97 TABLE
26-10月-08 26-10月-08 2008-10-26:01:03:06 VALID   N N N

 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=10 Card=1 Bytes=76)
   1    0   TABLE ACCESS (FULL) OF 'YIBIN_TEST' (Cost=10 Card=1 Bytes=
          76)

 

 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         83  consistent gets
          0  physical reads
          0  redo size
       1167  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)
          1  rows processed

SQL>

 

总结:根据上面的分析，得出以下结论

CBO下联合索引:

要是where 后面联合索引字段都列上，不管先后顺序都会用上索引

要是where后面只列单独一个字段，那只有按照建立索引字段顺序排在最前的会用上索引，后面的不会用上

所以要根据应用来结合，建立合适的索引

经过测试 发现在rbo下也是和上面规律一样

如果大家对这个结果有问题，请提出来

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7199859/viewspace-533331/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7199859/viewspace-533331/