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

Oracle Sponge -- Now Moved To Wordpress

Please use http://oraclesponge.wordpress.com

Tuesday, August 30, 2005

Partition Pruning and Dimension Tables

As a side note to a previous blog, Peter Scott kindly humoured me by checking on a partition pruning scenario that I had been unable to simulate. Specifically, the scenario is of partition pruning in a fact table based on a predicate that references a higher dimensional level through a joined dimension table.

Well, I found the metalink note that describes this method of optimization, and it's number 209070.1, with the generously proportioned title: "Partition Pruning based on Joins to Partitioning Criteria Stored in Dimension Tables". In contrast to the Most Useless Metalink Article Ever, this one is actually extremely useful, and aside from skipping details of a couple of hidden parameters it is very detailed in its description of the internal mechanism by which the optimizer implements "subquery pruning". Maybe I'll name it "Most Useful Metalink Article Ever". Nah, maybe not.

What also aroused my interest was the article's mention of the TBL$OR$IDX$PART$NUM() function, which I'd previously only encountered in trace files for partition exchanges and an article by Jonathan Lewis. I guess that this is about as documented as this function gets.

Another feature, interesting by it's omission, is that foreign keys do not get a mention either in this article or the associated 179518.1: "Partition Pruning and Joins". I'll have to see if my tests require a foreign key or not -- Peter's own test used foreign key constraints in the disabled/rely state, and I'm guessing that they're not considered.

Also worthy of note are the conditions under which subquery pruning will be invoked: "The cost of the recursive subquery must not exceed 5% of the cost of accessing all data in the partitioned fact table and the predicates on the dimension table must select less than 50% of the data in the dimension table." The former appears to be controlled by the hidden parameter _subquery_pruning_cost_factor and the latter by _subquery_pruning_reduction, which have default values of 20 (1/0.05 of course) and 50 respectively on my database. Rather circumstantial evidence, but supported by the note's advice:
"As long as there is a valid join on the partition key then pruning can be forced by setting :
_subquery_pruning_cost_factor =1
_subquery_pruning_reduction =100.
This effectively overrides the default choice made by the optimizer."

The recursive subquery referred to above appears to be the one by which the required partitions are identified: that involving the TBL$OR$IDX$PART$NUM() function. Since this is a query against a dimension table it is difficult toimagine that it could reach 5% of the total query cost under real world conditions, but it's another issue to bear in mind. I speculatively wonder whether it would be possible to create a materialized view to help return this result faster, or whether the nature of the function or the use of recursive SQL would prohibit it's creation or use? Another thing to think about later.

Anyway, a little more work could nail these issues down ... alas, tempus fugit etc.

Finally there is another hidden parameter (revealed through the use of JL's script here) to consider: _subquery_pruning_mv_enabled, with a default value of "false" and a description of "enable the use of subquery predicates with MVs to perform pruning". This seems to me to be a critical parameter. Why it is set to false by default is not immediately clear to me but I'll be keeping this parameter in mind as it appears that it could relate to query rewrite which is an important part of the database design I am working on. Worse case scenario: raising an iTar to get the thumbs up from Oracle Support to make a change to a hidden parameter. Not a bad thing in itself, as they generally like you to jump through a few hoops in justifying such a course of action.

Well, it's been an interesting and educational week so far, that's for sure.

For another resource on this issue see this thread at AskTom.


At 9:39 AM, Blogger Pete_S said...

You've been busy, David. Nice research!

I would have thought that in the real world there would be some form of FK on the dimension (well, if you want the figures to add up that is) But I can't see a reason why not having FKs will cause a problem with the test - but you would have real problems with DW integrity ;)

In my test I was looking at the result for 1 week out of 116 and certainly a small amount of the data from the fact table so both condtitions would be met with comfort.

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

Hmmm, I don't think that the existence of the FK would affect the accuracy except in as far as it relates to query rewrite ... is that what you mean Pete?

You know, you might like to look at whether you are getting subquery pruning (isn't it nice when you find out the proper name for something?) when a query rewrites to an MV ... just to see whether you can detect any influence from that _subquery_pruning_mv_enabled. I'm speculating that it might demote the chances of a rewrite by preventing SQPP (I've invented a new abbreviation!) against the MV, or it might allow the rewrite but disallow SQPP against the MV anyway.

Or maybe it is completely innocuous.

At 10:29 AM, Blogger Pete_S said...

Hmmm, I don't think that the existence of the FK would affect the accuracy except in as far as it relates to query rewrite
It also affects queries where you roll-up a snow-flake dimension.

I would need to do some more research before I change a hidden parameter, even on dev system! And then I may not share the results, for what works for me may not for anyone else in the whole world

At 10:33 AM, Blogger David Aldridge said...

Ah OK, you mean FK's from dim->dim ..sorry.

Oh indeed, heaven forbid. I wouldn't ask you to change the value :D ... but if you do get a moment perhaps you might modify that previous script in which you demoed PP so that it could be rewritten, and just see if the rewrite happens, and if it does whether SQPP occurs (if appropriate). Only if you have time mind ... I know how busy you managers are ;)


Post a Comment

Links to this post:

Create a Link

<< Home