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

Apex Interactive Grid and IOT’s

I love the interactive grid in Application Express.  And here’s why… (Warning: Ranting mode is now on Smile)

You can tell people

  • here’s an application built with almost no code, in fact, you probably could have built it yourself
  • it’s multi-user, with optimistic locking built in for you
  • it’s secure
  • it’s backed up and recoverable,
  • it’s scales ridiculously well,
  • it doesn’t need any complicated middle tier, or software libraries,
  • it can be accessed anywhere you have a browser…which is…anywhere!
  • it has responsive look and feel,
  • it was built with software that doesn’t cost a single dollar,
  • it centralises the data so you have a single source of truth

and after you have told them all of that….do you know what they’ll say ?

“Yeah…but I like to double-click on a field to edit it…So I’ll just use Excel and store it on my hard drive”

AGGGGHHHHHHHH!!!!!!!!!!!!!  Somebody…..shoot….me……

Interactive grids blow that flaccid argument out of the water!  So get on board to Application Express 5.1+ for some interactive grid awesome-ness.

One discovery I did make with interactive grids, is that if you based the grid on an Index-Organized table, you will erroneously get a ROWID column in your grid

int_grid_iot_proj_short

 

This is not a major drama – just delete the item from the designer and it will still work just fine, and this minor detail is fixed in an upcoming release.

How important is "up to date" to you ?

I wrote a post a while back showing how one of the options with an Oracle Text index is to keep that index perfectly updated with its underlying table.  (Long termers with Oracle may know that this is a relatively recent addition to Text indexes, which historically were updated with calls to CTXSYS.CTX_DDL.SYNC_INDEX, either on demand or via the scheduler).

I also said that there is “no such thing as a free lunch”, as refreshing that index for every single commit introduces overheads.  So let’s explore that a little more thoroughly

First I sourced a file of hundreds of thousands of random english sentences, and exposed them to Oracle via an external table


SQL> DROP TABLE sentences;

Table dropped.

SQL>
SQL> CREATE TABLE sentences (
  2    line  VARCHAR2(4000)
  3  )
  4  ORGANIZATION EXTERNAL
  5  (
  6    TYPE ORACLE_LOADER
  7    DEFAULT DIRECTORY temp
  8    ACCESS PARAMETERS
  9    (
 10      RECORDS DELIMITED BY NEWLINE
 11      BADFILE 'sentences.bad'
 12      LOGFILE 'sentences.log'
 13      FIELDS TERMINATED BY '@'
 14      MISSING FIELD VALUES ARE NULL
 15      (
 16        line  CHAR(4000)
 17      )
 18    )
 19    LOCATION ('english.dat')
 20  )
 21  REJECT LIMIT UNLIMITED
 22  /

Table created.

SQL> SELECT * FROM sentences where rownum <= 10;

LINE
-------------------------------------------------------------
Let's try something.
I have to go to sleep.
Today is June 18th and it is Muiriel's birthday!
Muiriel is 20 now.
The password is "Muiriel".
I will be back soon.
I'm at a loss for words.
This is never going to end.
I just don't know what to say.
That was an evil bunny.

10 rows selected.

And now we will load 50,000 of those sentences into a Text-indexed table, one row at a time, committing (and syncing) for every row inserted.


SQL> create table t1 (
  2  x int,
  3  y varchar2(3000) ) tablespace demo
  4  ;

Table created.

SQL>
SQL>
SQL> begin
  2  ctx_ddl.create_preference('demo_text_idx', 'BASIC_STORAGE');
  3  ctx_ddl.set_attribute('demo_text_idx', 'I_TABLE_CLAUSE', 'tablespace demo');
  4  ctx_ddl.set_attribute('demo_text_idx', 'K_TABLE_CLAUSE', 'tablespace demo');
  5  ctx_ddl.set_attribute('demo_text_idx', 'R_TABLE_CLAUSE', 'tablespace demo');
  6  ctx_ddl.set_attribute('demo_text_idx', 'N_TABLE_CLAUSE', 'tablespace demo');
  7  ctx_ddl.set_attribute('demo_text_idx', 'I_INDEX_CLAUSE', 'tablespace demo');
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> create index t1_iz on T1  ( y )
  2  indextype is ctxsys.context
  3  parameters ('sync (on commit) storage demo_text_idx');

Index created.

