RE: Index Organized tables: Please explain use?

From: <Hannah.M.Doran_at_sb.com>
Date: Fri, 10 Nov 2000 10:00:46 -0500
Message-Id: <10676.121710@fatcity.com>

--0__=0XPadVmOANmlcEdVuWCRMBw0eUfAImHYs60MSW6qLT894tCvNm0H6DIb
Content-type: text/plain; charset=us-ascii Content-Disposition: inline

Good point. I was seeing the IOT as an *ordered/sorted* table. And from all of the responses, it seems to be the case. Basically, seems as if its just a table with a defined order which occurs on insert, update, or delete (like a b-tree). Only, I would think that if the table were LARGE, that there would be some perfomrance impact on arranging it this way. Aren't you basically re-indexing with each entry?

I can see using one for lookup tables only. Otherwise, I just dont see the use of it (or it may be that I have just not come across a use for it in my daily work).

Thanks,

Hannah

"Gillies, Garry" <garry_at_weir.co.uk> on 11/10/2000 09:35:35 AM

Please respond to ORACLE-L_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc:
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-----

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.

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

--0__=0XPadVmOANmlcEdVuWCRMBw0eUfAImHYs60MSW6qLT894tCvNm0H6DIb
Content-type: text/html;

name="att1.htm"

Content-Disposition: attachment; filename="att1.htm"
Content-transfer-encoding: base64
Content-Description: Internet HTML

