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下也是和上面規律一樣

如果大家對這個結果有問題，請提出來

oracle聯合索引在CBO下的分析

相關文章