注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle、MySQL资料及经验

.

 
 
 

日志

 
 

查哪些索引的聚合因子特别大  

2013-12-22 22:22:35|  分类: 优化 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

当前用户下,哪些索引的聚合因子特别大。
select a.table_name,
       a.index_name,
       a.blevel,
       a.leaf_blocks,
       b.num_rows,
       b.blocks,
       a.clustering_factor,
       trunc(a.clustering_factor / b.num_rows,2) cluster_rate
  from user_indexes a, user_tables b
 where a.table_name = b.table_name
     and a.clustering_factor is not null
     and a.clustering_factor / b.num_rows>0.7   --clustering_factor/num_rows
 order by cluster_rate desc;

 

--例子:


--colocated表根据x列有一定的物理顺序
 
drop table colocated purge;
create table colocated ( x int, y varchar2(80) );
begin
    for i in 1 .. 100000
    loop
        insert into colocated(x,y)
        values (i, rpad(dbms_random.random,75,'*') );
    end loop;
end;
/

alter table colocated
add constraint colocated_pk
primary key(x);
begin
dbms_stats.gather_table_stats( user, 'COLOCATED', cascade=>true );
end;
/

--disorganized 表数据根据x列完全无序
drop table disorganized purge;
create table disorganized
as
select x,y
  from colocated
 order by y;
alter table disorganized
add constraint disorganized_pk
primary key (x);
begin
dbms_stats.gather_table_stats( user, 'DISORGANIZED', cascade=>true );
end;
/

set autotrace off
alter session set statistics_level=all;
set linesize 1000


---两者性能差异显著
select /*+ index( colocated colocated_pk ) */ * from colocated where x between 20000 and 40000;
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));

------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |      1 |        |  20001 |00:00:00.05 |    2900 |
|   1 |  TABLE ACCESS BY INDEX ROWID| COLOCATED    |      1 |  20002 |  20001 |00:00:00.05 |    2900 |
|*  2 |   INDEX RANGE SCAN          | COLOCATED_PK |      1 |  20002 |  20001 |00:00:00.03 |    1375 |
------------------------------------------------------------------------------------------------------

 

 

select /*+ index( disorganized disorganized_pk ) */* from disorganized  where x between 20000 and 40000;
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));

---------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |      1 |        |  20001 |00:00:00.09 |   21360 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DISORGANIZED    |      1 |  20002 |  20001 |00:00:00.09 |   21360 |
|*  2 |   INDEX RANGE SCAN          | DISORGANIZED_PK |      1 |  20002 |  20001 |00:00:00.03 |    1375 |
---------------------------------------------------------------------------------------------------------


---看聚合因子,就明白真正的原因了。

select a.index_name,
       b.num_rows,
       b.blocks,
       a.clustering_factor
  from user_indexes a, user_tables b
where index_name in ('COLOCATED_PK', 'DISORGANIZED_PK' )
  and a.table_name = b.table_name;

INDEX_NAME                       NUM_ROWS     BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ---------- -----------------
COLOCATED_PK                       100000       1252              1190
DISORGANIZED_PK                    100000       1219             99899

  评论这张
 
阅读(344)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018