Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index for very small tables needed ?

Re: Index for very small tables needed ?

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Mon, 31 Dec 2001 07:49:06 +1100
Message-ID: <3c2f7d62$0$2599$afc38c87@news.optusnet.com.au>


An index on a small table is likely to be a complete waste of time. That's because the optimizer is likely to conclude, whenever access to a piece of that table's data is requested, that it would be just as quick to scan the whole table in and get the record from there.

The reason *why* it might so conclude is that full table scans are performed not one block at a time, but by grabbing multiple blocks of the disk in one pass. Exactly how many blocks can be read like that depends on your hardware and O/S, and what your db_file_multiblock_read_count init.ora parameter is set to. Usually, it's something like the equivalent of 64K or 128K (but again, what it's set to ought to depend very much on what your hardware can actually perform).

In contrast, if we were to use an index, we would be reading single blocks. And there would be at least 3 such single blocks to read -the root node of the index, the index leaf node itself, and the table block containing the row requested (assuming the smallest and simplest index structure possible). Those three single blocks require 3 separate I/O operations.

Assume your "very small table" is 48K in size, and your db_file_multiblock_read_count is set to 64K -that means the table can be read in its entirety in one multi-block I/O operation (a full scan). Which do you think the optimizer would prefer to do: 1 I/O or 3?

It's not true, either, to say that data read via a full tablescan is not read into the buffer cache. All data is read into that cache. The question is more one of 'how quickly does it get flushed out again'? Blocks read via a full tablescan are placed around half-way down the LRU list, so that the moment they are read, they are well on their way to being candidates for being flushed out of the cache again, assuming we need the space.

Your small table blocks, being read via a full tablescan, would likewise be candidates for an early flush. But think about it: if any of those blocks are re-touched quite soon after initially being loaded into the cache, they will rapidly migrate up to the MRU end of the LRU list, and thus be retained in the cache. If they are not re-touched by a subsequent query or DML, then fair enough -they stay toward the LRU end of the list, and are vulnerable to flushing. But their only at risk of being flushed because no-one is touching them -in other words, no-one else needs that data. So why not flush them if they are of no use to anyone?

If you are that concerned that someone else doing a large scan might flush your small scan blocks before you get a chance to re-use them, then you can consider the CACHE clause if you've got Oracle 7, or the rather more useful KEEP buffer if you've got Oracle 8.0 and above. There's a couple of articles on both techniques on my web site.

But the short story is: full tablescans do not bypass the buffer cache at all, so there's no intrinsic reason why your small table should need to be re-read with additional I/O. However, the presence of an index on your 'very small' table is likely to be a matter of supreme irrelevance to the optimizer, but will always slow down any DML that you perform to the table (because the index has to be maintained as the base table data changes). Short answer, therefore: don't index small tables.

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"Andreas Wizemann" <AWizemann_at_fvvag.de> wrote in message
news:haml2ukh1eere5p9mcrp3k8jule26k6hn2_at_4ax.com...

> Hi,
> do i need to define an index on small tables ?
> I'm confused after reading different articles about this.
> One tells me i should define an index the other says there is no need,
> as a full table scan is fast enough.
> Also other articles say, oracle does not read rows in the buffer cache
> while doing a Full table scan. So i think, next time this table will
> be accessed there is another need of doing io.
> So now, whats best ?
> No Index with Full table scan and io's
> or
> Having an Index, so rows may stay in buffer ?
>
> Any Ideas ?
> Thanks a lot.
>
> Andreas Wizemann
Received on Sun Dec 30 2001 - 14:49:06 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US