主鍵列直方圖有用嗎?

wei-xh發表於2011-07-18

create table wxh_tbd as select * from dba_objects;
update wxh_tbd set object_id=10000000 where object_id=100;
create index t_1 on wxh_tbd(object_id);
----------------分析表
begin
  dbms_stats.gather_table_stats(ownname          => 'APOLLO',
                                tabname          => 'wxh_tbd',
                                no_invalidate    => FALSE,
                                estimate_percent => 100,
                                force            => true,
                                method_opt       => 'for all columns size 1',
                                cascade          => true);
end;
/

begin
  dbms_stats.gather_table_stats(ownname          => 'APOLLO',
                                tabname          => 'wxh_tbd',
                                no_invalidate    => FALSE,
                                estimate_percent => 100,
                                force            => true,
                                method_opt       => 'for  columns size 1,object_id size auto',
                                cascade          => true);
end;
/


select * from wxh_tbd where object_id>500000;

-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         | 18850 |  1730K|   205   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| WXH_TBD | 18850 |  1730K|   205   (1)| 00:00:01 |
-----------------------------------------------------------------------------

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |    75 |  7050 |     7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| WXH_TBD |    75 |  7050 |     7   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_1     |    75 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22034023/viewspace-702438/,如需轉載,請註明出處,否則將追究法律責任。

相關文章