| Help Deciding on Local or Global Index [message #199692] |
Wed, 25 October 2006 20:56  |
mdoakes42
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.
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
|
|
|
|
| Re: Help Deciding on Local or Global Index [message #199702 is a reply to message #199692] |
Wed, 25 October 2006 21:36  |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
