Index rebuild --case 1

實驗條件：

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/，如需轉載，請註明出處，否則將追究法律責任。