Re: Index compression vs. table compression

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sat, 22 Jan 2005 18:59:34 +1100
Message-ID: <cst15a$ivk$1@news-02.connect.com.au>

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 Received on Sat Jan 22 2005 - 01:59:34 CST