oracle联合Unique索引,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下也是和上面规律一样

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