Home » RDBMS Server » Performance Tuning » Help Deciding on Local or Global Index
Help Deciding on Local or Global Index [message #199692] Wed, 25 October 2006 20:56 Go to next message
Messages: 5
Registered: May 2006
Junior Member
am running Oracle 10gR2.

I have a few hundred million records partitioned on a date range.

For argument sake my table looks like this.


I have the table partitioned on V_DATE with about 10 million records in each partition. I have over 200 partitions.

I have a business requirement to update a single record using the primary key. Although most of the queries are time constrainted, the update IS NOT. I originally used a local index for the primary key but performance seemed extremely slow. (I had to add the v_DATE field as part of the index as the DBMS required it). I'm working on creating a global index but it looks like it may take a while. I've also read about prefixed and non-prefixed but wasn't able to understand how this works.

We also have a few other requirements where we may want to move some partitions (tablespaces) to read-only, and possibly compress some of them. Also down the road we may actually drop some of the partitions. So considering all of this I don't want to have to rebuild the index if these events occur.

Considering this environment, I'm wondering what type of index would be best (local, global, or maybe global partitioned, etc..)

Any advice would be appreciate. Thanks
Re: Help Deciding on Local or Global Index [message #199702 is a reply to message #199692] Wed, 25 October 2006 21:36 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you don't want to perform index rebuilds when you drop partitions, then you CANNOT have global indexes.

If you can come to terms with index rebuild, Global index is the way to go for PKs and UKs that do not include the partition column.

If you access data regularly via the key, then such a SQL would return a single row. You don't want to have to go searching for that row in 200 index partitions (as you've discovered).

Prefixing is just adding the partition column to the index - this is what you've already done. It helps maintainability, but not performance, because you still have to check all 200 partitions to find a single row by the PK.

My advice, global index rebuilds are a fact of life for such tables - get used to it.

Note that I don't typically apply the same logic to non-unique indexes or indexes used primarily for range scans. The results of such SQLs could easily span many partitons (as opposed to a unique lookup, where the result is guaranteed to be in one partition only), so there may not actually be much wasted effort.

Ross Leishman
Previous Topic: Statspack: How to reduce the LNS wait for LGWR redo EVENT and CPU TIME
Next Topic: Regarding v$librarycache
Goto Forum:

Current Time: Wed Jan 18 12:46:38 CST 2017

Total time taken to generate the page: 0.15133 seconds