Message-Id: <10676.121760@fatcity.com> From: Glenn Travis Date: Fri, 10 Nov 2000 13:21:48 -0500 Subject: RE: Index Organized tables: Please explain use? This is a multi-part message in MIME format. --Boundary_(ID_IdBRm+FDj5Sigt+wZxbJcQ) Content-type: text/plain; charset=iso-8859-1 Content-transfer-encoding: 7bit RE: Index Organized tables: Please explain use?Ingres has indexes which let you carry data as well. Very nice feature. -----Original Message----- From: root@fatcity.com [mailto:root@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@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. ++++++++++++++++++++++++++++++++++++ --Boundary_(ID_IdBRm+FDj5Sigt+wZxbJcQ) Content-type: text/html; charset=iso-8859-1 Content-transfer-encoding: quoted-printable RE: Index Organized tables: Please explain = use?
Ingres=20 has indexes which let you carry data as well.  Very nice=20 feature.
-----Original Message-----
From: root@fatcity.com=20 [mailto:root@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?

Hi Hannah,
Think of a = standard old=20 fashioned Oracle table with an old fashioned Oracle index. =
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@sb.com [mailto:Hannah.M.Doran@sb.com]=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
     really is no underlying table.  = Oracle 8i=20 allows secondary indexes to be
created on = these=20 tables, allows them to store large
     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 =
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 

++++++++++++++++++++++++++++++++++++
All internet traffic to this site is=20