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: 21148
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Don't cross-post. Ross has already answered your question here.
Previous Topic: Errors in emConfig.log file due to port problem
Next Topic: renaming the shchema name while exporting
Goto Forum:

Current Time: Tue Aug 22 19:47:55 CDT 2017

Total time taken to generate the page: 0.05184 seconds