Re: Lookup/Code table as hot block
- From: "Stefan Knecht" <knecht.stefan@xxxxxxxxx>
- To: sjaffarhussain@xxxxxxxxx
- Date: Tue, 2 May 2006 11:37:25 +0200
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:
