Re: Hash Clusters .vs. Indexed Tables

Robert Walters wrote:
>
> Esteemed Wizards,
>
> I have a theoretical question. If I have a lookup table, very static,
> and very small, does it make sense to put an index on the table or
> would creating a hash cluster be better.
>
> For example:
>
> create table state (
> abbrev varchar2(2) not null,
> country varchar2(8) not null,
> name varchar2(30) not null);
>
> Other than in eastern europe, the values in this table do not change
> often.
>
> I put an index on abbrev and country so that the table does not
> have a full table scan, and throw off the rule based optimizer. This
> makes two I/Os to read the index and the table.
>
> According to Oracle, turning this into a hash cluster would reduce it
> to one I/O.
>
> From a theoretical standpoint, would it be more efficient to create a
> hash cluster? How about the practical aspect?
>
> Any replies are appreciated.
>
> Bob Walters bobwal_at_infocom.com
> Florists' Mutual Insurance Co.

To me it only makes sense to create a hash cluster if you have more than one table with the same key. In a hash cluster, Oracle stores all info with the same key on the same page (theoretically, that is). This means that when you get data from the cluster, any info with a particular key is retrieved in one I/O.

For a single table, I don't think the performance gain would be noticable, especially with a table limited in size

Roald.

-- 

+-------------------------------------------------------+
| R.A. van Geleuken                     roald_at_xs4all.nl |
+-------------------------------------------------------+
| There's no such thing as impossibility, only          |
| extremely low chance                                  |
+-------------------------------------------------------+