Re: Index compression vs. table compression

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Fri, 31 Dec 2004 12:34:42 -0800
Message-ID: <41d5b6c6$1_1@127.0.0.1>

Howard J. Rogers wrote:

> Jonathan Lewis wrote:
>
>>> Therefore, we
>>> want a mechanism that will say "if you are read by a FTS, stay at the
>>> cold end of the LRU list, even though you are actually the most
>>> recently used block"... and that is precisely what the *NOCACHE*
>>> clause does. But there 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...

SQL*Plus: Release 10.1.0.3.0 - Production on Fri Dec 31 12:29:42 2004

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> create table t (

2 testcol varchar2(20));

Table created.

SQL> alter table t

   2 add constraint pk_t
   3 primary key (testcol)
   4 using index
   5 /

Table altered.

SQL> insert into t values ('ABC');

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_schema_stats(OWNNAME=>'UWCLASS', CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL> EXPLAIN PLAN
2 SET STATEMENT_ID = 'UW'
3 FOR SELECT * FROM t WHERE testcol = 'ABC';

Explained.

SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT

Plan hash value: 572282914

|   0 | SELECT STATEMENT  |      |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| PK_T |     1 |     4 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

1 - access("TESTCOL"='ABC')

13 rows selected.

SQL> I see an INDEX UNIQUE SCAN not an FTS.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Fri Dec 31 2004 - 14:34:42 CST