如下,在表tt_test上有索引单字段索引indx_tt_test_1,组合索引indx_tt_test_2和indx_tt_test_3。每个索引的值的分布情况都不一致,对于同一个查询语句(均使用单字段索引的字段),三个索引的访问效果都不一定一致的。这样是否可以理解为,组合索引是否不能替代单字段索引?
SQL> create table tt_test as select * from ta200251;
表已创建。
SQL> select count(1) from tt_test;
COUNT(1)
----------
265972
SQL> create index indx_tt_test_1 on tt_test (ta200251047);
索引已创建。
SQL> create index indx_tt_test_2 on tt_test (ta200251047,ta200251001);
索引已创建。
SQL> create index indx_tt_test_3 on tt_test (ta200251047,ta200251012);
索引已创建。
SQL> alter system flush shared_pool;
系统已更改。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> set autotrace on ;
SQL> select /*+ index(tt_test indx_tt_test_1) */ count(1)
from tt_test
where ta200251047='12020202';
COUNT(1)
----------
604
执行计划
----------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 7 |
| 1 | SORT AGGREGATE | | 1 | 6 | |
| 2 | INDEX RANGE SCAN| INDX_TT_TEST_1 | 1450 | 8700 | 7 |
--------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
统计信息
----------------------------------------------------------
28 recursive calls
0 db block gets
86 consistent gets
1 physical reads
0 redo size
209 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter system flush shared_pool;
系统已更改。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> select /*+ index(tt_test indx_tt_test_2) */ count(1)
from tt_test
where ta200251047='12020202';
COUNT(1)
----------
604
执行计划
----------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 10 |
| 1 | SORT AGGREGATE | | 1 | 6 | |
| 2 | INDEX RANGE SCAN| INDX_TT_TEST_2 | 1450 | 8700 | 10 |
--------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
统计信息
----------------------------------------------------------
638 recursive calls
0 db block gets
232 consistent gets
418 physical reads
0 redo size
224 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter system flush shared_pool;
系统已更改。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> select /*+ index(tt_test indx_tt_test_3) */ count(1)
from tt_test
where ta200251047='12020202';
COUNT(1)
----------
604
执行计划
----------------------------------------------------------
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 8 |
| 1 | SORT AGGREGATE | | 1 | 6 | |
| 2 | INDEX RANGE SCAN| INDX_TT_TEST_3 | 1450 | 8700 | 8 |
--------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
统计信息
----------------------------------------------------------
638 recursive calls
0 db block gets
231 consistent gets
414 physical reads
0 redo size
224 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT INDEX_NAME,
BLEVEL,LEAF_BLOCKS LB,
DISTINCT_KEYS DK,
CLUSTERING_FACTOR CF,
AVG_DATA_BLOCKS_PER_KEY ADBPK
FROM DBA_INDEXES
WHERE TABLE_NAME='TT_TEST';
INDEX_NAME BLEVEL LB DK CF ADBPK
------------------------------ ---------- ---------- ---------- ---------- -------------------------------------
INDX_TT_TEST_1 2 741 561 26113 46
INDX_TT_TEST_2 2 1337 31609 49214 1
INDX_TT_TEST_3 2 929 4399 68628 15