RE: Index Organized tables: Please explain use?

From: Richard Ji <richard_at_letsplay.com>
Date: Fri, 10 Nov 2000 10:17:51 -0500
Message-Id: <10676.121718@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_0024_01C04AFF.8438E150 Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Garry

IMHO, the order of data in the table is irrelevant from the user perspective, but not to the DBMS itself. IOT is not meant to replace table. It there for a few very speical situation where it can be very useful especially in data warehouse, and that's why Oracle include it there.

Richard Ji

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Gillies, Garry Sent: Friday, November 10, 2000 9:36 AM
To: Multiple recipients of list ORACLE-L Subject: RE: Index Organized tables: Please explain use?

Hi Hannah,
Think of a standard old fashioned Oracle table with an old fashioned Oracle index.
If a select is done on the table which references only the keys of the index,
then there is no need for a table lookup - all the data is available in the index.

Suppose you have a query which can get all of its fields from an index except one.
Might it be worth while to include that field in the index to save the table lookup?

Index entries consist of key data and rowids. Would it be so difficult to slip in some
extra, non key, data as well? Apparently not.

Carried to its illogical conclusion, you get an index which contains all of a tables
data, or looking at it from the other side, a table structured like an index.

This sort of structure was common on the old mainframe codasyl databases of yesteryear.

Its inclusion in a relational database is a mystery to me, since a fundamental tenet
is that the order of data in a table is irrelevant.

Personally, I intend to avoid them as far as possible.

Regards

Garry

-----Original Message-----
From: Hannah.M.Doran_at_sb.com [ mailto:Hannah.M.Doran_at_sb.com]

Hello List!

I'm having trouble comprehending the idea of an Index Organized table. The
Oracle 8i DBA Bible has this paragraph in it:

An index-organized table is one in which the entire table is created as an
index. All the data is stored in the index, and there

really is no underlying table. Oracle 8i allows secondary indexes to be
created on these tables, allows them to store large

objects, and allows you to add or modify columns using the ALTER TABLE command.

This is all they wrote. But I dont understand how/why one would store data in
the actual index. And if you create an index, don't you need to create it on a
specific table? And if there is NO table, then HOW could you create and index
on it. And why would you do it even if you could?

Thanks in advance,

Hannah

++++++++++++++++++++++++++++++++++++

All internet traffic to this site is
automatically scanned for viruses
and vandals.

++++++++++++++++++++++++++++++++++++

------=_NextPart_000_0024_01C04AFF.8438E150 Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =

charset=3Diso-8859-1">
<TITLE>RE: Index Organized tables: Please explain use?</TITLE>

<META content=3D"MSHTML 5.00.3103.1000" name=3DGENERATOR></HEAD>
<BODY>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><FONT=20

size=3D2>Garry</DIV>
<DIV> </DIV>
<DIV>IMHO,=20
the order of data in the table is irrelevant from the user perspective, = but not=20
to the DBMS itself.  IOT is not meant to replace table.  It = there for=20
a few very speical situation where it can be very useful especially in = data=20
warehouse, and that's why Oracle include it there.</DIV> <DIV><SPAN=20 class=3D443551315-10112000> </DIV> <DIV><SPAN=20 class=3D443551315-10112000>Richard Ji</DIV> <BLOCKQUOTE style=3D"MARGIN-RIGHT: 0px">
<DIV align=3Dleft class=3DOutlookMessageHeader dir=3Dltr>-----Original Message----- From: root_at_fatcity.com=20 [mailto:root_at_fatcity.com]On Behalf Of Gillies, = Garry Sent:=20
 Friday, November 10, 2000 9:36 AM To: Multiple recipients of = list=20
 ORACLE-L Subject: RE: Index Organized tables: Please explain =

use? </DIV>
Hi Hannah, Think of a =
standard old=20
 fashioned Oracle table with an old fashioned Oracle index. = <FONT=20
 size=3D2>If a select is done on the table which references only the = keys of the=20
 index, then there is no need for a table = lookup - all=20
 the data is available in the index. 
Suppose you have a query which can get all of its =
fields from=20
 an index except one. Might it be worth while = to=20
 include that field in the index to save the table lookup? 
Index entries consist of key data and rowids. Would =
it be so=20
 difficult to slip in some extra, non key, = data as=20
 well? Apparently not. 
Carried to its illogical conclusion, you get an =
index which=20
 contains all of a tables data, or looking at = it from=20
 the other side, a table structured like an index. 
This sort of structure was common on the old =
mainframe codasyl=20
 databases of yesteryear. 
Its inclusion in a relational database is a mystery =
to me,=20
 since a fundamental tenet is that the order = of data in=20
 a table is irrelevant. 
Personally, I intend to avoid them as far as =
possible.=20

Regards 
Garry 
-----Original Message----- From:=20
 Hannah.M.Doran_at_sb.com [<A=20
 =
href=3D"mailto:Hannah.M.Doran_at_sb.com">mailto:Hannah.M.Doran_at_sb.com</A>]</= FONT>=20

Hello List! 
     I'm having trouble =
comprehending the=20
 idea of an Index Organized table.  The Oracle 8i=20
 DBA Bible has this paragraph in it: 
     An index-organized table is =
one in=20
 which the entire table is created as an index. =20
 All the data is stored in the index, and there <FONT=20 size=3D2>     really is no underlying table.  = Oracle 8i=20
 allows secondary indexes to be created on = these=20
 tables, allows them to store large <FONT=20 size=3D2>     objects,  and allows you to add = or modify=20
 columns using the ALTER TABLE command. 
This is all they wrote.  But I dont understand =
how/why=20
 one would store data in the actual = index.  And if=20
 you create an index,  don't you need to create it on a = <FONT=20
 size=3D2>specific table?  And if there is NO table, then HOW = could you=20
 create and index on it.  And why would = you do it=20
 even if you could? 
Thanks in advance, 
     Hannah <FONT =
color=3D#3db00e=20

  size=3D3><PRE>++++++++++++++++++++++++++++++++++++

All internet traffic to this site is=20 Received on Fri Nov 10 2000 - 09:17:51 CST