Re: Index compression vs. table compression

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 31 Dec 2004 18:08:54 +0000 (UTC)
Message-ID: <cr44jm$no0$1@hercules.btinternet.com>

Note in line:

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:41d57a50$0$5112$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...
>

It's typically more expensive on CPU and latching to use an FTS for a simple lookup than it is to use a primary key indexed access. From a vanilla 9.2.0.6 setup:

SQL> drop table t1;

Table dropped.

SQL> create table t1 (

  2        id       number(6) not null,
  3        short_name      varchar2(10),
  4        long_name      varchar2(100)

  5 );

Table created.

SQL> alter table t1 add constraint t1_pk primary key(id);

Table altered.

SQL> insert into t1
  2 select

  3        rownum,
  4        lpad(rownum,10),
  5        rpad('x',100)
  6  from
  7        all_objects
  8  where
  9        rownum <= 50

 10 ;

50 rows created.

SQL> execute dbms_stats.gather_table_stats(user,'t1',cascade => true)

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select long_name from t1 where id = 25;

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=104)    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes=104)    2 1 INDEX (UNIQUE SCAN) OF 'T1_PK' (UNIQUE) (Cost=1 Card=1)

SQL> set autotrace off

SQL> select blocks from user_tables
  2 where table_name = 'T1';

    BLOCKS

---

         1

SQL> select name, value from v$parameter   2 where name in (

  3        'db_block_size',
  4        'db_file_multiblock_read_count'

  5 );

NAME                           VALUE
------------------------------ -----
db_block_size                  8192

db_file_multiblock_read_count 16

>
>>
>>>And therefore a further tool is needed: a mechanism which will
>>>distinguish between nasty, huge FTSes of bulky tables, and small, OK,
>>>FTSes of useful lookup tables.
>>
>>
>> But a 'genuinely small' table - which means less than 20
>> blocks or 2% of the number of buffers in the cache,
>> whichever is larger - is not automatically loaded to the
>> discard end of the LRU list, anyway
>
> Forget the "genuinely small". Deal with the actual issue being discussed
> here. How do you distinguish between benign and bad FTSes?
>

    Bad tablescans are ones that should not be happening,     and benign tablescans are ones that should be happening,     but probably will not be happening very often, because if     they happen frequently they are bad tablescans. If you suggest     that a 'small table lookup' should be a tablescan than you     are propagating a myth that needs to be corrected.

    I hadn't actually realised that this was the actual issue being     discussed here, though, I was under the impression that the     issue was a discussion on the mechanics of the buffer pools.

>> At best it could be described as the top of the cold
>> half.
>> Blocks are only promoted to the hot half after they have traversed
>> the cold half and are found to be suitable candidates for promotion
>> as they reach the discard end of the cold half.
>
> Whatever. Does this change the conclusions to be drawn from anything I've
> written? If not, say so. If yes, explain why.
>

    "Whatever" - that's a comment that doesn't go too well with another     comment of yours, viz:

        "and I wish you'd get your terminology correct: it's a LEAST
        recently used list, not an MRU one".

    I think one of your conclusions was this:

>> And that is what the *CACHE* clause
>> does: if you specify it as an attribute of a small lookup table, its
>> blocks will indeed be read into the hot half of the LRU list, *even
>> though they were read by a FTS*.

    Apart from the detail about the 'hot half' - the conclusion is     invalid, because a SMALL lookup table will be read into the     middle of the LRU whether you specify CACHE or NOCACHE.

    I think another of your conclusions was this:
>> All of which boils down to: CACHE/NOCACHE and KEEP/RECYCLE are trying to
>> do precisely the same thing.

    I think you are the primary complainant about 'sloppy descriptions'.     This is a very sloppy comment. One of the features of the KEEP     pool is that it can do a better job for SOME of the stuff that CACHE     does.

In passing - there is a bug that is only fixed in 10g that makes the KEEP cache a more appropriate target than the default cache if you do really need to do frequent __small__ tablescans i.e. tablescans that you would normally expect to 'survive' in the default cache. Received on Fri Dec 31 2004 - 12:08:54 CST