RE: Index Organized tables: Please explain use?

From: Gillies, Garry <garry_at_weir.co.uk>
Date: Fri, 10 Nov 2000 14:34:27 -0000
Message-Id: <10676.121707@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_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_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_001_01C04B23.546E0990
Content-Type: text/html;

charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2651.75">
<TITLE>RE: Index Organized tables: Please explain use?</TITLE>
</HEAD>
<BODY>

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 [<A HREF="mailto:Hannah.M.Doran_at_sb.com">mailto:Hannah.M.Doran_at_sb.com</A>]


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


<PRE>++++++++++++++++++++++++++++++++++++
All internet traffic to this site is
automatically scanned for viruses Received on Fri Nov 10 2000 - 08:34:27 CST