On Jun 9, 8:20*pm, "Dereck L. Dietz" <diet... (AT) ameritech (DOT) net> wrote:
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 --

On Jun 10, 1:20 am, "Dereck L. Dietz" <diet... (AT) ameritech (DOT) net> wrote:
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.

On Jun 10, 1:20 am, "Dereck L. Dietz" <diet... (AT) ameritech (DOT) net> wrote:
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.

On Jun 10, 1:20 am, "Dereck L. Dietz" <diet... (AT) ameritech (DOT) net> wrote:
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.

Dereck L. Dietz wrote:
The obvious question would be what problem are you trying to solve? If
the the table is small enough, your memory large enough and is hit often
enough, may end up being cached in memory anyway. If it is that small,
just pin it in memory and your I/O is moot. If it is just for the
experience, the others have answered well.

Dereck L. Dietz wrote:
The obvious question would be what problem are you trying to solve? If
the the table is small enough, your memory large enough and is hit often
enough, may end up being cached in memory anyway. If it is that small,
just pin it in memory and your I/O is moot. If it is just for the
experience, the others have answered well.