重复测试。
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
)
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
;
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一样的情况下，要注意索引的命名问题。