Index compression–quick tip

If you’re appropriately licensed and want to use advanced index compression, you can take advantage of the setting a tablespace to automatically add compression as a default at a nominated level in the database.  From the docs:

Here is an example of that in action.   We’ll set our tablespace default accordingly


SQL> create tablespace demo
  2  datafile 'C:\ORACLE\ORADATA\DB122\DEMO.DBF'
  3  size 100M
  4  default index compress advanced high;

Tablespace created.

Now we’ll create a table and an index on it in that tablespace


SQL> column index_name format a10
SQL> column tablespace_name format a15
SQL> create table t tablespace demo as
  2  select *
  3  from dba_objects;

Table created.

SQL> create index t_idx on t(owner) tablespace demo;

Index created.

SQL> select index_name,compression
  2  from user_indexes
  3  where table_name ='T';

INDEX_NAME COMPRESSION  
---------- ------------- 
T_IDX      ADVANCED HIGH   

1 row selected.

You can see that even though we did not specify any kind of compression for the index, it picked up the setting from the tablespace. Thus existing DDL scripts you have for indexes et al will not need to be changed.

Similarly, rebuilding the index does not lose the setting


SQL> alter index t_idx rebuild tablespace demo;

Index altered.

SQL> select index_name,compression
  2  from user_indexes
  3  where table_name ='T';

INDEX_NAME COMPRESSION   
---------- ------------- 
T_IDX      ADVANCED HIGH 

1 row selected.

You can see what the defaults are for a tablespace by querying DBA_TABLESPACES as per normal


SQL> select tablespace_name,compress_for,index_compress_for
  2  from user_tablespaces where tablespace_name = 'DEMO';

TABLESPACE_NAME COMPRESS_FOR                   INDEX_COMPRES
--------------- ------------------------------ -------------
DEMO                                           ADVANCED HIGH

Index compression–working out the compression number

Richard Foote did a series of informative posts on Index Compression which concludes that there is whole lot of positives about index compression, and very little negatives.  But obviously one critical thing is choosing the right number of leading columns to compress. Is it just “take a guess?” .  Luckily, Oracle has taken the guesswork out of it.

The ANALYZE command on an index can let you find the optimial compression count.  When you do an ANALYZE INDEX command, two critical columns are populated:

SQL> desc index_stats
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 HEIGHT                                 NUMBER
 BLOCKS                                 NUMBER
 NAME                                   VARCHAR2(128)
 PARTITION_NAME                         VARCHAR2(128)
 LF_ROWS                                NUMBER
 LF_BLKS                                NUMBER
 LF_ROWS_LEN                            NUMBER
 LF_BLK_LEN                             NUMBER
 BR_ROWS                                NUMBER
 BR_BLKS                                NUMBER
 BR_ROWS_LEN                            NUMBER
 BR_BLK_LEN                             NUMBER
 DEL_LF_ROWS                            NUMBER
 DEL_LF_ROWS_LEN                        NUMBER
 DISTINCT_KEYS                          NUMBER
 MOST_REPEATED_KEY                      NUMBER
 BTREE_SPACE                            NUMBER
 USED_SPACE                             NUMBER
 PCT_USED                               NUMBER
 ROWS_PER_KEY                           NUMBER
 BLKS_GETS_PER_ACCESS                   NUMBER
 PRE_ROWS                               NUMBER
 PRE_ROWS_LEN                           NUMBER
 OPT_CMPR_COUNT                         NUMBER
 OPT_CMPR_PCTSAVE                       NUMBER

Lets look at an example – we’ll create a table with 10 columns with all sorts of interesting distributions

SQL> drop table T purge;

Table dropped.

SQL> create table T as
 2   select
 3     rownum r01,
 4     mod(rownum,10) r02,
 5     mod(rownum,100) r03,
 6     mod(rownum,1000) r04,
 7     trunc(rownum/10) r05,
 8     trunc(rownum/100) r06,
 9     trunc(dbms_random.value(1,100)) r07,
10     trunc(dbms_random.value(1,1000)) r08  ,
11     trunc(mod(rownum,100)/10) r09,
12     mod(trunc(rownum/10),100) r10
13  from
14    ( select 1 from dual connect by level <= 1000 ),
15    ( select 1 from dual connect by level <= 1000 )
16  /

