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.
Sanjay
________________________________
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
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@xxxxxxxxx]
>Sent: Friday, October 30, 2009 10:04 AM
>To: oracle-l@xxxxxxxxxxxxx
>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
>
Other related posts:
