FAQ
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_at_freelists.org
put 'unsubscribe' in the subject line.

Search Discussions

  • Syed jaffar hussain at Apr 28, 2004 at 9:25 am
    I hope Mr. Jonathan can give you very good suggestions about your
    questions.

    Best Regards,

    Syed Jaffar Hussain
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Daniel Fink
    Sent: Wednesday, April 28, 2004 5:15 PM
    To: oracle-l_at_freelists.org
    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_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/

    FAQ is at http://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_at_freelists.org

    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Goulet, Dick at Apr 28, 2004 at 9:47 am
    Daniel,

    I've had IOT's around in production since 8.0.5 just for the very =
    reason you are looking at. We have some lookup type tables that have =
    two to three columns and all of the columns form the primary key. In =
    older versions of Oracle you'd have to have a table and a separate =
    index, except that the table never gets used. Therefore the IOT is the =
    perfect replacement, table and index in one. The only bad, that's been =
    fixed in 8i was that you could not add a column to an IOT. Now in 9i =
    they've added the ability to have multiple indexes on an IOT if needed, =
    haven't found a reason for that, yet.

    Dick Goulet
    Senior Oracle DBA
    Oracle Certified 8i DBA

    -----Original Message-----
    From: Daniel Fink
    Sent: Wednesday, April 28, 2004 10:15 AM
    To: oracle-l_at_freelists.org
    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_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/

    FAQ is at http://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_at_freelists.org

    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Igor Neyman at Apr 28, 2004 at 9:50 am
    I've been using IOTs, though not for intersection entities.
    I had a problem (in Oracle 8.1.5) with "on delete cascade" foreign key
    (IOT as a child table). Had to implement "on delete" trigger on parent
    table instead of foreign key.
    Don't know, if it's fixed in 10g.

    Igor Neyman, OCP DBA
    ineyman_at_perceptron.com

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Daniel Fink
    Sent: Wednesday, April 28, 2004 9:15 AM
    To: oracle-l_at_freelists.org
    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_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://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_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Juan Cachito Reyes Pacheco at Apr 28, 2004 at 9:58 am
    Hi, I had used IOTs, because several tables are accesed by primary key only, but because a bug in developer 6i forms, problems with database blocks
    I had to go back and undo all the work.

    Juan Carlos Reyes Pacheco
    OCP

    Database 9.2 Standard Edition

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
  • Rachel Carmichael at Apr 28, 2004 at 9:55 am
    85 million row table, key was coupon code and "used/unused" flag ....
    updates were done in batches as the coupons were processed. We did NOT
    have performance problems. We didn't have to worry about the FK index
    as it was a standalone table.

    No gotchas under the conditions we used it.

    I see scattered implementations of IOTs here and again, all I hear are
    good things about it (other than the "index" is in the "data"
    tablespace.... running and ducking now!)

    Daniel Fink wrote:
    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_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
    Do you Yahoo!?
    Win a $20,000 Career Makeover at Yahoo! HotJobs
    http://hotjobs.sweepstakes.yahoo.com/careermakeover

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Goulet, Dick at Apr 28, 2004 at 9:59 am
    Or that the data is in the index tablespace. Who really cares?

    Dick Goulet
    Senior Oracle DBA
    Oracle Certified 8i DBA

    -----Original Message-----
    From: Rachel Carmichael
    Sent: Wednesday, April 28, 2004 10:58 AM
    To: oracle-l_at_freelists.org
    Subject: Re: Index-Organized Table experiences

    85 million row table, key was coupon code and "used/unused" flag ....
    updates were done in batches as the coupons were processed. We did NOT
    have performance problems. We didn't have to worry about the FK index
    as it was a standalone table.

    No gotchas under the conditions we used it.=20

    I see scattered implementations of IOTs here and again, all I hear are
    good things about it (other than the "index" is in the "data"
    tablespace.... running and ducking now!)

    Daniel Fink wrote:
    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
    =20
    example:
    =20
    Employee (heap table)
    Project (heap table)
    =20
    There is a many-to-many relationship between the tables (1
    employee can be on many projects and 1 project can have many
    employees).
    =20
    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.
    =20
    This situation *sounds* like the right one for an IOT,
    otherwise we would have 1 table and 2 indexes (1 on each
    column).
    =20
    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
    =20
    Thanks,
    Daniel
    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
    =09

    =09

    Do you Yahoo!?
    Win a $20,000 Career Makeover at Yahoo! HotJobs =20
    http://hotjobs.sweepstakes.yahoo.com/careermakeover=20

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://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_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Rstevenson_at_cobblesoft.com at Apr 28, 2004 at 10:00 am
    Your gut instinct is spot on. IOT's are an excellent enhancement - one we have made considerable use of - both in OLTP and DW environments. The scenario you pose is the ideal situation in which to use an IOT - it negates the overhead of scanning both table and index to get the required rows - with consequent performance improvements.

    In addition to usage as per your scenario, we also use them extensively throughout our metadata repositories, especially for simple "lookup" data.

    The one thing to watch out for is "overflow" - which maybe a real DBA can explain better in response than I can (I'm an architect that knows enough DBA stuff to be dangerous and that's as far as it goes!).

    We're using 9i2 primarily, which allows us to place additional indices on IOTs - mostly for referential integrity. However, we don't tend to add additional indices unless we really need to - I figure it's a performance hit ???

    Note also that they do require the use of a primary key... I think this is still true - it's been a while ....

    Regards,
    Richard J Stevenson
    CobbleSoft International Ltd.
    "Helpdesk and Service Management Tools for a Real Database"
    www.cobblesoft.com
    US/Can Toll-Free: 1-866-380-6716
    International: +1 315 548 5810
    On Wed, 28 Apr 2004 08:14:50 -0600, Daniel Fink wrote:

    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_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://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_at_freelists.org
    put 'unsubscribe' in the subject line.

    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Michael McMullen at Apr 28, 2004 at 11:01 am
    Aren't inserts slower with an IOT and more suited to skinny tables? Haven't
    worked with them myself just what I've read.

    Mike
    ganstadba_at_hotmail.com

    Please see the official ORACLE-L FAQ: http://www.orafaq.com

    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
  • Leslie Tierstein at Apr 28, 2004 at 10:49 am
    Performance, etc, was excellent. The only problem was with using the Oracle
    development tools (Designer and Developer) with the IOT's. The tools didn't
    differentiate between an IOT and a "regular" table, and wanted to generate
    code that used ROWID's; IOT's don't have ROWID's.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Daniel Fink
    Sent: Wednesday, April 28, 2004 10:15 AM
    To: oracle-l_at_freelists.org
    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_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://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_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Khedr, Waleed at Apr 28, 2004 at 10:58 am
    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
    Sent: Wednesday, April 28, 2004 10:15 AM
    To: oracle-l_at_freelists.org
    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_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/

    FAQ is at http://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_at_freelists.org

    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
  • Stephane Faroult at Apr 28, 2004 at 1:59 pm

    Daniel Fink wrote:

    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
    Dan,

    I have mostly had disappointing experiences with IOTs. I especially
    remember a case where one of the tables (around 3 million rows) looked
    like the perfect case-study for IOTs. First tests were rather
    encouraging, fewer LIOs, etc. However, the cruel reality of timing
    contradicted the stats. The application was a little weird (investment
    book valuation, biggest table involved 15 million rows, thankfully
    partitioned, reports of death concurrently run by two scores of users).
    What we noticed with IOTs is that the initially good throughput was
    diminishing over time. An indiscreet peek at V$BH revealed that the IOT
    was taking more and more place into the SGA, letting fewer and fewer
    space to the rest of the data.
    My feeling is that IOTs, being first and foremost indices, tend to be a
    bit 'sticky' in memory and are bad memory-mates of tables (or
    partitions) which are scanned.

    --
    Regards,

    Stephane Faroult
    Oriole Software
    ----------------------------------------------------------------
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    ----------------------------------------------------------------
    To unsubscribe send email to: oracle-l-request_at_freelists.org
    put 'unsubscribe' in the subject line.
    --
    Archives are at http://www.freelists.org/archives/oracle-l/
    FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
    -----------------------------------------------------------------
  • Jonathan Lewis at Apr 28, 2004 at 4:08 pm
    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"
    To:
    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_at_freelists.org
    put 'unsubscribe' in the subject line.

Related Discussions

People

Translate

site design / logo © 2022 Grokbase