Re: Index-Organized Table experiences
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 28 Apr 2004 22:11:04 +0100
There doesn't seem to be any real point
in using IOTs for your scenario. For an
intersection table that small, the saving in
space is likely to be non-existent. You may
as well have:
create table intersection(
proj_id typeX,
emp_id typeX,
constraint int_pk primary key (proj_id, emp_id)
);
create unique index int_uk on intersection(emp_id, proj_id);
Given the space overheads in indexing and IOTs in particular,
(even allowing for the documented reduced size of secondary
indexes in 10g), I doubt if the table plus two indexes will take
up much more space than the IOT plus secondary. (In fact,
in my test cast with, the t + 2i used a little less space than
the IOT + s).
Since secondaries have had a revamp in 10g, I'd be cautious
about assuming that they would do all the right things.
Since you only need to access an index to traverse
between the employee and project tables, irrespective
of direction, the table would only get into memory when
you were inserting or deleting rows.
Stick with the traditional technology when the new technology
has no apparent added value.
One minor detail with IOTs - if two users try to update
the same row, the second user's TX wait will be mode 4,
rather than mode 6.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
April 2004 Iceland http://www.index.is/oracleday.php
June 2004 UK - Optimising Oracle Seminar
July 2004 USA West Coast, Optimising Oracle Seminar
August 2004 Charlotte NC, Optimising Oracle Seminar
September 2004 USA East Coast, Optimising Oracle Seminar
September2004 UK - Optimising Oracle Seminar
----- Original Message -----
From: "Daniel Fink" <Daniel.Fink@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, April 28, 2004 3:14 PM
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.
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
-----------------------------------------------------------------
Other related posts:
