Re: Index compression vs. table compression

In article <41d57a50$0$5112$afc38c87_at_news.optusnet.com.au>, Howard J. Rogers says...
>
>Jonathan Lewis wrote:
>> Notes inline
>>
>> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
>> news:41d49207$0$3805$afc38c87_at_news.optusnet.com.au...
>>
>>
>> Not if they're being used for doing lookups, I hope.
>
>Why?
>
>A small table is always likely to be read via a FTS using CBO. Even for
>a single key lookup...
>
>

not so, the table has to pass a threshold before that happens for a range scan and for a keyed lookup, it won't be true:

ops$tkyte_at_ORA9IR2> create table t ( x int primary key, y char(25) default 'x' ) tablespace manual;
Table created.  

ops$tkyte_at_ORA9IR2> insert into t (x) values ( 1 ); 1 row created.  

ops$tkyte_at_ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );
PL/SQL procedure successfully completed.  

ops$tkyte_at_ORA9IR2> set autotrace traceonly explain ops$tkyte_at_ORA9IR2> select * from t where x = 1;  

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=29)    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=29)    2 1 INDEX (UNIQUE SCAN) OF 'SYS_C004806' (UNIQUE)   ops$tkyte_at_ORA9IR2> select * from t;  

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=29)    1 0 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1 Bytes=29)      

ops$tkyte_at_ORA9IR2> set autotrace off

The cost of the index hip hop is less than the cost of the full scan, even for a single block table.

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation