索引键压缩

索引占用空间对比：

create table t1

as

select * from dba_objects;

 

create index uncompressed_idx

on t1( owner,object_type,object_name );

 

查询索引的高度：

Select index_name,blevel,num_rows from user_indexes where table_name = ‘T1’;

INDEX_NAME                         BLEVEL   NUM_ROWS

------------------------------ ---------- ----------

UNCOMPRESSED_IDX                        2      50223

Blevel为2，意味着索引的高度为3。

analyze table t1 compute statistics

for table

for all indexes

for all indexed columns;

 

analyze index uncompressed_idx validate structure;

 

create table index_stats_copy as select * from index_stats;

 

select height,btree_space,opt_cmpr_count,opt_cmpr_pctsave from index_stats_copy;

 

    HEIGHT BTREE_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

---------- ----------- -------------- ----------------

         3     2808096              2               28

 

从查询结果可以得知，Oracle认为如果设置压缩值为2将最优，可以节约28%的空间。

 

create table t2

as

select * from t1;

 

按照Oracle的建议把索引的压缩值设为2

create index compressed_idx

on t2( owner,object_type,object_name )

COMPRESS 2;

 

analyze table t2 compute statistics

for table

for all indexes

for all indexed columns;

 

analyze index compressed_idx validate structure;

insert into index_stats_copy select * from index_stats;

 

variable x refcursor

declare

    l_stmt long;