Table created.

We’ve used a technique described here https://www.youtube.com/watch?v=UonikfFgEyM to generate 1,000,000 rows.

Now with some SQL trickery, we can generate every possible combination of 3 column indexes for this table.

SQL> with inds as ( select rownum r from dual connect by level <= 10 )
  2  select  '(r'||i1.r||',r'||i2.r||',r'||i3.r||')' ix_cols
  3  from inds i1, inds i2, inds i3
  4  where i1.r != i2.r
  5  and   i2.r != i3.r
  6  and   i1.r != i3.r
  7  /

IX_COLS
---------------------------------------------------------------
(r1,r2,r3)
(r1,r2,r4)
(r1,r2,r5)
(r1,r2,r6)
(r1,r2,r7)
(r10,r9,r4)
...
...
(r10,r9,r5)
(r10,r9,r6)
(r10,r9,r7)
(r10,r9,r8)

So, now we’ll create a table called IND_STATS to hold the results of our ANALYZE command, because every time you issue ANALYZE INDEX the INDEX_STATS view is cleared.

SQL> create table IND_STATS as select name, OPT_CMPR_COUNT,  OPT_CMPR_PCTSAVE from index_stats where 1=0;

Table created.

And now we’ll just loop around all 720 potential indexes and work out the optimal compression.

SQL> begin
  2  for i in (
  3    with inds as ( select rownum r from dual connect by level <= 10 )
  4    select 'IX_'||i1.r||'_'||i2.r||'_'||i3.r ix_name,
  5           '(r'||to_char(i1.r,'fm00')||',r'||to_char(i2.r,'fm00')||',r'||to_char(i3.r,'fm00')||')' ix_cols
  6    from inds i1, inds i2, inds i3
  7    where i1.r != i2.r
  8    and   i2.r != i3.r
  9    and   i1.r != i3.r
 10  )
 11  loop
 12    dbms_application_info.set_client_info(i.ix_name);
 13    begin
 14      execute immediate 'drop index IX';
 15    exception
 16      when others then null;
 17    end;
 18    execute immediate 'create index IX on t '||i.ix_cols;
 19    execute immediate 'analyze index IX validate structure';
 20    insert into IND_STATS select i.ix_cols, OPT_CMPR_COUNT,  OPT_CMPR_PCTSAVE from index_stats;
 21    commit;
 22  end loop;
 23  end;
 24  /

PL/SQL procedure successfully completed.

So now we can take a look at the compression recommendations for all of our potential 3 column indexes. (Note: I’m not suggesting you would actually index all 720 combinations ! Smile)

SQL> select * from ind_stats;

NAME                                     OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------------------------------------- -------------- ----------------
(r01,r02,r03)                                         0                0
(r01,r02,r04)                                         0                0
(r01,r02,r05)                                         0                0
(r01,r02,r06)                                         0                0
(r01,r02,r07)                                         0                0
(r01,r02,r08)                                         0                0
(r01,r02,r09)                                         0                0

...
...


(r02,r06,r05)                                         2               24
(r02,r06,r07)                                         2               26
(r02,r06,r08)                                         2               25
(r02,r06,r09)                                         2               26
(r02,r06,r10)                                         2               26
(r02,r07,r01)                                         2               26
(r02,r07,r03)                                         3               43
(r02,r07,r04)                                         3               39
(r02,r07,r05)                                         2               26
(r02,r07,r06)                                         2               28
(r02,r07,r08)                                         2               28
(r02,r07,r09)                                         3               43
(r02,r07,r10)                                         3               37
(r02,r08,r01)                                         2               29
(r02,r08,r03)                                         3               39

You can see that some indexes probably wont benefit at all from compression, whereas for others, ALL columns become candidates for compression and might save nearly half the size of index.

So you dont need to guess at the compression for your indexes – you can work it out using ANALYZE.



					

Compressed partitions are not compressed tables

So…you have got a big table and you’re lucky enough to have a partitioning license.  So far, so good.

Then you read about using compression to make your older, static data faster and smaller and you thought … “Cool!”

Let’s see that in action – first here is my uncompressed partition table

