Key Compressed B* Tree Indexes

Key Compressed B* Tree Indexes
1) Our block buffer cache will be able to hold more index entries than before
2) our cache-hit ratio might go up
3) our physical I/Os should go down

create table t nologging as select * from all_objects;
insert /*+ append */ into t select * from T;
commit;
insert /*+ append */ into t select * from T;
commit;

create index uncompressed_idx on t( owner,object_type,object_name,0 ) nologging;
analyze index uncompressed_idx validate structure;

scott@10G> select height, blocks,lf_rows, br_rows, del_lf_rows, name
  2  from index_stats;

    HEIGHT     BLOCKS    LF_ROWS    BR_ROWS DEL_LF_ROWS NAME
---------- ---------- ---------- ---------- ----------- -----------------------
         3       1536     205540       1474           0 UNCOMPRESSED_IDX

create index compressed_idx on t( owner,object_type,object_name,1 ) COMPRESS 3 nologging;
analyze index compressed_idx validate structure;

scott@10G> select height, blocks,lf_rows, br_rows, del_lf_rows, name
  2  from index_stats;

    HEIGHT     BLOCKS    LF_ROWS    BR_ROWS DEL_LF_ROWS NAME
---------- ---------- ---------- ---------- ----------- -----------------
         3        768     205540        718           0 COMPRESSED_IDX


********************************************************************************
SELECT /*+ index (t uncompressed_idx) */ owner,COUNT(*)
FROM T
GROUP BY owner

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.07       0.08          0       1478          0          30
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.07       0.08          0       1478          0          30

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 

Rows     Row Source Operation
-------  ---------------------------------------------------
     30  SORT GROUP BY NOSORT (cr=1478 pr=0 pw=0 time=60 us)
 205540   INDEX FULL SCAN UNCOMPRESSED_IDX (cr=1478 pr=0 pw=0 time=29 us)(object id 64687)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.08          0.08
********************************************************************************

SELECT /*+ index (t compressed_idx) */ owner,COUNT(*)
FROM T
GROUP BY owner

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.09       0.09          0        722          0          30
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.09       0.09          0        722          0          30

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 

Rows     Row Source Operation
-------  ---------------------------------------------------
     30  SORT GROUP BY NOSORT (cr=722 pr=0 pw=0 time=74 us)
 205540   INDEX FULL SCAN COMPRESSED_IDX (cr=722 pr=0 pw=0 time=35 us)(object id 64689)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  SQL*Net message from client                     3        0.00          0.00
********************************************************************************