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 10.1.0.3.0 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 :
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.