Re: Lookup/Code table as hot block

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

Other related posts: