The Oracle (tm) Users' Co-Operative FAQ
Why does a global index on my partitioned table work faster than a local index ?
| Author's name: Jonathan Lewis Author's Email: Jonathan@jlcomp.demon.co.uk |
Date written: 1st Nov 2002 Oracle version(s): 8 - 9 |
| Partitioning is often touted as the cure-all for performance problems. "Partitioned your tables into 1,000 sections, and your queries run hundreds of times faster" is a comment I heard from one well-known "authority" in the early days of Oracle 8 - and some people still think it is true. So why, when you have partitioned your table, and start to index them, do you find that global indexes are quicker than local indexes. |
As usual, the quick (consultant's) answer is that the benefits depend on your system. How big is your data, what are your clustering factors like, are you using the features that supply the benefits, and in this case, how much work do you typical queries do anyway ?
The global/local indexes problem is generic - global indexes CAN be faster than local indexes. In general, local indexes will be faster when partition elimination can take place, and the expected volume of data acquired id significant. If either of this conditions is not met, then local indexes probably won't help performance.
Remember, the optimizer has to do some extra work to deal with partitioned tables. If the queries you use are very precise queries (e.g. on a nearly unique pair fo columns) then the cost of optimisation and identifying the partition may easily exceed the saving of having the partitions in the first place (The saving might be just one logical I/O, hence < 1/10,000 of a CPU second) .Where queries are very precise, a global index is quite likely to be a little more performant than a local index.
This is a common Oracle trade-off between how much you win/lose and how often you make that win/loss. In the local / global indexes case you (should expect to) lose a tiny amount of performance on every query in order to win a huge amount when you do partition maintenance such as eliminating entire partitions of very old data with a quick drop partition command.
Of course, if your partitions are large, and the indexes hit a large number of table rows, and the queries are over-generous in their throwaway rates; then the work saved by hitting just the one partition through exactly the correct index partition may prove to be a significant gain.
Further reading: N/A
