Re: Hash clustering vs Indexing in ORACLE7SKIP
From: <pihlab_at_hhcs.gov.au>
Date: 4 Sep 92 08:52:37 +1000
Message-ID: <1992Sep4.085237.345_at_hhcs.gov.au>
>
> No.
>
> Hashing is an optional way of storing table data to improve the
> performance of data retrieval. To use hashing, a HASH CLUSTER
> is created and a table (or multiple tables) is loaded into the
> cluster. The rows of a table in a hash cluster are physically
> stored and retrieved according to the results of a hash
> function. A HASH FUNCTION is used to generate a distribution of
> numeric values, called HASH VALUES, which are based on specific
> cluster key values. [...]
> -- page 7-74, Oracle7 DBA Guide
>
> So you can hash a single table.
Date: 4 Sep 92 08:52:37 +1000
Message-ID: <1992Sep4.085237.345_at_hhcs.gov.au>
>>I believe hashing is only available for clusters and not for individual >>tables.
>
> No.
>
> Hashing is an optional way of storing table data to improve the
> performance of data retrieval. To use hashing, a HASH CLUSTER
> is created and a table (or multiple tables) is loaded into the
> cluster. The rows of a table in a hash cluster are physically
> stored and retrieved according to the results of a hash
> function. A HASH FUNCTION is used to generate a distribution of
> numeric values, called HASH VALUES, which are based on specific
> cluster key values. [...]
> -- page 7-74, Oracle7 DBA Guide
>
> So you can hash a single table.
YES, but I think what they actually mean is that you have to create a CLUSTER consisting of only one TABLE and that's the only way to use HAS INDEXES.
If you look at the syntax of the CREATE INDEX and CREATE CLUSTER commands you will notice that HASH INDEXES can only be created on CLUSTERs.
>
> You also mention the use of indexes for enforcing primary key
> uniqueness. You're better off defining a PRIMARY KEY constraint instead
> of an index -- indexes for lookup speed; constraints for uniqueness etc.
> (my interpretation of what's on p. 7-41, ibid).
The PRIMARY KEY constraint actually creates a UNIQUE Index in Oracle7 otherwise how would it ensure uniqueness (a full table scan?).
--
Bruce... pihlab _at_ hhcs.gov.au
^^
*******************************************************************
* Bruce Pihlamae -- Database Administration *
* Commonwealth Department of Health, Housing & Community Services *
* Canberra, Australia (W) 06-289-7056 *
*******************************************************************
* These are my own thoughts and opinions, few that I have. *
*******************************************************************
Received on Fri Sep 04 1992 - 00:52:37 CEST