Predicate Pushing And Analytic Functions

I'm sure that there must be a fair number of Oracle professionals who carry around in their heads a little score card of some of their best tuning results ever … hopefully, at least, 'cos otherwise I'm a weirdo. Anyway, today I improved the performance of a set of decision support queries and achieved my best result ever – improving query speed by a factor of 180,000, from an original run time of one hour down to a new time of 0.02 seconds.

The wise monkeys in the audience will immediately be thinking "partition pruning!" or "materialized view!", and in fact if you thought the former then you'd be right. Here's how it worked.

I had defined a view against a large fact table so that it included several analytic functions. The details really don't matter, but the intention was to allow a column to contribute to a metric value only once per transaction_id, so the metric definition was something like:

Decode(Row_Number() Over (Partition By Transaction_ID Order By 1),1,Qty,0)

Queries that access this view had predicates on a number of columns, including some very selective indexed columns (Item_ID) and a partition key column (Fiscal_Month). Unfortunately there is nothing in the database to tell the optimizer that each unique value of Transaction_ID had but a single value Item_ID and Fiscal_Month, so logically the predicates could not be applied until after the analytic function had been calculated. Hence there was no predicate pushing on the fiscal_month and item_id, and neither partition pruning nor index access was considered. The query was actually scanning about one quarter of the table (it looks like a combination of subpartition pruning and partition pruning was taking place, but this table is multicolumn range + list composite partitioned, and pruning at the partition level was only taking place on the leading column of the partition key).

However, we included the two predicate columns in the analytic functions' partition clause like so:

Decode(Row_Number() Over (Partition By Transaction_ID, Item_Id, Fiscal_Month Order By 1),1,Qty,0)

Now this doesn't change the result because the Item_Id and Fiscal_Month entries are actually logically redundant, but it did allow both 9i and 10g to push the predicates and give full partition and subpartition pruning and index-based access.

Quite a nice result.

Here's a script that I used to demonstrate that it would work.

drop table t1;
create table t1
   txid   ,
   month  ,
connect by
In 10g the explain plan with predicate pushing was very straightforward:

Plan hash value: 2273146475

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT    |      |     9 |   252 |     3  (34)| 00:00:01 |
|   1 |  VIEW               |      |     9 |   252 |     3  (34)| 00:00:01 |
|   2 |   WINDOW SORT       |      |     9 |   351 |     3  (34)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| T1   |     9 |   351 |     2   (0)| 00:00:01 |


Predicate Information (identified by operation id):

3 - filter("ITEM_CD"=0)

Note the filter being applied to line 3. Where the predicate was not pushed it was applied to line 1.
The 9i explain plan was rather ... funky. But it worked.


9 thoughts on “Predicate Pushing And Analytic Functions

  1. Ah, but he was at your training day!

    (Which I am *still* waiting for one of the bloggers who were in attendance to review. David gave me an informal review via email but it included a swear-word of sorts so he may need to edit it)

  2. I’ll tell you what’s unbelievable — that Shrek wouldn’t let me autograph Jonathan’s book. Cheek!

    I’ll tell you what else though, there is a thread at AskTom which starts off with the assertion that you can’t predicate push into a view with analytic functions because it changes the result set, and that made me cry a modern equivalent of “Poppycock!” when I read it, but later on Tom cunningly refines his advice into “you can do it if the optimizer knows it won’t change the result set” (which is rather different from being able to do it if it won’t change the result set, of course). I was still suprised that the optimizer did it, more so about 9i than 10g.

    I thought I had him there, but alas …

    Anyway, for the small-slice-of-the-public record the training day was an excellent experience. It was a good job Jonathan skipped out early ‘cos I was going to ask him a series of unfeasible questions, such as “All of what you’ve said today, does it apply with parallel query/partitioning/MTS as well?” Again, alas …

  3. “Silver bullet” … heh heh heh.

    I guess if there is a wider lesson to be drawn from this very specific situation, it is to reinforce that reducing work by filtering as soon as possible is a Very Important Thing. When it also involves partition pruning then the results can be particularly memorable.

    Now that is something that Jonathan addressed in the training day — except for the bit about partitioning ;)

  4. It would be interesting to see if a defined dimension (realizing they were meant for materialized views, but should work in this situation) would accomplish the same thing…that is…telling the optimizer about the relationship…since that is what dimensions are supposed to do, tell the optimizer about hierarchies in flattened table structures.


  5. Hi David,

    This is a very interesting topic !!
    I am just wondering, since the goal of this performance tuning exercise is to push the predicates (Item_Id & Fiscal_Month)
    as deep as possible into the query and allows the optimizer to have full partition and subpartition pruning and index-based access

    How about using a parameterized view (sys_contex) ?
    Such as :

    select col_name
    from your_table
    where Fiscal_Month = to_date(sys_context( ‘Context’,’Fiscal_Month_value’ ), ‘YYYYMM’)
    and Item_Id = to_number(sys_context(‘Context’,’Item_Id_value’))

    If Fiscal_Month and Item_Id are declared as not null

    Then a “NVL” function can be used in the view

    where Fiscal_Month = NVL(to_date(sys_context( ‘Context’,’Fiscal_Month_value’ ), ‘YYYYMM’) , Fiscal_Month )
    and Item_Id = NVL(to_number(sys_context(‘Context’,’Item_Id_value’)) , Item_Id )


  6. Hi David

    I am having problem in pushing the predicate( 10.2) when I use partioned outer joins. It does a FTS instead of index path . For the following query I am getting a FTS on the table CT_BI_DST_CST_CAT instead of Index access on vp_cst_nbr.

    per.yyyy yr,
    per.mth_nbr mth,
    sum(equiv_cases) equiv_cases
    PARTITION BY (s.vp_cst_nbr, s.prod_cat)
    RIGHT OUTER JOIN ( select * from CT_BI_PERIOD where yyyy between 2006 and 2007) per ON ( s.yr =
    per.yyyy and s.mth = per.mth_nbr)
    WHERE vp_cst_nbr = 327260;

    Any ideas ? . I filed a SR with oracle and am still waiting for an answer.

    Any help would be appreciated


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s