数据仓库中有个表A这个表A 中共有记录数 1182481条
我执行一个查询 ,这个查询结果有 10965 条,
SELECT COUNT(*) FROM DM_BASE_ESSENTIAL_MED_OP
WHERE DIM_DATE>=DATE'2012-2-1'
AND DIM_DATE
我DIM_DATE日期列上也已经建立了索引,按道理查询结果只占总数的1%,应该走索引啊,
可是它偏偏走了全表扫描,这个是它的执行计划
explain plan for
SELECT * FROM A t
WHERE DIM_DATE>=DATE'2012-2-1' AND DIM_DATE
select * from table(dbms_xplan.display())
----执行计划如下
Plan hash value: 2285450492
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7883 | 585K| 3515 (2)| 00:00:43 |
|* 1 | TABLE ACCESS FULL| DM_BASE_ESSENTIAL_MED_OP | 7883 | 585K| 3515 (2)| 00:00:43 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DIM_DATE"
"DIM_DATE">=TO_DATE('2012-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
当强制走索引的时候,执行计划是这样
explain plan for
SELECT /*+index(t IDX_MED_DATE)*/* FROM DM_BASE_ESSENTIAL_MED_OP t
WHERE DIM_DATE>=DATE'2012-2-1' AND DIM_DATE
select * from table(dbms_xplan.display())
----执行计划如下
Plan hash value: 24065298
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7883 | 585K| 5079 (1)| 00:01:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| DM_BASE_ESSENTIAL_MED_OP | 7883 | 585K| 5079 (1)| 00:01:01 |
|* 2 | INDEX RANGE SCAN | IDX_MED_DATE | 7883 | | 23 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DIM_DATE">=TO_DATE('2012-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
"DIM_DATE"
网上查询了下,听说可能是聚簇因子太高了
SELECT CLUSTERING_FACTOR,NUM_ROWS FROM USER_IND_STATISTICS
WHERE TABLE_NAME='A'
CLUSTERING_FACTOR NUM_ROWS
757289 1182481
这种情况改怎么解决啊,是不是聚簇因子太高了的原因????