oracle联合索引在CBO下的分析

SQL> set autot on
SQL> desc test_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 test_TEST
OBJECT_ID
IDX_TEST test_TEST
OBJECT_NAME
并且分析索引
SQL> analyze table object_test compute statistics;
Table analyzed

看下面 两个字段都列上时,不管后面字段摆放顺序 都会用上 INDEX (UNIQUE SCAN) 索引,
后面两个字段对调位置
SQL> select * from test_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 'test_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 test_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 'test_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 test_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 'test_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 test_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 'test_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下也是和上面规律一样
如果大家对这个结果有问题，请提出来