RE: Lookup/Code table as hot block

Remember that you have to be sure that the table will never have rows in
excess of the number of hashkeys if you choose this option.

 

Thanks,

Jay Miller

Sr. Oracle DBA

x68355

 

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 
Sent: Tuesday, May 02, 2006 5:37 AM
To: sjaffarhussain_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: 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_at_CENTRAL> create cluster c1 (key int) single table hashkeys 100
pctfree 99 pctused 1;

Cluster created.

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

Table created.

test_at_CENTRAL> 
test_at_CENTRAL> insert into t1 values (1,'a','a');

1 row created.

test_at_CENTRAL> insert into t1 values (2,'b','b');

1 row created.

test_at_CENTRAL > insert into t1 values (3,'c','c');

1 row created.

test_at_CENTRAL> commit;

Commit complete.

test_at_CENTRAL> select dbms_rowid.rowid_block_number(rowid) from t1

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

test_at_CENTRAL> exec dbms_stats.gather_table_stats(user,'T1'); 

PL/SQL procedure successfully completed.

test_at_CENTRAL> set autotrace on stat exp  

test_at_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_at_CENTRAL> 

That should scale really well too.

Stefan






-----------------------------------------
This message is confidential and sent by TD Waterhouse solely for
use by the intended recipient. If you are not the intended
recipient, you are hereby notified that any use, distribution or
copying of this communication is strictly prohibited. This should
not be deemed as an offer or solicitation, to buy or sell any
product. Any 3rd party information contained herein was prepared by
sources deemed reliable, but is not guaranteed. TD Waterhouse does
not accept electronic instructions that would require an original
signature. Information received by or sent from TD Waterhouse is
stored, subject to review, and may be produced to regulatory
authorities or others with a legal right to such.

--
http://www.freelists.org/webpage/oracle-l