Re: Indexes on very small tables

I'm sorry, but I disagree. The answer is not that simple. I'm going to answer based on my experience on RULE optimizer, because I have very little exprience on COST BASED optimizer.

Supose you have to tables A and B and make a join:

select ...
from a, b
where a.pk = b.fk

and table A has only 2 rows but table B has 1,000,000 rows. If you don't have an index on table A, Oracle will sort A (fast), sort B (slowwww) and then make a merge join. If you create an index on A, Oracle will do a next loop (B as outer, A inner) and will be much faster. In this case, having a index is a good deal.

But I suggest you to make all PKs enabled. It will make your database safer against inconsitencies. PKs should be enforced and indexes on PKs (or PKs enabled) is the way to do it.

Regards,

Carlos
Software Design Informatica
Brazil

In article <ihbh3.161$Ay3.196478976_at_momma.bigmomma.com>,   "Michael Zhang" <zhangliuqing_at_hotmail.com> wrote:
> Hi:
>
> The way Oracle use index is like this:
> 1 read index block
> 2 if data entry is found in the index block, read data block.
> So, if the data of your table is larger than 2 blocks, it's useful to
use
> index, for 1 and 2 blocks tables, indexes are not necessary.
>
> Michael.
>
> > Quick question:
> >
> > Will an index improve performance if the indexed table fits in one
> > Oracle block?
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jul 08 1999 - 23:48:39 CDT