SQL> conn mcdonac/******
Connected.

SQL> set timing on
SQL>
SQL> begin
  2  for i in ( select rownum r, line from sentences where rownum <= 50000 )   3  loop   4    insert into t1 values (i.r, i.line );   5    commit;   6  end loop;   7  end;   8  / PL/SQL procedure successfully completed. Elapsed: 00:14:34.98 SQL> @stat

[some stats omitted]

 SID_STAT# NAME                                                    VALUE
---------- -------------------------------------------------- ----------
         6 user commits                                           250000
         9 recursive calls                                       4351635
        14 session logical reads                                 7248007
        19 CPU used by this session                                35831
        55 physical read total bytes                           410017792
       128 db block gets                                         2387570
       132 consistent gets                                       4860437
       146 physical read bytes                                 407281664
       147 db block changes                                      3112305
       148 consistent changes                                      50041
       159 physical write bytes                                   712704
       258 redo size                                           666954796
      1089 execute count                                         2000136

1162 rows selected.

SQL> select segment_name, bytes from dba_segments
  2  where tablespace_name = 'DEMO'
  3  order by 1;

SEGMENT_NAME                                  BYTES
---------------------------------------- ----------
DR$T1_IZ$I                                 14680064
DR$T1_IZ$R                                    65536
DR$T1_IZ$X                                 10485760
DRC$T1_IZ$R                                   65536
SYS_IL0000162067C00006$$                      65536
SYS_IL0000162072C00002$$                      65536
SYS_IOT_TOP_162070                          2097152
SYS_IOT_TOP_162076                            65536
SYS_LOB0000162067C00006$$                    131072
SYS_LOB0000162072C00002$$                   2293760
T1                                          3145728

11 rows selected.

So we can see that

  • the process took a little over 14 minutes
  • consumed around 660M of redo
  • the segment size totalled around 31M

Let’s now repeat the exercise with a more relaxed strategy on index freshness.  We will still commit every row inserted, but only sync the index every 100 rows.



SQL> drop table t1 purge;

Table dropped.

SQL>
SQL> create table t1 (
  2  x int,
  3  y varchar2(3000) ) tablespace demo
  4  ;

Table created.

SQL>
SQL>
SQL> create index t1_iz on T1  ( y )
  2  indextype is ctxsys.context
  3  parameters ('storage demo_text_idx');

Index created.

SQL>
SQL> conn mcdonac/******
Connected.

SQL> begin
  2  for i in ( select rownum r, line from sentences where rownum <= 50000 )   3  loop   4    insert into t1 values (i.r, i.line );   5    commit;   6   7    if mod(i.r,100) = 0 then   8      ctxsys.ctx_ddl.sync_index('T1_IZ');   9    end if;  10  end loop;  11  end;  12  / PL/SQL procedure successfully completed. Elapsed: 00:00:38.45 SQL> select segment_name, bytes from dba_segments
  2  where tablespace_name = 'DEMO'
  3  order by 1;

SEGMENT_NAME                        BYTES
------------------------------ ----------
DR$T1_IZ$I                       10485760
DR$T1_IZ$R                          65536
DR$T1_IZ$X                        7340032
DRC$T1_IZ$R                         65536
SYS_IL0000162255C00006$$            65536
SYS_IL0000162260C00002$$            65536
SYS_IOT_TOP_162258                2097152
SYS_IOT_TOP_162264                  65536
SYS_LOB0000162255C00006$$          131072
SYS_LOB0000162260C00002$$         2293760
T1                                3145728

11 rows selected.

Elapsed: 00:00:00.02
SQL>
SQL> @stat

 SID_STAT# NAME                                                    VALUE
---------- -------------------------------------------------- ----------
         6 user commits                                            52000
         9 recursive calls                                        349084
        14 session logical reads                                 1352820
        19 CPU used by this session                                 2719
        55 physical read total bytes                             6709248
       128 db block gets                                          940374
       132 consistent gets                                        412446
       146 physical read bytes                                   4087808
       147 db block changes                                      1184146
       148 consistent changes                                        539
       159 physical write bytes                                   712704
       258 redo size                                           270443980
      1089 execute count                                          176163

1162 rows selected.


By relaxing our sync strategy, now we have

  • the process took less than 1 minute
  • consumed around 270M of redo
  • the segment size totalled around 24M

Hence if your document loading apparatus is high volume OLTP style, you might want to carefully consider the impact of saying to your customers “Hey, we’ll keep that index updated in real time”.  If it truly is high volume OLTP style…it is unlikely they would even notice a more streamlined approach with regular sync’s, where “regular” is defined either on a time interval or pending-document count threshold.  Conversely, if document loading is rare, then an on-commit strategy combined with an occasional optimize and/or rebuild might be perfectly adequate.

Direct mode operations on IOT’s

An AskTom contributor brought to my attention, that direct mode insert on index organized tables now appears possible in 12c.  We can see the difference by running a simple script in both v11 and v12


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

5 rows selected.

SQL> drop table t purge;

Table dropped.

SQL> create table t(x int primary key, y int) organization index;

Table created.

SQL> insert /*+ append */ into t select 1 x, 1 y from dual;

1 row created.