PCFET0NUWVBFIEhUTUwgUFVCTElDICItLy9XM0MvL0RURCBIVE1MIDMuMi8vRU4iPg0KPEhUTUw+ DQo8SEVBRD4NCjxNRVRBIEhUVFAtRVFVSVY9IkNvbnRlbnQtVHlwZSIgQ09OVEVOVD0idGV4dC9o dG1sOyBjaGFyc2V0PWlzby04ODU5LTEiPg0KPE1FVEEgTkFNRT0iR2VuZXJhdG9yIiBDT05URU5U PSJNUyBFeGNoYW5nZSBTZXJ2ZXIgdmVyc2lvbiA1LjUuMjY1MS43NSI+DQo8VElUTEU+UkU6IElu ZGV4IE9yZ2FuaXplZCB0YWJsZXM6IFBsZWFzZSBleHBsYWluIHVzZT88L1RJVExFPg0KPC9IRUFE Pg0KPEJPRFk+DQoNCjxQPjxGT05UIFNJWkU9Mj5IaSBIYW5uYWgsPC9GT05UPg0KPEJSPjxGT05U IFNJWkU9Mj5UaGluayBvZiBhIHN0YW5kYXJkIG9sZCBmYXNoaW9uZWQgT3JhY2xlIHRhYmxlIHdp dGggYW4gb2xkIGZhc2hpb25lZCBPcmFjbGUgaW5kZXguPC9GT05UPg0KPEJSPjxGT05UIFNJWkU9 Mj5JZiBhIHNlbGVjdCBpcyBkb25lIG9uIHRoZSB0YWJsZSB3aGljaCByZWZlcmVuY2VzIG9ubHkg dGhlIGtleXMgb2YgdGhlIGluZGV4LDwvRk9OVD4NCjxCUj48Rk9OVCBTSVpFPTI+dGhlbiB0aGVy ZSBpcyBubyBuZWVkIGZvciBhIHRhYmxlIGxvb2t1cCAtIGFsbCB0aGUgZGF0YSBpcyBhdmFpbGFi bGUgaW4gdGhlIGluZGV4LjwvRk9OVD4NCjwvUD4NCg0KPFA+PEZPTlQgU0laRT0yPlN1cHBvc2Ug eW91IGhhdmUgYSBxdWVyeSB3aGljaCBjYW4gZ2V0IGFsbCBvZiBpdHMgZmllbGRzIGZyb20gYW4g aW5kZXggZXhjZXB0IG9uZS48L0ZPTlQ+DQo8QlI+PEZPTlQgU0laRT0yPk1pZ2h0IGl0IGJlIHdv cnRoIHdoaWxlIHRvIGluY2x1ZGUgdGhhdCBmaWVsZCBpbiB0aGUgaW5kZXggdG8gc2F2ZSB0aGUg dGFibGUgbG9va3VwPzwvRk9OVD4NCjwvUD4NCg0KPFA+PEZPTlQgU0laRT0yPkluZGV4IGVudHJp ZXMgY29uc2lzdCBvZiBrZXkgZGF0YSBhbmQgcm93aWRzLiBXb3VsZCBpdCBiZSBzbyBkaWZmaWN1 bHQgdG8gc2xpcCBpbiBzb21lPC9GT05UPg0KPEJSPjxGT05UIFNJWkU9Mj5leHRyYSwgbm9uIGtl eSwgZGF0YSBhcyB3ZWxsPyBBcHBhcmVudGx5IG5vdC48L0ZPTlQ+DQo8L1A+DQoNCjxQPjxGT05U IFNJWkU9Mj5DYXJyaWVkIHRvIGl0cyBpbGxvZ2ljYWwgY29uY2x1c2lvbiwgeW91IGdldCBhbiBp bmRleCB3aGljaCBjb250YWlucyBhbGwgb2YgYSB0YWJsZXM8L0ZPTlQ+DQo8QlI+PEZPTlQgU0la RT0yPmRhdGEsIG9yIGxvb2tpbmcgYXQgaXQgZnJvbSB0aGUgb3RoZXIgc2lkZSwgYSB0YWJsZSBz dHJ1Y3R1cmVkIGxpa2UgYW4gaW5kZXguPC9GT05UPg0KPC9QPg0KDQo8UD48Rk9OVCBTSVpFPTI+ VGhpcyBzb3J0IG9mIHN0cnVjdHVyZSB3YXMgY29tbW9uIG9uIHRoZSBvbGQgbWFpbmZyYW1lIGNv ZGFzeWwgZGF0YWJhc2VzIG9mIHllc3RlcnllYXIuPC9GT05UPg0KPC9QPg0KDQo8UD48Rk9OVCBT SVpFPTI+SXRzIGluY2x1c2lvbiBpbiBhIHJlbGF0aW9uYWwgZGF0YWJhc2UgaXMgYSBteXN0ZXJ5 IHRvIG1lLCBzaW5jZSBhIGZ1bmRhbWVudGFsIHRlbmV0PC9GT05UPg0KPEJSPjxGT05UIFNJWkU9 Mj5pcyB0aGF0IHRoZSBvcmRlciBvZiBkYXRhIGluIGEgdGFibGUgaXMgaXJyZWxldmFudC48L0ZP TlQ+DQo8L1A+DQoNCjxQPjxGT05UIFNJWkU9Mj5QZXJzb25hbGx5LCBJIGludGVuZCB0byBhdm9p ZCB0aGVtIGFzIGZhciBhcyBwb3NzaWJsZS48L0ZPTlQ+DQo8L1A+DQoNCjxQPjxGT05UIFNJWkU9 Mj5SZWdhcmRzPC9GT05UPg0KPC9QPg0KDQo8UD48Rk9OVCBTSVpFPTI+R2Fycnk8L0ZPTlQ+DQo8 L1A+DQoNCjxQPjxGT05UIFNJWkU9Mj4tLS0tLU9yaWdpbmFsIE1lc3NhZ2UtLS0tLTwvRk9OVD4N CjxCUj48Rk9OVCBTSVpFPTI+RnJvbTogSGFubmFoLk0uRG9yYW5Ac2IuY29tIFs8QSBIUkVGPSJt YWlsdG86SGFubmFoLk0uRG9yYW5Ac2IuY29tIj5tYWlsdG86SGFubmFoLk0uRG9yYW5Ac2IuY29t PC9BPl08L0ZPTlQ+DQo8L1A+DQoNCjxQPjxGT05UIFNJWkU9Mj5IZWxsbyBMaXN0ITwvRk9OVD4N CjwvUD4NCg0KPFA+PEZPTlQgU0laRT0yPiZuYnNwOyZuYnNwOyZuYnNwOyZuYnNwOyBJJ20gaGF2 aW5nIHRyb3VibGUgY29tcHJlaGVuZGluZyB0aGUgaWRlYSBvZiBhbiBJbmRleCBPcmdhbml6ZWQg dGFibGUuJm5ic3A7IFRoZTwvRk9OVD4NCjxCUj48Rk9OVCBTSVpFPTI+T3JhY2xlIDhpIERCQSBC aWJsZSBoYXMgdGhpcyBwYXJhZ3JhcGggaW4gaXQ6PC9GT05UPg0KPC9QPg0KDQo8UD48Rk9OVCBT SVpFPTI+Jm5ic3A7Jm5ic3A7Jm5ic3A7Jm5ic3A7IEFuIGluZGV4LW9yZ2FuaXplZCB0YWJsZSBp cyBvbmUgaW4gd2hpY2ggdGhlIGVudGlyZSB0YWJsZSBpcyBjcmVhdGVkIGFzIGFuPC9GT05UPg0K PEJSPjxGT05UIFNJWkU9Mj5pbmRleC4mbmJzcDsgQWxsIHRoZSBkYXRhIGlzIHN0b3JlZCBpbiB0 aGUgaW5kZXgsIGFuZCB0aGVyZTwvRk9OVD4NCjxCUj48Rk9OVCBTSVpFPTI+Jm5ic3A7Jm5ic3A7 Jm5ic3A7Jm5ic3A7IHJlYWxseSBpcyBubyB1bmRlcmx5aW5nIHRhYmxlLiZuYnNwOyBPcmFjbGUg OGkgYWxsb3dzIHNlY29uZGFyeSBpbmRleGVzIHRvIGJlPC9GT05UPg0KPEJSPjxGT05UIFNJWkU9 Mj5jcmVhdGVkIG9uIHRoZXNlIHRhYmxlcywgYWxsb3dzIHRoZW0gdG8gc3RvcmUgbGFyZ2U8L0ZP TlQ+DQo8QlI+PEZPTlQgU0laRT0yPiZuYnNwOyZuYnNwOyZuYnNwOyZuYnNwOyBvYmplY3RzLCZu YnNwOyBhbmQgYWxsb3dzIHlvdSB0byBhZGQgb3IgbW9kaWZ5IGNvbHVtbnMgdXNpbmcgdGhlIEFM VEVSIFRBQkxFPC9GT05UPg0KPEJSPjxGT05UIFNJWkU9Mj5jb21tYW5kLjwvRk9OVD4NCjwvUD4N Cg0KPFA+PEZPTlQgU0laRT0yPlRoaXMgaXMgYWxsIHRoZXkgd3JvdGUuJm5ic3A7IEJ1dCBJIGRv bnQgdW5kZXJzdGFuZCBob3cvd2h5IG9uZSB3b3VsZCBzdG9yZSBkYXRhIGluPC9GT05UPg0KPEJS PjxGT05UIFNJWkU9Mj50aGUgYWN0dWFsIGluZGV4LiZuYnNwOyBBbmQgaWYgeW91IGNyZWF0ZSBh biBpbmRleCwmbmJzcDsgZG9uJ3QgeW91IG5lZWQgdG8gY3JlYXRlIGl0IG9uIGE8L0ZPTlQ+DQo8 QlI+PEZPTlQgU0laRT0yPnNwZWNpZmljIHRhYmxlPyZuYnNwOyBBbmQgaWYgdGhlcmUgaXMgTk8g dGFibGUsIHRoZW4gSE9XIGNvdWxkIHlvdSBjcmVhdGUgYW5kIGluZGV4PC9GT05UPg0KPEJSPjxG T05UIFNJWkU9Mj5vbiBpdC4mbmJzcDsgQW5kIHdoeSB3b3VsZCB5b3UgZG8gaXQgZXZlbiBpZiB5 b3UgY291bGQ/PC9GT05UPg0KPC9QPg0KDQo8UD48Rk9OVCBTSVpFPTI+VGhhbmtzIGluIGFkdmFu Y2UsPC9GT05UPg0KPC9QPg0KDQo8UD48Rk9OVCBTSVpFPTI+Jm5ic3A7Jm5ic3A7Jm5ic3A7Jm5i c3A7IEhhbm5haDwvRk9OVD4NCjwvUD4NCg0KPEZPTlQgU0laRT0zRDMgQ09MT1I9M0RCTFVFPjxQ UkU+KysrKysrKysrKysrKysrKysrKysrKysrKysrKysrKysrKysrDQpBbGwgaW50ZXJuZXQgdHJh ZmZpYyB0byB0aGlzIHNpdGUgaXMgDQphdXRvbWF0aWNhbGx5IHNjYW5uZWQgZm9yIHZpcnVzZXMg DQphbmQgdmFuZGFscy4NCisrKysrKysrKysrKysrKysrKysrKysrKysrKysrKysrKysrKzwvUFJF Received on Fri Nov 10 2000 - 09:00:46 CST