Re: IOT Vs Normal Tables

Murali,

If your query can be satisfied by the columns in an index, and the optimizer chooses that index, the table will not be read at all.

Thus, the IOT will not offer any performance improvements in this case.

This is well documented in the Oracle documentation if you care to take a look. ( I'm too lazy tonight to look it up :)

Jared

On Fri, 15 Dec 2000, Murali Vallath wrote:

> Jared,
>
> Since the index and data are stored together, my impression on this subject
> was IOT's will save you the extra I/O for the data lookup.
>
> With the B-TREE index structure being common, the savings should be in the
> data lookup.
>
> Also, from the Oracle Internals class I had the opportunity to attend in
> October, at Hustone TX, the instructor told us that with 9i the data
> dictionary will be using IOT.
>
> I have not tested it myself, but these are from training, conversations and
> reading knowledge.
>
> Murali Vallath
> Oracle Certified DBA
> http://www8.ewebcity.com/muralivallath
> http://www.summerksyus.com/
>
> Reply-To: ORACLE-L_at_fatcity.com
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Date: Wed, 13 Dec 2000 20:35:31 -0800
>
> On Wed, 13 Dec 2000, Sanjay Kumar wrote:
>
> > Hi,
> >
> > Can anyone tell me how to see the performance differences of using IOTs
> over the normal tables.
> >
> > Sanjay
> >
>
> I don't think you will see any performance increase
> from an IOT.
>
> You can get the same performance if your query can
> be satisfied by an index without reading the table.
> i.e. the index has all the columns to satisfy the query.
>
> IOT's just save space and complicate your life. :)
>
> Jared Still
> Certified Oracle DBA and Part Time Perl Evangelist ;)
> jkstill_at_teleport.com
> jared_still_at_enron.net
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: jared still
> INET: jkstill_at_teleport.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at http://explorer.msn.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Murali Vallath
> INET: murali_vallath_at_hotmail.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;) Received on Sun Dec 17 2000 - 22:19:23 CST