SQL> select * from t;  -- no error, so no direct mode was done

         X          Y
---------- ----------
         1          1

1 row selected.


SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                        0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                                   0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

5 rows selected.

SQL> drop table t purge;

Table dropped.

SQL> create table t(x int primary key, y int) organization index;

Table created.

SQL> insert /*+ append */ into t select 1 x, 1 y from dual;

1 row created.

SQL> select * from t;  -- now we do get the error, so append hint was respected
select * from t
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

So that looks pretty cool. People may recall an older blog post of mine where it was not possible to do a large scale IOT population without a large underlying sort operation. So I thought I’d revisit that, and see what we can discover.

In this case, we are using table T_SOURCE as the source of our data. It was created simply as:


SQL> create table T_SOURCE ( n , r , constraint T_SOURCE_PK primary key( n ))
  2  organization index
  3  as select rownum, rpad(rownum,128)
  4  from dual
  5  connect by level <= 1000000
  6  /

Table created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','t_source')

PL/SQL procedure successfully completed.

So we are going to copy those 1million rows from T_SOURCE to a new table, and try to avoid having to sort the rows by asking the optimizer to “walk” the source data in index order.

So let’s try direct mode first. You’ll references to a script called “mystat”, that’s simply a query to v$mystat to pick up session level statistics that might be relevant.

We’ll run these before and after to see what deltas pop out.


SQL> truncate table t_tgt;

Table truncated.

SQL> alter table T_TGT nologging;

Table altered.

SQL> @mystat
Enter value for statname: redo size

NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                              135736
redo size for lost write detection                          0
redo size for direct writes                                 0

3 rows selected.

SQL> @mystat
Enter value for statname: sorts

NAME                                                    VALUE
-------------------------------------------------- ----------
sorts (memory)                                            117
sorts (disk)                                                0
sorts (rows)                                              359

3 rows selected.

SQL> set timing on
SQL> insert /*+ APPEND */ into T_TGT select /*+ index_asc(t_source) */ * from T_SOURCE order by 1  ;

1000000 rows created.

Elapsed: 00:00:46.30

SQL> select count(*) from t_tgt where rownum < 10;
select count(*) from t_tgt where rownum < 10
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


SQL> commit;

Commit complete.

SQL> @mystat
Enter value for statname: redo size

NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                           497096676
redo size for lost write detection                          0
redo size for direct writes                                 0

3 rows selected.

SQL> @mystat
Enter value for statname: sorts

NAME                                                    VALUE
-------------------------------------------------- ----------
sorts (memory)                                            122
sorts (disk)                                                1
sorts (rows)                                          1000436

3 rows selected.

SQL> @mystat
Enter value for statname: direct

NAME                                                    VALUE
-------------------------------------------------- ----------
db block gets direct                                        0
consistent gets direct                                      0
physical reads direct                                   19231
physical writes direct                                  19231
physical reads direct temporary tablespace              19231
physical writes direct temporary tablespace             19231

 

So we can make the following deductions

  • Direct mode seems to have been activated, due to the ORA-12838 error.
  • I didn’t avoid the sorting, because I sorted 1,000,000 rows
  • The NOLOGGING attribute seems of no benefit here – still consumed 500megabytes of redo

So whether it was really a direct mode operation is perhaps up for debate, because all of the direct reads/writes recorded were all against temporary tablespace.

Let’s explore that last one a little.

I repeated the same population script, but before I did so, I set my workarea_size_policy to manual, and cranked up my sort_area_size to 1G.

I’ll save you all the output again, but here’s the stats output


SQL> @mystat
Enter value for statname: direct

NAME                                                    VALUE
-------------------------------------------------- ----------
db block gets direct                                        0
consistent gets direct                                      0
physical reads direct                                       0
physical writes direct                                      0
physical reads direct temporary tablespace                  0
physical writes direct temporary tablespace                 0

So…no direct operations here now. This would appear to confirm that the direct mode operations were simply sorting operations.

Now we’ll perform the same process without using the direct mode hint. We’ll set the table back to logging because we’ve seen that’s of no benefit, and use a fresh session to pick up fresh statistics


SQL> truncate table t_tgt;

Table truncated.

SQL> alter table T_TGT logging;

Table altered.

SQL> @mystat
Enter value for statname: redo size

NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                              137236
redo size for lost write detection                          0
redo size for direct writes                                 0

3 rows selected.

SQL> @mystat
Enter value for statname: sorts

NAME                                                    VALUE
-------------------------------------------------- ----------
sorts (memory)                                             21
sorts (disk)                                                0
sorts (rows)                                               86

3 rows selected.

SQL> insert into T_TGT select /*+ index_asc(t_source) */ * from T_SOURCE order by 1  ;

1000000 rows created.

