Re: Global Index on Partition Table

Thanks Tim for so many options and queries. The application is working from several years and recently Standby is created. Earlier nobody used the GLoabl Update syntax
and are doing the rebuild due to Unusable state of the index but using NOLOGGING operation. Anyway Rebuild with or Without LOGGING is still going to take time. With Update INDEX., time is several Times lowered based on the indexes and it is not minimal as even I am saying WITHOUT VALIDATION, it will still do the VALIATION for UNIQUE indexes as per the Oracle Doc.

Ofcourse design is not clear and I can change some of them to LOCAL partitioned index but UNIQUE cannot be changed and required composite key used in the index has to be part of the Partition Key which can affect and require more task to be passed to Development. Data is loaded daily but query is mostly done to last 3 month data and that might be the reason they are quarterly index. 

Changing them to LOCAL ( One which can be changed except UNIQUE) is surely a point refered to Development so that they can check the application  to make sure it is not affecting them. Also it will surely help in Maintenance when we have to work with 2 Gloabl Index instead of 5.

I will surely go thru your other good points.

Sanjay


________________________________
From: Tim Gorman <tim_at_evdbt.com>
To: smishra_97_at_yahoo.com; oracle-l_at_freelists.org
Sent: Fri, October 30, 2009 1:00:47 PM
Subject: Re: Global Index on Partition Table


Sanjay,
 
So many questions...
 
1. Why quarterly partitions?  Do most of your queries fetch data by quarter?  Are data loads quarterly?
2. What is the reason for *five* global indexes?  Is it to maintain the proper definition to enforce uniqueness?  Or is it to support certain queries for which local indexes have proven insufficient?
    2a. If the former reason, are you certain about the requirement for uniqueness absolutely being enforced by an index?
    2b. If the latter reason, are you certain that a global index is absolutely the best way to achieve the desired performance?
         Is it possible that SQL statements might benefit from finer granularity on the partitioning, assuming that the
         partition-key column(s) are referenced at all in the WHERE clause of many queries...
               2b2. If the response to the question above is "no, partition-key columns aren't referenced", then again
                      the appropriate question is "why not"?
 
But enough of the questions about the foundations of the design, which by themselves could render most of your questions moot...  :-)
 
"Validation" in this context refers only to the ALTER TABLE command validating whether or not each row belongs in the partition according to the definition of the ranges.  For example, if you wanted, you could put rows into a table, then exchange it into a partitioned table using EXCHANGE PARTITION.  If you used WITHOUT VALIDATION in the ALTER TABLE ... EXCHANGE PARTITION statement, then any rows that shouldn't be in the partition could now reside within the partition as a result of the partition exchange.  This is a form of data corruption, amply documented, because operations that perform partition pruning will miss these out-of-place rows, while operations not performing partition pruning will fetch them.  So, I strongly suggest you use WITH VALIDATION until you are confident of what this load processing is doing.  Your email makes it appear that you are not clear on the meaning of VALIDATION.
 
The [ INVALIDATE | UPDATE ] GLOBAL INDEXES clause does just what the name implies, where INVALIDATE GLOBAL INDEXES is the default.  If you leave the default, it will be necessary to rebuild your global indexes completely.  If you choose to maintain (or UPDATE) your global indexes during the ALTER TABLE command, then the ALTER TABLE will run longer but the global indexes will remain fully available throughout, not requiring a rebuild.  You can specify a PARALLEL clause as well to try to make the UPDATE GLOBAL INDEXES run faster, if desired.
 
However, as the old saying goes, the fastest operation is one that never happens.  If global indexes are proving expensive and difficult to maintain, that is because they are.  Go back and question the assumptions that led to the creation of so many global indexes, and see if there are alternatives.  In particular, push hard on the idea of UNIQUE indexes, as there are other ways to enforce uniqueness that, while low-tech, are just as effective.  And push just as hard on the assertion that global indexes are needed for query performance, as I am suspicious that the partitioning granularity (i.e. quarterly) is so large as to be useless for improving SQL performance via partition pruning or improving data loads using partition exchange.
 
Hope this helps...
 
-Tim
 
 
-----Original Message-----
>From: Sanjay Mishra [mailto:smishra_97_at_yahoo.com]
>Sent: Friday, October 30, 2009 10:04 AM
>To: oracle-l_at_freelists.org
>Subject: Global Index on Partition Table
>
>
>Hi
>
>I had 2 Tb table with Quarterly Partition and has 5 Global index. When we are doing quarterly maintenance and exchanging the partition  we are using the Clause
>
>WITHOUT VALIDATION UPDATE GLOBAL INDEXES
>
>As two of them are Unique index and so I think VALIDATION is anyway done. Question is that how it impact the Indexes ? Do I really need to Rebuild the index in the future or it is fine. 
>
>Actually Earlier partition maintenance are not done with above clause leaving Index in Unusable state but adding the above will avoid this and save downtime which is big due to several big indexes as well as DB in LOGGING mode due to Standby database.
>
>TIA
>Sanjay
> 


      

--
http://www.freelists.org/webpage/oracle-l