| Help deciding on Local or Global Index [message #199687] |
Wed, 25 October 2006 19:56  |
mdoakes42
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.
v_TRANSACTION_ID VARCHAR --primary key
V_DATE DATE
......
......
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
