How to Optimize or change an update of global indexes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: How to Optimize or change an update of global indexes

  1. #1
    Join Date
    Aug 2007
    Posts
    2

    How to Optimize or change an update of global indexes

    Hi,

    Is there a way to change or optimize updating of global indexes in Oracle 9i.

    I have a DDL statement which executes as a part of batch job for 2-3 hours.
    How do I change this to work better? The table_1 has partition and indexes on it.

    The DDL statement is:

    Code:
    ALTER TABLE table_1 TRUNCATE PARTITION partition_name UPDATE GLOBAL INDEXES
    Thanks.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Time comsumption for UPDATE GLOBAL INDEXES can be correlated to a) Size of the truncated partition and, b) Quantity of Global Indexes.

    Smaller partitions will result in a faster process.
    Less global indexes will result in a faster process.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Aug 2007
    Posts
    2
    a) Size of truncated partition - Table has 1 column with 2 partitions - One partition has 12 million rows and second has 8 million rows.

    b) Quantity of Global Indexes - 13.

    Clearly, the size of truncated partition is high enough for performance draining. Is there a way I can optimize this by increasing the number of partitions? Any alternative method?

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Your analysis is correct you can either

    1- Increase the number of partitions and perform smaller purges
    I.e.: I don't know your partitioning strategy but, imagine your current partitoning strategy is by "month". If you can partition your table by "week" you will end doing four purge processes instead of one -for the same amount of data- but, these smaller purge processes will work faster.

    2- You might want to analyze if some of your Global Indexes can be partitioned alognside with the table.
    For each global index you bring down you are going to get immediate performance improvement during purging process.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

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



Click Here to Expand Forum to Full Width