We usually use locally partitioned indexes on partitioned tables. Many of our data warehouse tables are partitioned by date and partitions get added or removed over time. It is much easier to maintain the indexes on this kind of table if they are locally partitioned. You can just drop a partition without rebuilding the indexes and you can rebuild indexes on a particular partition if needed, such as after compressing the partition.
But, I was asked whether a particular query would run faster with a global non-partitioned index on a partitioned table instead of a locally partitioned index. I said that I did not know of any reason to expect better performance from a global non-partitioned index, but it got me thinking so I did some experiments.
I have found a case where a global non-partitioned index on a table partitioned by date produces better performance than a locally partitioned index. Here is a zip of my test case.
With the global index my query ran in 68701 microseconds and with the local index it ran in 86816 microseconds.
I’m not sure if this is significant or not. It looks like each partition of a locally partitioned index has its own tree so you have to at least hit the root block on every partition if your query doesn’t have any condition which limits the partitions to be considered.
Anyway, I’d say stick with locally partitioned indexes unless you prove that something else is faster and you know the index maintenance isn’t a problem. But this test case shows that it is at least possible to get better performance with a non-partitioned global index on a partitioned table so there may be some special case where the performance benefit is great enough to be worth the maintenance headache.
– Bobby
