ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Index rebuild --case 1

Index rebuild --case 1

原创 Linux操作系统 作者:yezhibin 时间:2009-09-17 15:21:00 0 删除 编辑
实验条件:

1、非ASSM表空间
2、索引创建,CF值接近Blocks数量
3、索引列不超过表列10%
4、测试查询不同数量的行,索引碎片造成的影响情况

实验:

SQL> create tablespace perfstat datafile
            '/home/simskf/simskfdbs/oradata/simskf/perfstat.dbf' size 600m
            extent management local uniform. size 1M
            segment space management manual;

SQL>create table t1 (id number, pad char(50), name1 char(50),  name2 char(50),
          name3 char(50), name4 char(50), name5 char(50), name6 char(50), name7 char(50), name8 char(50), name9 char(50));

SQL>insert into t1 select rownum,  rpad('1234567890' ,50), 'test1', 'test2', 'test3', 'test4', 'test5', 'test6', 'test7', 'test8', 'test9'  from dual connect by level<=100000;

SQL>commit;

SQL>create index t1_idx on t1 (id, pad) pctfree 0;

SQL>begin
              dbms_stats.gather_table_stats(
                user,
                'T1',
                cascade=>true,
                estimate_percent=>null,
                method_opt=>'for all columns size 1'
           );
        end;
       /

1rows          0.001%  select * from t1 where id =200; 
10 rows       0.01%       select * from t1 where id between 10 and 19;
100 rows     0.1%      select * from t1 where id between 500 and 599;
1000rows     1%     select * from t1 where id between 3000 and 3999;
5000 rows    5%     select * from t1 where id between 5000 and 9999;
10000 rows  10%   select * from t1 where id between 10000 and 19999
100000 rows 100% select /*+ index(t1_idx) */ * from t1 where id between 1 and 100000;
100000 rows(FTS) 100% select /*+ index_ffs(t1_idx) */ * from t1 where id between 1 and 100000;

analyze index t1_idx validate structure;
PCTFREE=0
SQL> select HEIGHT,BR_BLKS,LF_BLKS ,PCT_USED from index_stats;

    HEIGHT    BR_BLKS    LF_BLKS   PCT_USED
---------- ---------- ---------- ----------
         3          3        839        100
1rows     Elapsed: 00:00:00.01
10rows   Elapsed: 00:00:00.23
100rows   Elapsed: 00:00:02.70

 
        

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/354732/viewspace-614916/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-12-18

  • 博文量
    159
  • 访问量
    508450