This is my blog on all things Oracle. I was an Enterprise Architect for a unnamed company, doing unamed things.
Please note that the views expressed here are my own.

Twitter Updates 2.2.1: FeedWitter

Wednesday, September 28, 2011

partitioning Local vs Global

In my last post I talked about creating a function based index on a GTT after my query plans went to hell after partitioning. Someone asked me to elaborate why my query plans went south.

Well to start with, I deal with very large tables.. Not terribly large (250g 2 billion rows). We are in the processess of partitioning this data, so we can purge it.. The performance on the data is very good, but we keep eating up disk space.

Seems simple enough right ? partition by date ranges, with some hash partitions thrown in on the column used the most for lookups. Nice and neat. At this point we have 116 partitions. Smaller is better right.

Since the whole reason for doing this is being able to purge, we created local indexes on almost all the columns except for the primary key. Being able to maintain the partitions is critical.

Doing all this I assumed we would be OK with local indexes. The application does index lookups, and the ones that don't use the primary key (or the hash partitioned key) are close to unique.

How long can a lookup take with an index and number of distict values = num_rows. Easy..

Then the dbreplay came, and the queries were slower.. much slower.. plan was similar but buffer gets was off.

Comparing the index partitioned vs non-partitioned, (with 116 subpartitions), you can see the difference. 3,999 ms vs 472,928 ms . What caused me the biggest issue is that I didn't realize it was doing a nested loop, 54 times.. this made the difference 36,000 ms vs 4,256,352 ms. 116x longer with a local partion vs global.

Lesson learned was that with partitioning you need to balance performance with maintainability.. Local indexes can be very expensive. Especially with nested loops.