Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Location
    France
    Posts
    754

    Unanswered: What about index-organized tables ?

    Hello,

    I've been looking at the Oracle doc concerning index-organized tables. It looks like they offer great advantages : less disk space, great perf for DML and selects using the primary key (they say so in the doc, but I also did some tests which were positively surprising), as well as great availability (since secondary indexes are still available during maintenance operations) at almost no cost. So my (double) question is : are there major drawbacks not stated in the doc, and is this type of table available in the standard version of Oracle, or only in the Enterprise version ?

    Thanks & Regards,

    RBARAER

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    IOTs are available in both versions. The only significant disadvantage is if you have to access them other than using the primary key.

    Alan

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Thanks Alan, I had come to that conclusion (secondary indexes are slower because of the use of logical rowid then physical rowid, instead of direct physical rowid).

    BTW, I asked you a new question in my "Very slow deletes" thread, but you haven't answered yet.

    Regards,

    RBARAER

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Another drawback is that one cannot create a DOMAIN index (Oracle Text) on an IOT.

    Apart from these drawbacks, IOT can be very interesting : in tests I've done, they perform 4 times better than normal tables for inserts, and 3 times better for updates on a field part of the PK (tests on insert/update of 150000 records on a 11,5 million rows table, in the same conditions). Other updates as well as selects using the PK index also showed better results, while the overhead when using a secondary index doesn't seem to be that important (for now, I'd say max 10%, if any).

    RBARAER

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •