RE: Index-Organized Table experiences
- From: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 28 Apr 2004 11:53:15 -0400
Main reason deciding to use IOT is avoiding an extra rowid access to the
table to fetch some other columns not part of the index and are
expensive to include them in the index.=20
In your case the table has two columns only that will be part of the
primary key.
So no real gain of using IOT except saving some disk space (no need for
the table segment) and removing some confusion for the optimizer.
On the other hand, direct loading the IOT will be slower than direct
loading a table and parallel building an index.
Hope it helps,
Waleed
-----Original Message-----
From: Daniel Fink [mailto:Daniel.Fink@xxxxxxx]
Sent: Wednesday, April 28, 2004 10:15 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Index-Organized Table experiences
We are looking to implement IOTs for a couple of intersection
entities in a 10g db. I would like to hear from those brave
enough to actually use IOTs what is the good, the bad and the
ugly.=20
example:
Employee (heap table)
Project (heap table)
There is a many-to-many relationship between the tables (1
employee can be on many projects and 1 project can have many
employees).
The emp_project table is the intersection entity containing
emp_id and project_id as the only columns. There are FK
constraints on each of the columns. The combination of emp_id
and project_id is unique.
This situation *sounds* like the right one for an IOT,
otherwise we would have 1 table and 2 indexes (1 on each
column).
My main concerns are:
1) Integrity/performance
2) Locking behavior (do I need to adhere to the traditional
"index all foreign keys" rule to prevent excessive locking?)
3) Any especially nasty gotchas
Thanks,
Daniel
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
