Message-Id: <10676.121707@fatcity.com> From: "Gillies, Garry" Date: Fri, 10 Nov 2000 14:34:27 -0000 Subject: RE: Index Organized tables: Please explain use? 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_01C04B23.546E0990 Content-Type: text/plain; charset="iso-8859-1" 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@sb.com [mailto:Hannah.M.Doran@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_001_01C04B23.546E0990 Content-Type: text/html; charset="iso-8859-1" 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@sb.com [mailto:Hannah.M.Doran@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