Re: Hash Clustered Table Question

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: Tue, 10 Jun 2008 07:38:01 -0700 (PDT)
Message-ID: <1913cfb5-8c37-4738-a786-847557c0a759@t54g2000hsg.googlegroups.com>


On Jun 10, 1:20 am, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> I'm reading Tom Kyte's Expert Oracle Database Architecture about Hash
> Clusted Tables and have a question about something I'm obviously
> overlooking.
>
> I've never used hash tables before but wanted to experiment. I have a small
> lookup table with only 110 rows. If I make that into a hash clustered table
> will the hash key suffice or would I also have to include a primary key?
>
> Sorry if this is a simple question but I must be overlooking the obvious
> somewhere.
>
> Thanks.

If the table has a current primary key, then you can (and should create) create a primary key on the Hash Cluster table.

If I remember correctly, the single table hash cluster is ideally suited for tables where the number of rows is pretty much fixed, and you always fetch rows by the hash key.

The benefits are basically reduced I/O, usually just logical I/O - instead of the typical three index reads and then a read to get the table block, it can get the block directly. This also means the index doesn't need to be in the buffer cache, which yields another small saving.

So, if you have a table that has a fixed number of rows (lookup table) that you always access by primary key then its an ideal candidate for becoming a single table hash cluster. Received on Tue Jun 10 2008 - 09:38:01 CDT

Original text of this message