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: Lionel Mandrake <nobody_at_nospam.nowhere.nohow>
Date: Sat, 29 Dec 2001 02:06:08 GMT
Message-ID: <ky9X7.31604$Z03.18507013@news1.rdc1.mi.home.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

It would depend on many factors including 'how small is small', the selectivity of the index (if created), init parameters like db_file_multiblock_read_count, db_block_size, cbo/rbo, the version of Oracle you are using, etc... Why don't you try it out and use tkprof with sql_trace to see what is best for your situation?

HTH,

Received on Fri Dec 28 2001 - 20:06:08 CST

Original text of this message

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