Re: Global Index on Partition Table
- From: "Tim Gorman" <tim@xxxxxxxxx>
- To: smishra_97@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Fri, 30 Oct 2009 17:00:47 +0000
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:
