.comment-link {margin-left:.6em;}

Oracle Sponge -- Now Moved To Wordpress

Please use http://oraclesponge.wordpress.com

Sunday, August 21, 2005

More on Partition Not-Quite-Pruning

(Updated below)

Here's a follow up to the previous post on this subject, which ought to be read first. This is just to give more background on why I think this technique works.

We have a scenario where we want to read all of the rows where fiscal_month is Jan-2005 (around one million rows) from a fact table of sixty partitions (based on file_load_month). 99.5% of these rows (995,000 rows) are contained in a single partition (file_load_month=Jan-2005) which itself holds around one million rows. The other 5,000 rows are scattered across other partitions of the table. In fact they will probably be found in around ten-twenty of the remaining fifty-nine partitions.

If you just query for rows where "fiscal_month = Jan-2005" then no partition pruning is possible (remember that the table is partitioned on file_load_month) and therefore the optimizer has two choices on how to execute this
  1. Access by index -- find several million rows by reading a bitmap index on fiscal_month and then reading the relevant blocks through single block io.
  2. Access by full table scan -- read all sixty partitions of the table in multiblock io.
Neither of these are satisfactory, so a different query was employed where two queries were UNION ALL'd together.
  1. Selects all of the required rows from the file_load_month Jan-2005 partition
  2. Select all of the required rows from all of the other partitions.
The first query uses partition pruning to read 995,000 rows of the one million rows in the file_load_month Jan-2005 partition. Multiblock io is used.

The second query accesses the table through a BITMAP MINUS operation on two bitmap indexes (on fiscal_month minus file_load_month) to read around 5,000 rows that are probably scattered throughout the remaining fifty-nine partitions using single block io.

Now if you consider how many blocks get read for the old technique and the new technique, then the two methods are practically the same. In fact the new method probably reads slightly more blocks than the old method. The difference between them comes from the ability of the new methodology to read nearly all of the required table blocks in multiblock mode.

It is interesting to note that the optimizer actually has all of the information that it needs in order to perform this optimization internally. Partition-level histograms on the fiscal_month show that nearly all of the required rows are contained in a single partition, and almost none of them are contained in any other.

I'm going to get some explain plans extracted that are more instructive than those I currently have, and I'll get back to you with them.

Update

Jonathan Lewis has written to point out the similarity between this approach and the practically-defunct feature of "Partition Views" (PV), in which similar tables are UNION ALL'ed together in a view that can be queried to produce an effect similar to pruning in a partitioned table (PT).

He also points out one of the strengths of PV -- that the optimizer could not only decide not to touch a partition (a form of optimization we know as "pruning" of course) but could also generate a different form of execution plan for each partition that the query would touch. This is a benefit of PV that was lost with PT (except for the "do not touch this partition" pruning optimization) and which is resurrected in the technique I'm describing here. Exactly the same principle applies, of breaking up a result set into multiple subsets with UNION ALL to merge them together.

I'm inclined to wonder whether in this particular example an approach of defining a partition view over a partitioned table, in the form of ...

Select fiscal_month, file_load_month, ...
from my_table
where fiscal_month = file_load_month
Union All
Select fiscal_month, file_load_month, ...
from my_table
where fiscal_month != file_load_month

... would be productive. I wonder whether the optimizer transitivity would be robust enough to handle this situation.

Jonathan's article comparing PV and PT is in Microsoft Word format here -- for the MS-averse Google has a passable HTML conversion here.

8 Comments:

At 2:20 PM, Anonymous Anonymous said...

David,

Your first query is

Select *
From old_table
Where fiscal_month = '01-jan-2005'

and the partition key is file_load_month. Please post the execution plan. I am interested to see how the query on this table on fiscal_month column is doing the partition pruning. How you got into the conclusion that the second query is faster than the first one because it is doing "Multiblock io". I think the difference is partition pruning Vs no-partition pruning.

 
At 4:43 PM, Blogger David Aldridge said...

You're not getting "first" and "second" queries that areunioned together confused with the "old"and "new" queries are you?

The one you list is the "old" query that does no partition pruning ... the "new" one is the UNION ALL combination of two queries, in which one of them performs a full scan of a single partition and the other performs an index-based lookup of the few required rows not present in that partition.

 
At 9:53 PM, Anonymous Anonymous said...

I am suspicious about this example. You have 60 monthly partitions for 60 fiscal months and 99.5% of the data belongs to one fiscal month – January 2005. All the other 59 fiscal months have the remaining 0.5% of the data!. You are doing conversion for changing the partition key from fiscal month to load month. A query time of 56 seconds for 1 million rows is not acceptable in a data conversion situation. I am very suspicious about this example. Is this a real life one or something you came up with to explain the query behavior

 
At 10:57 PM, Blogger David Aldridge said...

I'm guessing/half-remembering the one million -- it's a ballpark figure, but the real number could be from one to four million. Likewise the 99.5/0.5% split -- that could be 99.995/0.005%, I just don't have the numbers available because I don't have access to the data on the weekend, and only have access through one of my co-workers (Hi Larry!) at any other time, but I should be able to get the real numbers tomorrow.

In fact, just to amplify on the business scenario here in case that helps, we get transactions from approxiamtely 80 systems, all of the same application but with different databases. Imagine that they are different unconnected store locations and that'd be close. Sometimes we don't get all of the January transactions in January -- sometimes a few arrive in February, and a few in March, and a few in April. Sometimes there is a type of "store" that is disconnected from the outside world for several weeks or months and then a bunch of transactions will all arrive at once.

There is another business process where we can get a transaction modification sent to us one, two, six, twelve months after the original. This is dated the same as the original transaction but arrives so-many months later.

In fact the fiscal month for a record is always less than or equal to the file load month (as far as I know). The practical effect of this is that the records that it holds mostly cover the prior twelve month period. Sometimes we get a record with a fiscal month that is twenty-or-so months older than its file load month.

As far as the optimizer is concerned it's going to touch every partition of the local bitmap indexes in order to find those records, even if all the fiscal month = Jan2005 records are actually located in the file load month = 2005 partition as it has no absolute information on this.

Don't forget that the 56 seconds is when those rows are accessed through a local bitmap index of 60 partitions. If this was a full scan of a few million rows in a single partition it would be a heck of a lot faster. In fact I'm doubtful that parallel query is helping at all here, other than to avoid messing with the buffer cache.

 
At 11:02 PM, Anonymous Anonymous said...

Should My Home Business Have a Blog?
Some choice quotes: "But as a home-based business owner, should I have a blog? To put it bluntly, if you conduct any part of your business over the internet, then Yes, you should have a blog.So why should your ...
Amazing job on your Blog! I'll definitely be coming back.
pancreas cancer site covering pancreas cancer related stuff.

 
At 11:13 PM, Blogger David Aldridge said...

Hmmm, maybe prunes cause pancreatic cancer, and it triggered that blog spam. Let's see what else we can get ...

*ahem*

"erectile dysfunction"

"mortgage rate too high"

"working from home"

Come on spambots, show us what you've got.

 
At 11:32 PM, Anonymous Anonymous said...

Cellphone Towers Sprouting Up In The Strangest Places
With the growth of cellphone usage comes an increasing amount of cellphone towers to supply the connectibility and reception of calls.
I liked your blog and will try to come back again. I have a credit line site/blog that is related to credit line stuff.
Thanks and check mine out sometime!

 
At 7:09 AM, Blogger David Aldridge said...

Lovely ... a "credit line" spam. The system works.

 

Post a Comment

<< Home