ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 主键列直方图有用吗?

主键列直方图有用吗?

原创 Linux操作系统 作者:wei-xh 时间:2011-07-18 10:09:23 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2341151