Re: Index compression vs. table compression

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sun, 23 Jan 2005 03:15:31 +1100
Message-ID: <cstu76$rra$1@news-02.connect.com.au>

Thomas Kyte wrote:
[snip]

>
> 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.

Not in mine.

> 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

Your example, it seems to me, simply demonstrates the smartness of the CBO in dealing with what it can determine to be searches for unique or nearly-unique data. I have no quarrel with that.

But you are having to carefully arrange for that uniqueness to happen (with your update of empno to rownum), and I would suggest that is not likely to be generally typical.

> 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...)

Then all we can say at the end of this is that your definition of a single key lookup is not mine.

I cannot know how many rows will be returned by a query until the lookup has taken place. I do, however, know how many key values I am looking up.

Regards
HJR Received on Sat Jan 22 2005 - 10:15:31 CST