begin

    for x in ( select '''' || column_name || '''' quoted,

                      column_name

                 from user_tab_columns

                where table_name = 'INDEX_STATS_COPY'

                  and column_name not in

                      ('NAME','PARTITION_NAME') )

    loop

        l_stmt := l_stmt || ' select ' || x.quoted || ' name,

                    max(decode(name,''UNCOMPRESSED_IDX'',' ||

                    x.column_name || ',null)) uncompressed,

                    max(decode(name,''UNCOMPRESSED_IDX'',

                        to_number(null),' || x.column_name ||

                        ')) compressed

                    from index_stats_copy union all';

    end loop;

    l_stmt :=

    'select name, uncompressed, compressed,

            uncompressed-compressed diff,

            decode(uncompressed,0,

               to_number(null),

               round(compressed/uncompressed*100,2)) pct

       from ( ' ||

         substr( l_stmt, 1,

                 length(l_stmt)-length(' union all') ) ||

            ') order by name';

    open :x for l_stmt;

end;

/

 

Print x

 

结果如下：

NAME                           UNCOMPRESSED COMPRESSED       DIFF        PCT

------------------------------ ------------ ---------- ---------- ----------

BLKS_GETS_PER_ACCESS             4.00137766 3.00137766          1      75.01

BLOCKS                                  384        256        128      66.67

BR_BLKS                                   3          1          2      33.33

BR_BLK_LEN                             8032       8032          0        100

BR_ROWS                                 347        248         99      71.47

BR_ROWS_LEN                           11158       7989       3169       71.6

BTREE_SPACE                         2808096    1999036     809060      71.19

DEL_LF_ROWS                               0          0          0

DEL_LF_ROWS_LEN                           0          0          0

DISTINCT_KEYS                         50085      50085          0        100

HEIGHT                                    3          2          1      66.67

LF_BLKS                                 348        249         99      71.55

LF_BLK_LEN                             8000       7996          4      99.95

LF_ROWS                               50223      50223          0        100

LF_ROWS_LEN                         2486622    1768525     718097      71.12

MOST_REPEATED_KEY                        10         10          0        100

OPT_CMPR_COUNT                            2          2          0        100

OPT_CMPR_PCTSAVE                         28          0         28          0

PCT_USED                                 89         90         -1     101.12

PRE_ROWS                                  0        458       -458

PRE_ROWS_LEN                              0       9406      -9406

ROWS_PER_KEY                     1.00275532 1.00275532          0        100

USED_SPACE                          2497780    1785920     711860       71.5

 

从结果来看，页块压缩到(LF_BLKS) 71.55%,分支块（BR_BLKS）压缩到33.33%。

 

可以看出采用了压缩设置2后的索引的空间减少了28.5%（压缩到71.5%）。Oracle的预测是准确的。

 

查询性能对比：

 

先使用runstats对比工具：

exec runstats_pkg.rs_start

begin

    for x in ( select * from t1 )

    loop

        for y in ( select *

                     from t1

                    where owner = x.owner

                      and object_name = x.object_name

                      and object_type = x.object_type )

        loop

            null;

        end loop;

    end loop;

end;

/

 

exec runstats_pkg.rs_middle

 

begin

for x in ( select * from t2 )

    loop

        for y in ( select *

                     from t2

                    where owner = x.owner

                      and object_name = x.object_name

                      and object_type = x.object_type )

        loop

            null;

        end loop;

    end loop;

end;

/

 

exec runstats_pkg.rs_stop(100)

/

 

测试结果如下：

Run1 ran in 713 hsecs

Run2 ran in 711 hsecs

run 1 ran in 100.28% of the time

       

Name                                  Run1        Run2        Diff

STAT...no work - consistent re      52,128      52,026        -102

STAT...redo size                     2,752       2,892         140

STAT...consistent gets - exami     100,453      50,232     -50,221

STAT...session logical reads       202,831     152,513     -50,318

STAT...consistent gets             202,814     152,491     -50,323

STAT...consistent gets from ca     202,814     152,491     -50,323

LATCH.simulator lru latch           56,592       6,170     -50,422

LATCH.simulator hash latch          56,592       6,170     -50,422

LATCH.cache buffers chains         305,306     254,851     -50,455

       

Run1 latches total versus runs -- difference and pct

        Run1        Run2        Diff       Pct

     620,428     469,027    -151,401    132.28%

 

从中可以看出运行时间上，两者差不多；但不压缩使用的闩还要多一些。

 

再使用tkprof工具，对比一下性能指标：

先运行：

alter session set sql_trace=true;

 

begin

    for x in ( select * from t1 )

    loop

        for y in ( select *

                     from t1

                    where owner = x.owner

                      and object_name = x.object_name

                      and object_type = x.object_type )

        loop

            null;

        end loop;

    end loop;

    for x in ( select * from t2 )

    loop

        for y in ( select *

                     from t2

                    where owner = x.owner

                      and object_name = x.object_name

                      and object_type = x.object_type )

        loop

            null;

        end loop;

    end loop;

end;

/

 

tkprof报告的部分结果：

SELECT *

FROM

 T1 WHERE OWNER = :B3 AND OBJECT_NAME = :B2 AND OBJECT_TYPE = :B1

 

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute  50223      1.34       1.13          0          0          0           0

Fetch    50223      1.71       1.85          0     201611          0       50807

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total   100447      3.06       2.99          0     201611          0       50807

 

SELECT *

FROM

 T2 WHERE OWNER = :B3 AND OBJECT_NAME = :B2 AND OBJECT_TYPE = :B1

 

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute  50223      1.20       1.14          0          2          0           0

Fetch    50223      1.93       1.87          0     151286          0       50807

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total   100447      3.14       3.01          0     151288          0       50807

 

从报告结果可以看出，压缩索引的CPU消耗时间略长，但query次数要少。query次数少的原因是索引高度不同。

 

测试结论，当表是只读的或很少进行insert和update操作时，使用压缩索引可以节约空间，效率也不差，应该是一个不错的选择。

 

参考文献：

《Oracle9i&10g编程艺术》

《Oracle高效设计》

 

附：

Runstats工具的脚本：

create or replace view stats

as select 'STAT...' || a.name name, b.value

      from v$statname a, v$mystat b

     where a.statistic# = b.statistic#

    union all

    select 'LATCH.' || name,  gets

      from v$latch;

 

create global temporary table run_stats

( runid varchar2(15),

  name varchar2(80),

  value int )

on commit preserve rows;

 

create or replace package runstats_pkg

as

    procedure rs_start;

    procedure rs_middle;

    procedure rs_stop( p_difference_threshold in number default 0 );

end;

/

 

create or replace package body runstats_pkg

as

 

g_start number;

g_run1     number;

g_run2     number;

 

procedure rs_start

is

begin

    delete from run_stats;

 

    insert into run_stats

    select 'before', stats.* from stats;

 

    g_start := dbms_utility.get_time;

end;

 

procedure rs_middle

is

begin

    g_run1 := (dbms_utility.get_time-g_start);

 

    insert into run_stats

    select 'after 1', stats.* from stats;

    g_start := dbms_utility.get_time;

 

end;

 

procedure rs_stop(p_difference_threshold in number default 0)

is

begin

    g_run2 := (dbms_utility.get_time-g_start);

 

    dbms_output.put_line

        ( 'Run1 ran in ' || g_run1 || ' hsecs' );

    dbms_output.put_line

        ( 'Run2 ran in ' || g_run2 || ' hsecs' );

    dbms_output.put_line

    ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||

      '% of the time' );

        dbms_output.put_line( chr(9) );

 

    insert into run_stats

    select 'after 2', stats.* from stats;

 

    dbms_output.put_line

    ( rpad( 'Name', 30 ) || lpad( 'Run1', 10 ) ||

      lpad( 'Run2', 10 ) || lpad( 'Diff', 10 ) );

 

    for x in

    ( select rpad( a.name, 30 ) ||

             to_char( b.value-a.value, '9,999,999' ) ||

             to_char( c.value-b.value, '9,999,999' ) ||

             to_char( ( (c.value-b.value)-(b.value-a.value)), '9,999,999' ) data

        from run_stats a, run_stats b, run_stats c

       where a.name = b.name

         and b.name = c.name

         and a.runid = 'before'

         and b.runid = 'after 1'

         and c.runid = 'after 2'

         and (c.value-a.value) > 0

         and abs( (c.value-b.value) - (b.value-a.value) )

               > p_difference_threshold

       order by abs( (c.value-b.value)-(b.value-a.value))

    ) loop

        dbms_output.put_line( x.data );

    end loop;

 

        dbms_output.put_line( chr(9) );

    dbms_output.put_line

    ( 'Run1 latches total versus runs -- difference and pct' );

    dbms_output.put_line

    ( lpad( 'Run1', 10 ) || lpad( 'Run2', 10 ) ||

      lpad( 'Diff', 10 ) || lpad( 'Pct', 8 ) );

 

    for x in

    ( select to_char( run1, '9,999,999' ) ||

             to_char( run2, '9,999,999' ) ||

             to_char( diff, '9,999,999' ) ||

             to_char( round( run1/run2*100,2 ), '999.99' ) || '%' data

        from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2,

                      sum( (c.value-b.value)-(b.value-a.value)) diff

                 from run_stats a, run_stats b, run_stats c

                where a.name = b.name

                  and b.name = c.name

                  and a.runid = 'before'

                  and b.runid = 'after 1'

                  and c.runid = 'after 2'

                  and a.name like 'LATCH%'

                )

    ) loop

        dbms_output.put_line( x.data );

    end loop;

end;

 

end;

/