Re: Hash Clustered Table Question

On Jun 9, 8:20 pm, "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.

A clustered hash table by default allows duplicate key values to hash to the same hash key value. There is a PRIMARY KEY clause that can be used with hash clusters:

From 9.2 DBA Admin >>
CREATE TABLE trial (

    trialno NUMBER(5,0) PRIMARY KEY,
    ...)
    CLUSTER trial_cluster (trialno);
<<

If you have only one table and only 110 unique values you might want to use an IOT instead of a hash or index cluster.

Clusters are most useful where multiple tables are that share a common column are retrieved together, that is joined, based on the common column value.

A single table hash can be very fast but you always want to retireve by key and do not want to full scan. The total number of rows really needs to be a known constant.

HTH -- Mark D Powell -- Received on Tue Jun 10 2008 - 09:08:08 CDT