FreeLists / oracle-l / Re: Lookup/Code table as hot block

["Stefan Knecht" <knecht.stefan@xxxxxxxxx>]

You could try to put the table into a single table hash cluster, and specify
pctfree and pctused according to what jared suggested. Using a cluster has
the additional benefit of reducing ios, as compared to an index + table
lookup which in turn reduces latches and contention (getting one or more
index blocks - root -> branch -> leaf, plus eventually one table access by
rowid , versus a direct key to rowid conversion access going directly to the
table).


test@CENTRAL> create cluster c1 (key int) single table hashkeys 100 pctfree
99 pctused 1;

Cluster created.

test@CENTRAL> create table t1 (key int, data1 varchar2(20), data2
varchar2(20)) cluster c1 (key);

Table created.

test@CENTRAL>
test@CENTRAL> insert into t1 values (1,'a','a');

1 row created.

test@CENTRAL> insert into t1 values (2,'b','b');

1 row created.

test@CENTRAL> insert into t1 values (3,'c','c');

1 row created.

test@CENTRAL> commit;

Commit complete.

test@CENTRAL> select dbms_rowid.rowid_block_number(rowid) from t1

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                                 51
                                150
                                185

test@CENTRAL> exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

test@CENTRAL> set autotrace on stat exp

test@CENTRAL> select * from t1 where key = 3;

      KEY DATA1                DATA2
---------- -------------------- --------------------
        3 c                    c


Execution Plan
----------------------------------------------------------
Plan hash value: 3152376638

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |     0   (0)|
|*  1 |  TABLE ACCESS HASH| T1   |     1 |     7 |            |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - access("KEY"=3)


Statistics
----------------------------------------------------------
         1  recursive calls
         0  db block gets
         3  consistent gets
         0  physical reads
         0  redo size
       520  bytes sent via SQL*Net to client
       385  bytes received via SQL*Net from client
         2  SQL*Net roundtrips to/from client
         0  sorts (memory)
         0  sorts (disk)
         1  rows processed

test@CENTRAL>

That should scale really well too.

Stefan