RE: IOT Vs Normal Tables

From: Alex Hillman <alex_hillman_at_physia.com>
Date: Fri, 15 Dec 2000 12:53:35 -0500
Message-Id: <10711.124730@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C066BF.F232DA70
Content-Type: text/plain;

charset="iso-8859-1"

You will not need any extra I/O if index contain all columns to satisfy your query. I know several people used IOT and they hadn't any performance improvement, only storage savings.

Alex Hillman

-----Original Message-----
From: Murali Vallath [mailto:murali_vallath_at_hotmail.com] Sent: Friday, December 15, 2000 10:31 AM To: Multiple recipients of list ORACLE-L Subject: Re: IOT Vs Normal Tables

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).

------_=_NextPart_001_01C066BF.F232DA70
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2448.0">
<TITLE>RE: IOT Vs Normal Tables</TITLE>
</HEAD>
<BODY>

<P><FONT SIZE=3D2>You will not need any extra I/O if index contain all =
columns to satisfy your query. I know several people used IOT and they =
hadn't any performance improvement, only storage savings.</FONT></P>

<P><FONT SIZE=3D2>Alex Hillman</FONT>
</P>

<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Murali Vallath [<A =
HREF=3D"mailto:murali_vallath_at_hotmail.com">mailto:murali_vallath_at_hotmail=
.com</A>]</FONT>
<BR><FONT SIZE=3D2>Sent: Friday, December 15, 2000 10:31 AM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: Re: IOT Vs Normal Tables</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>Jared,</FONT>
</P>

<P><FONT SIZE=3D2>Since the index and data are stored together, my =
impression on this subject </FONT>
<BR><FONT SIZE=3D2>was IOT's will save you the extra I/O for the data =
lookup.</FONT>
</P>

<P><FONT SIZE=3D2>With the B-TREE index structure being common, the =
savings should be in the </FONT>
<BR><FONT SIZE=3D2>data lookup.</FONT>
</P>

<P><FONT SIZE=3D2>Also, from the Oracle Internals class I had the =
opportunity to attend in </FONT>
<BR><FONT SIZE=3D2>October, at Hustone TX, the instructor told us that =
with 9i the data </FONT>
<BR><FONT SIZE=3D2>dictionary will be using IOT.</FONT>
</P>

<P><FONT SIZE=3D2>I have not tested it myself, but these are from =
training, conversations and </FONT>
<BR><FONT SIZE=3D2>reading knowledge.</FONT>
</P>

<P><FONT SIZE=3D2>Murali Vallath</FONT>
<BR><FONT SIZE=3D2>Oracle Certified DBA</FONT>
<BR><FONT SIZE=3D2><A HREF=3D"http://www8.ewebcity.com/muralivallath" =
TARGET=3D"_blank">http://www8.ewebcity.com/muralivallath</A></FONT>
<BR><FONT SIZE=3D2><A HREF=3D"http://www.summerksyus.com/" =
TARGET=3D"_blank">http://www.summerksyus.com/</A></FONT>
</P>

<P><FONT SIZE=3D2>Reply-To: ORACLE-L_at_fatcity.com</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L =
&lt;ORACLE-L_at_fatcity.com&gt;</FONT>
<BR><FONT SIZE=3D2>Date: Wed, 13 Dec 2000 20:35:31 -0800</FONT>
</P>

<P><FONT SIZE=3D2>On Wed, 13 Dec 2000, Sanjay Kumar wrote:</FONT>
</P>

<P><FONT SIZE=3D2>&nbsp;&gt; Hi,</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; Can anyone tell me how to see the =
performance differences of using IOTs </FONT>
<BR><FONT SIZE=3D2>over the normal tables.</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt; Sanjay</FONT>
<BR><FONT SIZE=3D2>&nbsp;&gt;</FONT>
</P>

<P><FONT SIZE=3D2>I don't think you will see any performance =
increase</FONT>
<BR><FONT SIZE=3D2>from an IOT.</FONT>
</P>

<P><FONT SIZE=3D2>You can get the same performance if your query =
can</FONT>
<BR><FONT SIZE=3D2>be satisfied by an index without reading the =
table.</FONT>
<BR><FONT SIZE=3D2>i.e. the index has all the columns to satisfy the =
query.</FONT>
</P>

<P><FONT SIZE=3D2>IOT's just save space and complicate your life. =
:)</FONT>
</P>

<P><FONT SIZE=3D2>Jared Still</FONT>
<BR><FONT SIZE=3D2>Certified Oracle DBA and Part Time Perl Evangelist =
;)</FONT>
<BR><FONT SIZE=3D2>jkstill_at_teleport.com</FONT>
<BR><FONT SIZE=3D2>jared_still_at_enron.net</FONT>
</P>

<P><FONT SIZE=3D2>--</FONT>
<BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A =
HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A></FONT>
<BR><FONT SIZE=3D2>--</FONT>
<BR><FONT SIZE=3D2>Author: jared still</FONT>
<BR><FONT SIZE=3D2>&nbsp;&nbsp; INET: jkstill_at_teleport.com</FONT>
</P>

<P><FONT SIZE=3D2>Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) =
538-5051&nbsp; FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an =
E-Mail message</FONT>
<BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of =
'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB =
ORACLE-L</FONT>
<BR><FONT SIZE=3D2>(or the name of mailing list you want to be removed =
from).&nbsp; You may</FONT>
<BR><FONT SIZE=3D2>also send the HELP command for other information =
(like subscribing).</FONT>
</P>

<P><FONT =
SIZE=3D2>_______________________________________________________________=
__</FONT>
<BR><FONT SIZE=3D2>Get your FREE download of MSN Explorer at <A =
HREF=3D"http://explorer.msn.com" =
TARGET=3D"_blank">http://explorer.msn.com</A></FONT>
</P>

<P><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A =
HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A></FONT>
<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: Murali Vallath</FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: murali_vallath_at_hotmail.com</FONT>
</P>

<P><FONT SIZE=3D2>Fat City Network Services&nbsp;&nbsp;&nbsp; -- (858) =
538-5051&nbsp; FAX: (858) 538-5051</FONT>
<BR><FONT SIZE=3D2>San Diego, =
California&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------------------------------=
-----</FONT>
<BR><FONT SIZE=3D2>To REMOVE yourself from this mailing list, send an =
E-Mail message</FONT>
<BR><FONT SIZE=3D2>to: ListGuru_at_fatcity.com (note EXACT spelling of =
'ListGuru') and in</FONT>
<BR><FONT SIZE=3D2>the message BODY, include a line containing: UNSUB =
ORACLE-L</FONT>

Received on Fri Dec 15 2000 - 11:53:35 CST