Results 1 to 4 of 4

Thread: partition index

  1. #1
    Join Date
    Dec 2011
    Posts
    30

    Unanswered: partition index

    What are advantage and disadvantage of having partition and nonpartition index on the same table?

    How to know which Particular partition needs reorg?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If there are any non-partitioned (global) indexes on a partitioned table, when the partition is detached (such as would happen to remove the data in that partition) the process is not immediate and DB2 will need to initiate a background task to clean up the Global indexes (remove those index entries pertaining to the partition being removed). This can be a performance issue to delete all these index rows, and since it is a background task and control returns immediately when the command is submitted, even though not completed, it will not be known as to when the detached partition is available as a side table (to drop or whatever). If you are detaching a lot partitions at one time that all have global indexes, you don't want too many of these background tasks running at the same time (again, similar to background task of online reorg).

    The advantage of a global index is that it may have some performance advantages in certain cases, especially if there are a large number of partitions and DB2 cannot use partition elimination to narrow down the search.
    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
    Dec 2011
    Posts
    30
    Thanks for your reply

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Since 9.7 you have the ability to perform a offline reorg of a single data-partition. When all your indexes are partitioned this is very fast & efficient. In practice you will have at least 1 global index (primary key) and this will slow down the offline data-part-reorg.
    Same goes for attach & detach operations. Very fast when you have partitioned indexes only .
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

Posting Permissions

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