SQL> create table T ( x date, y int, z varchar2(50) )
  2  PARTITION BY RANGE (x) INTERVAL (INTERVAL '60' DAY)
  3    (PARTITION p1 VALUES LESS THAN (TO_DATE('01/01/2012','dd/mm/yyyy')  )
  4   )
  5  /

Table created.

SQL> insert /*+ APPEND */ into T
  2  select sysdate - 1000+rownum/100, trunc(rownum/1000), rpad('x',50)
  3  from dual
  4  connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','T',estimate_percent=>null)

PL/SQL procedure successfully completed.

SQL> select partition_name,  compression, num_rows / nullif(blocks,0) rows_per_blk
  2  from dba_tab_partitions
  3  where table_name = 'T'
  4  order by partition_position;

PARTITION_NAME                 COMPRESS ROWS_PER_BLK
------------------------------ -------- ------------
P1                             DISABLED   101.838046
SYS_P241                       DISABLED           80
SYS_P242                       DISABLED           80
SYS_P243                       DISABLED           80
SYS_P244                       DISABLED           80
SYS_P245                       DISABLED           80
SYS_P246                       DISABLED           80
SYS_P247                       DISABLED           80
SYS_P248                       DISABLED           80
SYS_P249                       DISABLED           80
SYS_P250                       DISABLED           80
SYS_P251                       DISABLED         17.5

and now I’ll compress all of the partitions

SQL> begin
  2  for i in (
  3  select partition_name,  compression
  4  from dba_tab_partitions
  5  where table_name = 'T' )
  6  loop
  7    execute immediate 'alter table t modify partition '||i.partition_name||' compress';
  8    execute immediate 'alter table t move partition '||i.partition_name;
  9  end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('','T',estimate_percent=>null)

PL/SQL procedure successfully completed.

SQL> select partition_name,  compression, num_rows / nullif(blocks,0) rows_per_blk
  2  from dba_tab_partitions
  3  where table_name = 'T'
  4  order by partition_position;

PARTITION_NAME                 COMPRESS ROWS_PER_BLK
------------------------------ -------- ------------
P1                             ENABLED    450.170455
SYS_P241                       ENABLED    206.896552
SYS_P242                       ENABLED    206.896552
SYS_P243                       ENABLED    206.896552
SYS_P244                       ENABLED    206.896552
SYS_P245                       ENABLED    206.896552
SYS_P246                       ENABLED    206.896552
SYS_P247                       ENABLED    206.896552
SYS_P248                       ENABLED    206.896552
SYS_P249                       ENABLED    206.896552
SYS_P250                       ENABLED    206.896552
SYS_P251                       ENABLED    20.2631579

12 rows selected.

so I give myself a nice big pat on the back and sit and bask in the glow of a job well done 🙂

But wait….I then want to merge a couple of those partitions…Let’s see what happens

SQL> col x new_value p1
SQL> select partition_name from user_tab_partitions
  2  where partition_position = 4;

PARTITION_NAME
------------------------------
SYS_P243

SQL> col x new_value p2
SQL> select partition_name from user_tab_partitions
  2  where partition_position = 5;

PARTITION_NAME
------------------------------
SYS_P244

SQL> alter table T merge partitions &&p1, &&p2 into partition NEW_PAR;

Table altered.

SQL> exec dbms_stats.gather_table_stats('','T',estimate_percent=>null)

PL/SQL procedure successfully completed.

SQL> select partition_name,  compression, num_rows / nullif(blocks,0) rows_per_blk
  2  from dba_tab_partitions
  3  where table_name = 'T';

PARTITION_NAME                 COMPRESS ROWS_PER_BLK
------------------------------ -------- ------------
SYS_P251                       ENABLED    20.2631579
SYS_P248                       ENABLED    206.896552
SYS_P245                       ENABLED    206.896552
NEW_PAR                        DISABLED   91.6030534
P1                             ENABLED    450.170455
SYS_P242                       ENABLED    206.896552
SYS_P246                       ENABLED    206.896552
SYS_P241                       ENABLED    206.896552
SYS_P247                       ENABLED    206.896552
SYS_P250                       ENABLED    206.896552
SYS_P249                       ENABLED    206.896552

11 rows selected.

