Re: Global Index on Partition Table

  • From: Sanjay Mishra <smishra_97@xxxxxxxxx>
  • To: tim@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 30 Oct 2009 10:17:35 -0700 (PDT)

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.


From: Tim Gorman <tim@xxxxxxxxx>
To: smishra_97@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Sent: Fri, October 30, 2009 1:00:47 PM
Subject: Re: Global Index on Partition Table

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 
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 
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...
-----Original Message-----
>From: Sanjay Mishra [mailto:smishra_97@xxxxxxxxx]
>Sent: Friday, October 30, 2009 10:04 AM
>To: oracle-l@xxxxxxxxxxxxx
>Subject: Global Index on Partition Table
>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 
>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.


Other related posts: