Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    15

    Unanswered: Creating Index after reorg; Index optimal?

    Hi, if you create an index after a table reorg:

    REORG TABLE T1;
    CREATE INDEX I_T1 ON T1 (C1, C2, C3);

    Is it safe to assume that the index is optimal? (as defined in DB2 Universal Database)

    I would assume the answer to be yes, but are there any additional REORG parameters or other commands that would ensure the table (plus all indexes) is in an optimal state at the end of the above sql command sequence?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Yes, the index will be in optimal state right after you create it. That would be the same as if you reorged the table (which reorgs all the indexes automatically unless it is an "inplace" reorg). A table reorg actually rebuilds the index from scratch, just like a create index statement.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2004
    Posts
    15
    Interesting, thanks for the insight about reorg rebuilding indexes. I'd assumed (incorrectly) that REORG on a table would not REORG the indexes, but know now its not the case (unless its an inplace REORG as you state).

  4. #4
    Join Date
    Oct 2007
    Posts
    246
    i m not able to get
    inplace is used to reorg online
    and reorg table tabname , is classic way which reorgs the table and indexes
    means rebuild the indexes
    --
    as per marcus when we use inplace command it reorgs only the table and not the indexes ?? , kindly correct me on this
    redgs
    paul

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Mathew_paul
    ..when we use inplace command it reorgs only the table and not the indexes ?? , kindly correct me on this
    redgs
    paul
    That is correct. The indexes obviously get updated with the new RIDs of the rows (which have been moved via the reorg of the table), but the index itself does not get completely reorged.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Is the index optimal? What about if you are creating this new index or recreating old one and it is defined as CLUSTER? Then the table and index are not optimal.

    Dave Nance

Posting Permissions

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