Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2005
    Posts
    5

    Unanswered: Index Organized Table in DB2 ?

    Dear all,

    Since I' m an Oracle DBA I am familiar with Index Organized Tables. (IOT)
    The table is in fact a btree structure , the leaf blocks/pages contain the data (whereas a "normal" btree index contains a rowid, a pointer to the actual table data)
    I thought this was available in DB2 as well, I think it was called btree tables.
    Can you confirm me this ?
    Any documentation links ?

    Regards
    Guy Lambregts

  2. #2
    Join Date
    Apr 2004
    Posts
    54
    There is no such tables in DB2 (or its analog).
    There is only ORGANIZE BY KEY SEQUENCE. But I doubt it appropriate for you.

  3. #3
    Join Date
    Nov 2004
    Posts
    374

    index

    and clustering index ? would that help..?
    This tries to keep the data that belongs together (organized by this index) on the same pages..
    Best Regards, Guy Przytula
    DB2/ORA/SQL Services
    DB2 DBA & Advanced DBA Certified
    DB2 Dprop Certified
    http://users.skynet.be/przytula/dbss.html

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    An index organized table is really just a index with all the columns included in it. You can do that in DB2, but obviously if there is a lot of inserting or deleting, then there is some extra overhead of inserting/deleting the actual row into the table in addtion to the index (but the index overhead is much higher because it must be in exact sequence). If all the columns are in an index, DB2 will almost always use index only access for select statements.

    Usually, defining a clustering index is the best solution, however the table will require reorgs on a periodic basis to maintain a reasonable clustered sequence if you have a lot of inserts. Careful coordination of percent free and reorg frequency is also advised.

    With a clustering index defined, DB2 tries to insert rows on the correct page, but never reorders rows on a page for the exact sequence the way an index is ordered. If there is no room on the correct page, DB2 will not split the page (like an index would) and just puts the row on a nearby page, or any page if no space is available on a nearby page.

    MDC (organize by) is a reasonable alternative, especially in a decision support environment. This is similar to table clustering in Oracle, but with only one table.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Aug 2005
    Posts
    5
    Hi all,

    Thanks for your valuable advice, I really appreciate it since my DB2 knowledge and experience is limited and since I' m involved these days in a DB2 database design.
    To be honest I have some DB2 deceptions :
    1. Oracle offers us these Index Organized Tables (IOT) the data is the index, the index is the data. I fully agree these tables are slower with regard to insert/update performance, but reduce logical/physical reads.
    2. Oracle offers us as well the multiple table index clusters, tablerows of multiple tables can be stored in the same datablock / page.
    3. Oracle offers us as well multiple table hash clusters, tablerows of multiple tables can be stored in the same datablock / page.
    4. The clustering index of which was spoken here above seems to be very close to the single table hash cluster, in Oracle we have as well the sorted hash cluster, seems also available in DB2.

    There are offcourse strong DB2 features as well.

    Regards

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    DB2 has several clustering solutions as well, which don't wxactly match the way Oracle does it. There are pros and cons of each method.

    If you post a specific example of a database design, someone will recommend the best DB2 clustering solution for you.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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