Uh oh….the act of merging the two compressed partitions has resulted in an uncompressed partition.  Now I would be forced to do an “alter table move partition” if I wanted to get those compression benefits back.  That’s not fun 😦

Now let’s repeat the exercise, but this time…I knew in advance that I might want to compress that table sometime in the future

SQL> drop table T purge;

Table dropped.

SQL> create table T ( x date, y int, z varchar2(50) )
  2  PARTITION BY RANGE (x) INTERVAL (INTERVAL '60' DAY)
  3    (PARTITION p1 VALUES LESS THAN (TO_DATE('01/01/2012','dd/mm/yyyy')  )
  4   )
  5   compress
  6  /

Table created.

SQL> insert /*+ APPEND */ into T
  2  select sysdate - 1000+rownum/100, trunc(rownum/1000), rpad('x',50)
  3  from dual
  4  connect by level <= 100000;

100000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','T',estimate_percent=>null)

PL/SQL procedure successfully completed.

SQL> select partition_name,  compression, num_rows / nullif(blocks,0) rows_per_blk
  2  from dba_tab_partitions
  3  where table_name = 'T'
  4  order by partition_position;

PARTITION_NAME                 COMPRESS ROWS_PER_BLK
------------------------------ -------- ------------
P1                             ENABLED    450.170455
SYS_P261                       ENABLED    206.896552
SYS_P262                       ENABLED    206.896552
SYS_P263                       ENABLED    206.896552
SYS_P264                       ENABLED    206.896552
SYS_P265                       ENABLED    206.896552
SYS_P266                       ENABLED    206.896552
SYS_P267                       ENABLED    206.896552
SYS_P268                       ENABLED    206.896552
SYS_P269                       ENABLED    206.896552
SYS_P270                       ENABLED    206.896552
SYS_P271                       ENABLED    20.2631579

12 rows selected.

SQL> exec dbms_stats.gather_table_stats('','T',estimate_percent=>null)

PL/SQL procedure successfully completed.

SQL> col x new_value p1
SQL> select partition_name x from user_tab_partitions
  2  where partition_position = 4;

X
--------------------------------------------------------------------------------------------------------------------------------
SYS_P263

SQL> col x new_value p2
SQL> select partition_name x from user_tab_partitions
  2  where partition_position = 5;

X
--------------------------------------------------------------------------------------------------------------------------------
SYS_P264

SQL> alter table T merge partitions &&p1, &&p2 into partition NEW_PAR;

Table altered.

SQL> exec dbms_stats.gather_table_stats('','T',estimate_percent=>null)

PL/SQL procedure successfully completed.

SQL> select partition_name,  compression, num_rows / nullif(blocks,0) rows_per_blk
  2  from dba_tab_partitions
  3  where table_name = 'T';

PARTITION_NAME                 COMPRESS ROWS_PER_BLK
------------------------------ -------- ------------
P1                             ENABLED    450.170455
SYS_P271                       ENABLED    20.2631579
SYS_P265                       ENABLED    206.896552
SYS_P266                       ENABLED    206.896552
SYS_P270                       ENABLED    206.896552
SYS_P267                       ENABLED    206.896552
SYS_P269                       ENABLED    206.896552
SYS_P262                       ENABLED    206.896552
SYS_P268                       ENABLED    206.896552
NEW_PAR                        ENABLED           300
SYS_P261                       ENABLED    206.896552

11 rows selected.

Well look at that !

It’s at this point, you’re probably thinking that this is a bug, or that you get “one shot” at this otherwise you’re doomed to not get compression.

But that’s not the case – it’s just a nuance of the way Oracle is interpreting your requests.  The MERGE PARTITION command is in effect creating a NEW partition, hence the new partition takes on the default compression clause at table level.  So, in the uncompressed example above, if I simply did

SQL> alter table T compress;

Table altered.

before I merge the partitions, then the resulting partition is indeed compressed.

Alternatively, although I can’t find an obvious reference in the documentation that this is permitted, the following also works:

SQL> alter table T merge partitions &&p1, &&p2 into partition NEW_PAR compress;

Table altered.

So to wrap up – be careful when manipulating partitions when dealing with compression – you might just end up with some uncompressed ones.