Re: Do indexes matter on db in memory

Absolutely true:

There is still a rumour going
around that a table of less than
a couple of blocks does not need
an index.

However a 16K block could easily hold
250 rows, and a plan like:

nested loop

    table access by rowid on large table returning 1000 rows

        index range scan on large index
    table scan on 250 row table

Would end up doing 250,000 comparisons.
for a cpu cost of perhaps 5-10 seconds.

Whereas

nested loop

    table access by rowid on large table returning 1000 rows

        index range scan on large index
    table access by rowid on 250 row table

        index unique scan

Might do 1,000 comparisons for a CPU
cost of a few hundredths of a second.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Adrian J. Shepherd wrote in message ...


>I would say that even if everything (i/o) took place in memory, an in

 memory


>index lookup would be faster than an in memory full table scan due to a

 lack


>of an index...A couple of million extra gets might start to make a

>difference. This execution plan difference might not show up in the real

>world though in terms of noticeable performance gain or loss...

>