RE: Index Organized tables: Please explain use?

From: Glenn Travis <Glenn.Travis_at_wcom.com>
Date: Fri, 10 Nov 2000 13:21:48 -0500
Message-Id: <10676.121760@fatcity.com>

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

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

--Boundary_(ID_IdBRm+FDj5Sigt+wZxbJcQ)
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><TITLE>RE: Index Organized tables: Please explain = use?</TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1">

<META content=3D"MSHTML 5.50.4134.600" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D60062118-10112000><FONT face=3DArial color=3D#0000ff =

size=3D2>Ingres=20
has indexes which let you carry data as well.  Very nice=20 feature.</DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px = solid; MARGIN-RIGHT: 0px">
<DIV class=3DOutlookMessageHeader>-----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 - 12:21:48 CST