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.

If your table is <= 5 blocks then you don't need an index at all.

If its likely to be in the SGA because its used a lot then full table scans are probably faster than the index lookup.

> >
> > 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.

What you have done though is cause an index lookup and then a read of the data block as well.

To speed this up you could make the index equal a concatenation of all 3 columns and your query would then be fully satisfied from the index blocks and not have to do the extra i/o to the data block.

> >
> > According to Oracle, turning this into a hash cluster would reduce it
> > to one I/O.

Hash indexing is really only of benefit where you have really large indexes and therefore have several B-Tree levels to step through (I/Os) to get to a leaf entry.

--
  -----------------
   T T T T T T T T        Bruce Pihlamae
   I I I I I I I I        bpihlama_at_nla.gov.au
   I I I I I I I I        National Library of Australia
   T T T T T T T T        Phone: +616 262-1575
  -----------------       Fax:   +616 273-2116
 ===================
 "If you swallow a live frog first thing in the morning;
  nothing worse will happen to either of you that day."