Elapsed: 00:00:13.96
SQL> set timing off
SQL> select count(*) from t_tgt where rownum < 10;

  COUNT(*)
----------
         9

1 row selected.

SQL> commit;

Commit complete.

SQL> @mystat
Enter value for statname: redo size

NAME                                                    VALUE
-------------------------------------------------- ----------
redo size                                           500630220
redo size for lost write detection                          0
redo size for direct writes                                 0

3 rows selected.

SQL> @mystat
Enter value for statname: sorts

NAME                                                    VALUE
-------------------------------------------------- ----------
sorts (memory)                                             26
sorts (disk)                                                0
sorts (rows)                                              146

3 rows selected.

SQL> @mystat
Enter value for statname: direct

NAME                                                    VALUE
-------------------------------------------------- ----------
db block gets direct                                        0
consistent gets direct                                      0
physical reads direct                                       0
physical writes direct                                      0
physical reads direct temporary tablespace                  0
physical writes direct temporary tablespace                 0

So for the conventional mode we observe

  • I didn’t have to sort the rows
  • I consumed similar amount of redo
  • It was three times faster than the case where we had to so all that sorting to disk

This just goes to show, that (like everything in software), the advice of “Do ‘X’ and in every case it will be faster” is never a good thing to adhere to.

Direct mode insert is a very cool facility, but it doesn’t mean that it’s going to be the best option in every situation.

Quick tip on Function Based Indexes

For “normal” indexes, USER_IND_COLUMNS will contain the column name(s) for an index, but things (appear to) go astray when looking at function-based indexes.


SQL> create table blah ( x varchar2(30));

Table created.

SQL> create index blah_ix on blah ( upper(x));

Index created.

SQL> select column_name from user_ind_columns
  2  where index_name = 'BLAH_IX'
  3  /

COLUMN_NAME
------------------------------
SYS_NC00002$

Don’t panic. Simply take a look at USER_IND_EXPRESSIONS to find the function that you used.


SQL> select column_expression from user_ind_expressions
  2  where index_name = 'BLAH_IX'
  3  /

COLUMN_EXPRESSION
--------------------------------------------------------------------------------
UPPER("X")

Easy peasy

Up to date text indexes

If you really need your text indexes to be “super dooper” up to date, that is, always in sync with your data, you can set the SYNC level to be ON COMMIT.  For example,


SQL> create table t1 (
  2    x int,
  3    y varchar2(1000) )
  4  ;

Table created.

SQL>
SQL> create index t1_iz on T1  ( y )
  2  indextype is ctxsys.context
  3  parameters ('sync (on commit)');

Index created.

SQL>
SQL> insert into t1 values ( 1, 'The quick brown fox jumped over the lazy dog');

1 row created.

SQL> insert into t1 values ( 2, 'Pack my box with five dozen liquor jugs');

1 row created.

SQL> insert into t1 values ( 3, 'Several fabulous dixieland jazz groups played with quick tempo');

1 row created.

SQL> insert into t1 values ( 4, 'Back in my quaint garden, jaunty zinnias vie with flaunting phlox');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1
  2  where contains(y,'quick') > 0 ;

         X
----------
Y
----------------------------------------------------------------------------------------------------
         1
The quick brown fox jumped over the lazy dog

         3
Several fabulous dixieland jazz groups played with quick tempo

But don’t forget. There is no such thing as a free lunch. As has always been the case, the more you can index “in batch” the less fragmentation you get with your text index, and obviously, if you’re planning on doing a stack of activities when you commit…then your commit performance will be impacted. Let’s compare the insert’s above, with and without a text index in place.


--
-- With index
--
SQL> insert into t1 values ( 1, 'The quick brown fox jumped over the lazy dog');

1 row created.

SQL> insert into t1 values ( 2, 'Pack my box with five dozen liquor jugs');

1 row created.

SQL> insert into t1 values ( 3, 'Several fabulous dixieland jazz groups played with quick tempo');

1 row created.

SQL> insert into t1 values ( 4, 'Back in my quaint garden, jaunty zinnias vie with flaunting phlox');

1 row created.

SQL> set timing on
SQL> commit;

Commit complete.

Elapsed: 00:00:00.02

-- 
-- Without index 
-- 
SQL> insert into t1 values ( 1, 'The quick brown fox jumped over the lazy dog');

1 row created.

SQL> insert into t1 values ( 2, 'Pack my box with five dozen liquor jugs');

1 row created.

SQL> insert into t1 values ( 3, 'Several fabulous dixieland jazz groups played with quick tempo');

1 row created.

SQL> insert into t1 values ( 4, 'Back in my quaint garden, jaunty zinnias vie with flaunting phlox');

1 row created.

SQL> set timing on
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

Not too shabby…but noticeable.

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.