Home » RDBMS Server » Server Administration » Help deciding on Local or Global Index
Help deciding on Local or Global Index [message #199687] Wed, 25 October 2006 19:56 Go to next message
Messages: 5
Registered: May 2006
Junior Member
I 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 #199718 is a reply to message #199687] Thu, 26 October 2006 00:25 Go to previous message
Messages: 20850
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Don't cross-post. Ross has already answered your question here.
Previous Topic: Select systime with timezone
Next Topic: Creating New database
Goto Forum:

Current Time: Sat Oct 22 21:21:38 CDT 2016

Total time taken to generate the page: 0.15460 seconds