Global index faster than local in some cases

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


About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.