Re: Index compression vs. table compression

In article <cst15a$ivk$1_at_news-02.connect.com.au>, Howard J. Rogers says...
>
>Thomas Kyte wrote:
>> 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.
>
>
>I didn't say you couldn't cite me examples of single key table access
>via the index still being cheaper than via a FTS for a small table. I
>said it was "likely" that small tables would be read via FTS for a
>lookup, not that it would invariably be so. The threshold you refer to
>is significant in this discussion regarding what constitutes "small"
>and, I would suggest, what constitutes "likely".
>
>SQL> create table e as select * from emp;
>Table created.
>
>SQL> insert into e select * from e;
>14 rows created.
>
>SQL> /
>28 rows created.
>
>SQL> /
>56 rows created.
>
>SQL> /
>112 rows created.
>
>SQL> /
>224 rows created.
>
>SQL> commit;
>Commit complete.
>
>SQL> create index e_empno_idx on e(empno);
>Index created.
>
>SQL> analyze table e compute statistics;
>Table analyzed.
>
>SQL> set autotrace traceonly explain
>SQL> select * from e where empno=7934;
>
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=32 Bytes=1024
> )
>
> 1 0 TABLE ACCESS (FULL) OF 'E' (Cost=2 Card=32 Bytes=1024)
>
>
>I would call 448 rows "small". And "where empno=7934" was a single key
>lookup.
>
>Regards
>HJR
I think most people think of a single key lookup as "a lookup by key that returns a single row -- a lookup".

Lookups are generally considered (in my experience) to return a single row.

You have a big range scan -- you have 32 rows that will be returned by that, and 32 rows that are "far apart" from eachother (eg: the way the data was loaded into a heap table would make it so that each block in this table -- howevever many that was, would have empno 7934 on it.)

If you put it back to a lookup situation -- take this "key" and turn it into another "value" -- it reverts once again:

ops$tkyte_at_ORA9IR2> select count(*) from emp where empno = 7934;  

  COUNT(*)

        32  

ops$tkyte_at_ORA9IR2> update emp set empno = rownum;  

448 rows updated.  

ops$tkyte_at_ORA9IR2> create index e_empno_idx on emp(empno);  

Index created.  

ops$tkyte_at_ORA9IR2> exec dbms_stats.gather_table_stats( user, 'EMP', cascade=>true );  

PL/SQL procedure successfully completed.  

ops$tkyte_at_ORA9IR2> set autotrace traceonly explain ops$tkyte_at_ORA9IR2> select * from emp where empno = 42;  

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=37)    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=1 Bytes=37)    2 1 INDEX (RANGE SCAN) OF 'E_EMPNO_IDX' (NON-UNIQUE) (Cost=1 Card=1)      

ops$tkyte_at_ORA9IR2> set autotrace off

it is only when you start range scanning largish amounts of data relative to the size of the table and the data is spread all over that the full scan kicks in.

So, it was in followup to this:

</quote>

>>is then a further problem: how is the optimiser likely to read small,
>>useful, lookup tables?.. er, via a FTS, probably, if they are genuinely
>>small.
>
>
> 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...
</quote>

that I said "er, well, actually no -- they are not"

a keyed lookup -- single value, single row -- full scan will in fact not win "probably" or "always likely"

(opposite is true...)

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