Oracle Scratchpad

July 6, 2011

Indexing

Filed under: Indexing,Oracle,Partitioning,Tuning — Jonathan Lewis @ 5:39 pm BST Jul 6,2011

A question about partitioning came up on OTN a few days ago – but the really interesting part of the thread was about the choice of indexes, and how the choice of partitioning, combined with the data patterns, could make a big difference to the choice of indexes. I think the comments I made about indexes are worth seeing, so I’ve linked to the thread.

2 Comments »

  1. I’m intersted to know what benefits do you see in list partitioning over range partitioning in this case. Is your preference based on the fact that there will be no queries that span more than one year/period?

    Comment by Todor Botev — July 22, 2011 @ 7:29 pm BST Jul 22,2011 | Reply

    • Todor,

      In this specific case the user is looking at a two-column strategy – (year, period) – which has a big impact on the choice of best partitioning strategy.

      Remember that I asked the question: ‘Do you not have to deal with queries like “data between year 2010 period 12 and year 2011 period 2″ – if so how will they work ?’ and got the answer: ‘This should not happen. And even if they do it will just end up looking at two partitions.. If we take the OP at face value then there’s a good reason for looking at all possible solutions.

      List partitioning is good on the year because there are only a few distinct values, not a continuous range of values. The same argument applies to list partitioning on period.

      List partitions do (to a limited extent) allow for range-based predicates. I haven’t done any experiments on list/list composites, but on a simple list partition you can have a predicate like: “year between 2008 and 2010″ and the optimizer will recognise this as an opportunity for an inlist iterator and take advantage of partition pruning.

      Comment by Jonathan Lewis — July 25, 2011 @ 9:09 am BST Jul 25,2011 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,101 other followers