主键列直方图有用吗？

最新推荐文章于 2025-07-14 09:17:22 发布

转载最新推荐文章于 2025-07-14 09:17:22 发布 · 62 阅读

本文介绍了通过创建表、更新表、创建索引以及使用不同的统计方法来优化Oracle数据库性能的过程，并对比了优化前后查询效率的变化。

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

转载于:http://blog.itpub.net/22034023/viewspace-702438/