Results 1 to 4 of 4

Thread: IOTs

  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: IOTs

    In some of our programs that were converted to Oracle (from COBOL), cursor declarations lack an ORDER BY clause. This was because data was sorted in primary key order for tables on our WANG system.

    It got us looking into using Index Organized Tables to create the same effect. From reading the Oracle documentation, they seem to promote the concept - especially in mentioning the types of systems it would benefit (OLTP & Warehousing).

    How do you decide when to use an IOT vs a Heap table? The documentation lists no drawbacks when it comes to using an IOT, only benefits.

    -Chuck

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    IOT's may not return the data sorted by the key, you would still need the "Order By..." clause.

    Use IOT's when you have a key and few data columns (vg. lookup tables).



    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Why do you mention a table with few 'secondary' columns? Is it because of the overhead of inserting an entire row into the IOT indexing structure, as opposed to the insertion of a single value into the PK INDEX if using a heap table?

    -Chuck

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    The main drawback with IOT tables is if you dont access them using the primary key, i.e. use a secondry index then they are usually less efficient than a normal table.

    Alan

Posting Permissions

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