create table test_his as select * from dba_objects;
--假设数据是09年全年的业务数据
update test_his set created=to_date('2009-12-31','yyyy-mm-dd');
commit;
update test_his set created=CREATED-trunc(dbms_random.value(0,365));
commit;
--创建索引
create index i_test_his_d on test_his (created);
--更新统计信息，不含柱状图
exec dbms_stats.gather_table_stats(user,'test_his',method_opt => 'for all indexed columns size 1');
SQL> select object_id from test_his
where created >=to_date('2009-12-31','yyyy-mm-dd')
and created <=to_date('2009-12-31','yyyy-mm-dd');
已选择47行。
执行计划
----------------------------------------------------------
Plan hash value: 129012486
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 46 | 598 | 43 (0)
| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_HIS | 46 | 598 | 43 (0)
| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_TEST_HIS_D | 46 | | 1 (0)
| 00:00:01 |
SQL> select object_id from test_his
where created >=to_date('2010-01-01','yyyy-mm-dd')
and created <=to_date('2010-01-01','yyyy-mm-dd');
未选定行
执行计划
----------------------------------------------------------
Plan hash value: 129012486
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 46 | 598 | 43 (0)
| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_HIS | 46 | 598 | 43 (0)
| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_TEST_HIS_D | 46 | | 1 (0)
| 00:00:01 |
SQL> select object_id from test_his
where created >=to_date('2009-01-01','yyyy-mm-dd')
and created <=to_date('2009-07-01','yyyy-mm-dd');
已选择8297行。
执行计划
----------------------------------------------------------
Plan hash value: 2828532586
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8397 | 106K| 50 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_HIS | 8397 | 106K| 50 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CREATED"<=TO_DATE('2009-07-01 00:00:00', 'yyyy-mm-dd
hh24:mi:ss') AND "CREATED">=TO_DATE('2009-01-01 00:00:00', 'yyyy-m
m-dd
hh24:mi:ss'))
--为了程序方便，程序员将期初数据统一以日期为0001-01-01来表示，假设其中有一条是期初数据
update test_his set created=to_date('0001-01-01','yyyy-mm-dd')
where created=to_date('2009-01-01','yyyy-mm-dd') and rownum<2;
commit;
--更新统计信息，不含柱状图
exec dbms_stats.gather_table_stats(user,'test_his',method_opt => 'for all indexed columns size 1');
--查一天的数据
SQL> select object_id from test_his
where created >=to_date('2009-01-01','yyyy-mm-dd')
and created <=to_date('2009-01-01','yyyy-mm-dd');
已选择38行。
执行计划
----------------------------------------------------------
Plan hash value: 129012486
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 46 | 598 | 43 (0)
| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_HIS | 46 | 598 | 43 (0)
| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_TEST_HIS_D | 46 | | 1 (0)
| 00:00:01 |
--9天的数据实际行数评估已经和实际差很多
SQL> select object_id from test_his
where created >=to_date('2009-01-01','yyyy-mm-dd')
and created <=to_date('2009-01-09','yyyy-mm-dd');
已选择396行。
执行计划
----------------------------------------------------------
Plan hash value: 2828532586
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 54 | 702 | 50 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_HIS | 54 | 702 | 50 (0)| 00:00:01 |
------------------------------------------------------------------------------
--半年的数据评估与9天的一样，与实际情况差距很大
SQL> select object_id from test_his
where created >=to_date('2009-01-01','yyyy-mm-dd')
and created <=to_date('2009-07-01','yyyy-mm-dd');
已选择8296行。
执行计划
----------------------------------------------------------
Plan hash value: 2828532586
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 54 | 702 | 50 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_HIS | 54 | 702 | 50 (0)| 00:00:01 |
------------------------------------------------------------------------------
--更新统计信息，含柱状图
exec dbms_stats.gather_table_stats(user,'test_his',method_opt => 'for all indexed columns size 255');
--能够正确的评估出期间范围的行数
SQL> select object_id from test_his
where created >=to_date('2009-01-01','yyyy-mm-dd')
and created <=to_date('2009-07-01','yyyy-mm-dd');
已选择8296行。
执行计划
----------------------------------------------------------
Plan hash value: 2828532586
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8199 | 104K| 50 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_HIS | 8199 | 104K| 50 (0)| 00:00:01 |
------------------------------------------------------------------------------
SQL> select object_id from test_his
where created >=to_date('2009-01-01','yyyy-mm-dd')
and created <=to_date('2009-01-01','yyyy-mm-dd')
已选择38行。
执行计划
----------------------------------------------------------
Plan hash value: 129012486
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 47 | 611 | 44 (0)
| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_HIS | 47 | 611 | 44 (0)
| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_TEST_HIS_D | 47 | | 1 (0)
| 00:00:01 |
可以这种情况下增加柱状图信息问题能够得到解决
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/27378/viewspace-619404/ ，如需转载，请注明出处，否则将追究法律责任。
