Re: Index for very small tables needed ?

A couple of things I can guess at:

If Oracle is aware that it may acquire several rows from a block in a single call, then it will pin the buffer - which means it has to create a state object, acquire the buffer header and insert the state object into the pin list - this has to be reversed when the block is released (unpinned) at the end of the fetch. By comparison, if Oracle is aware that the block will supply a single row (e.g. from a single indexed access) it does a simple get, without all the overhead of pinning.

Without knowing the source code, I can't tell you how Oracle traverses a block in a table scan, but each row has a length byte (or three), and each column has a length byte (or three) - so Oracle may have to keep counting bytes to the next offset time after time as it tries to find a specific column in each of the rows.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Stefan Moeding wrote in message <87pu4shc88.fsf_at_esprit.setuid.de>...


>Hi Jonathan!

>

>Jonathan Lewis writes:

>

>> Simple tests show that the "break point" from an indexed path to a

>> scan path can occur at dramatically different volumes of data because

>> of the way that the MULTIPLE cheap cost of finding and testing lots of

>> rows compares with the rare, but expensive cost of a block access.

>

>This is something I have been thinking about for a long time now.  What

>*exactly* causes the CPU time for accessing maybe 200 rows scattered

>over 3 blocks which are already cached in memory?

>

>Does Oracle step through the normal access path (hashing the block

>address, scanning the cache buffers chain, ...) for every single row or

>what makes it so expensive in terms of CPU time?

>

>Regards,

>Stefan