http://jonathanlewis.wordpress.com/2012/02/06/index-naming/

重复测试。

1.测试环境:
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

2.建立测试表:

create table t1

as

with generator as (

    select    --+ materialize

        rownum id 

    from dual 

    connect by 

        level <= 10000

)

select

    trunc((rownum-1)/2)    product_id,

    mod(rownum-1,2)        flag1,

    mod(rownum-1,3)        flag2,

    rownum            n1,

    lpad(rownum,30)        v1

from

    generator    v1,

    generator    v2

where

    rownum <= 1000000

;

alter table t1 add constraint t1_pk primary key (product_id,flag1, flag2) 

using index (

create unique index t1_pk on t1(product_id, flag1, flag2)

);

create index t1_i1 on t1(product_id, flag2, flag1, n1,v1);

exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'t1',Estimate_Percent  => NULL);

3.开始测试:

SQL> set autotrace traceonly

SQL> select rowid from t1 where product_id = 2500 ;

Execution Plan

----------------------------------------------------------

Plan hash value: 3836157954

--------------------------------------------------------------------------

| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT |       |     2 |    34 |     3   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| T1_I1 |     2 |    34 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("PRODUCT_ID"=2500)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

        605  bytes sent via SQL*Net to client

        519  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          2  rows processed

--可以发现执行计划使用T1_I1.如果使用提示/*+ index(t1 t1_pk) */：

SQL> select /*+ index(t1 t1_pk) */ rowid from t1 where product_id = 2500 ;

Execution Plan

----------------------------------------------------------

Plan hash value: 1838269289

--------------------------------------------------------------------------

| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT |       |     2 |    34 |     3   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| T1_PK |     2 |    34 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("PRODUCT_ID"=2500)

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

        605  bytes sent via SQL*Net to client

        519  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          2  rows processed

--可以发现两者cost一样。

SQL> SELECT index_name, blevel, leaf_blocks, clustering_factor, distinct_keys  FROM dba_indexes

WHERE table_name = 'T1';

INDEX_NAME                         BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR DISTINCT_KEYS

------------------------------ ---------- ----------- ----------------- -------------

T1_I1                                   2        7996              7154       1000000

T1_PK                                   2        2807              7088       1000000

4.做10053跟踪:
SQL> alter session set events '10053 trace name context forever';
SQL> Select rowid from t1 where product_id = 2500 ;
--注意要进行1次硬分析，修改开头s＝》S。
SQL> alter session set events '10053 trace name context off';
Session altered.

***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T1[T1]
  Table: T1  Alias: T1
    Card: Original: 1000000.000000  Rounded: 2  Computed: 2.00  Non Adjusted: 2.00
  Access Path: TableScan
    Cost:  1961.88  Resp: 1961.88  Degree: 0
      Cost_io: 1954.00  Cost_cpu: 251345582
      Resp_io: 1954.00  Resp_cpu: 251345582
  Access Path: index (index (FFS))
    Index: T1_I1
    resc_io: 2167.00  resc_cpu: 226943034
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000
  Access Path: index (FFS)
    Cost:  2174.12  Resp: 2174.12  Degree: 1
      Cost_io: 2167.00  Cost_cpu: 226943034
      Resp_io: 2167.00  Resp_cpu: 226943034
  Access Path: index (index (FFS))
    Index: T1_PK
    resc_io: 762.00  resc_cpu: 189989882
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000
  Access Path: index (FFS)
    Cost:  767.96  Resp: 767.96  Degree: 1
      Cost_io: 762.00  Cost_cpu: 189989882
      Resp_io: 762.00  Resp_cpu: 189989882


  Access Path: index (IndexOnly)
    Index: T1_I1
    resc_io: 3.00  resc_cpu: 21764
    ix_sel: 0.000002  ix_sel_with_filters: 0.000002
    Cost: 3.00  Resp: 3.00  Degree: 1


  Access Path: index (IndexOnly)
    Index: T1_PK
    resc_io: 3.00  resc_cpu: 21764
    ix_sel: 0.000002  ix_sel_with_filters: 0.000002
    Cost: 3.00  Resp: 3.00  Degree: 1
  ****** trying bitmap/domain indexes ******
  ****** finished trying bitmap/domain indexes ******
  Best:: AccessPath: IndexRange
  Index: T1_I1
         Cost: 3.00  Degree: 1  Resp: 3.00  Card: 2.00  Bytes: 0

***************************************

--可以发现先分析T1_I1,再分析T1_PK索引，由于得到的cost一样，先选择T1_I1索引。

5.修改索引看看：

SQL> alter index "SCOTT"."T1_I1" rename to T1_XX;

SQL> set autotrace traceonly

SQL> sElect  rowid from t1 where product_id = 2500 ;

Execution Plan

----------------------------------------------------------

Plan hash value: 1838269289

--------------------------------------------------------------------------

| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT |       |     2 |    34 |     3   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| T1_PK |     2 |    34 |     3   (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - access("PRODUCT_ID"=2500)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          4  consistent gets

          0  physical reads

          0  redo size

        605  bytes sent via SQL*Net to client

        519  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          2  rows processed

--可以发现这次使用T1_PK索引。

总结：看来在cost一样的情况下，要注意索引的命名问题。