Re: Anyway to optimize the optimizer

Hi Christian and Waleed,



thanks for the inspirational thoughts on this topic, that is particularly 
important to me. Some additional comments follows.



Jaromir



> Notice that if you don't use the view, Oracle should be able to perform

> the correct pruning.



I can only agree. The only thing I don't like on this approach is, that you 
incorporate the business rule (here: open_dt >= closed_dt - 10) into the 
query.

The original question was how can I profit from the partition pruning 
without explicitly defining the maximum open time of a transaction within 
each query.

Anyway this is the solution that I practise.



> In my opinion the opening/closing date should be checked at dimension =

> level and not on the fact. i.e. the structures used to support end-user =

> queries should be designed exactly for that.



I agree again. Especially if the business rule describing the maximum open 
time of a transaction (difference between closed_dt and open_dt is less or 
equal 10 in this example) can change over time it could be problematic to 
check it in the fact table.



But there are some possible problems with the proposed solution based on 
dimension table. Let me illustrate that:



I add an additional column to the time dimension table, describing a 
"minimal opening day for a transaction that was closed on the current day". 
In our example this "min_opening_dt" will be constantly 10 days behind the 
current day.



While joining the time dimension to the fact table for a particular day, 
everything works fine:



--- get all transactions closed on a particular day

select *

from time_dimension, fact_table

where time_dimension.day = to_date('2005.02.20','yyyy.mm.dd') and

time_dimension.day = fact_table.closed_dt  and

fact_table.open_dt <= time_dimension.day and

fact_table.open_dt >= time_dimension.min_opening_dt;



A nested loop join will most probably will be performed and a partition 
pruning KEY - KEY takes place based on the values of min_opening_dt and 
closed_dt.



The problem I see appears while joining on a range of closing dates:



-- the same select as above only with a different predicate for the day 
column:

select *

from time_dimension, fact_table

where time_dimension.day between to_date('2005.02.20','yyyy.mm.dd') and 
to_date('2005.02.21','yyyy.mm.dd') and

time_dimension.day = fact_table.closed_dt  and

fact_table.open_dt <= time_dimension.day and

fact_table.open_dt >= time_dimension.min_opening_dt;



Most probably a hash (or merge) join will be opened, see execution plan 
below. As the lower limit for open_dt is checked first in the filter for 
operation 1 (hash join) there is no pruning for Pstart for the fact_table 
(pruning for Pstop works fine).

Well, I may force the join to NL, but this can be sub optimal for larger 
ranges.

Any ideas on this topic?





-------------------------------------------------------------------------------------------------------

| Id  | Operation            | Name           | Rows  | Bytes | Cost (%CPU)| 
Time     | Pstart| Pstop |

-------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |                |     1 |    49 |     5  (20)| 
00:00:01 |       |       |

|*  1 |  HASH JOIN           |                |     1 |    49 |     5  (20)| 
00:00:01 |       |       |

|*  2 |   TABLE ACCESS FULL  | TIME_DIMENSION |     1 |    18 |     2   (0)| 
00:00:01 |       |       |

|   3 |   PARTITION RANGE ALL|                |     1 |    31 |     2   (0)| 
00:00:01 |     1 |     3 |

|*  4 |    TABLE ACCESS FULL | FACT_TABLE     |     1 |    31 |     2   (0)| 
00:00:01 |     1 |     3 |

-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------



   1 - access("TIME_DIMENSION"."DAY"="FACT_TABLE"."CLOSED_DT")

       filter("FACT_TABLE"."OPEN_DT"<="TIME_DIMENSION"."DAY" AND

              "FACT_TABLE"."OPEN_DT">="TIME_DIMENSION"."MIN_OPENING_DT")

   2 - filter("TIME_DIMENSION"."DAY">=TO_DATE('2005-02-20 00:00:00', 
'yyyy-mm-dd hh24:mi:ss')

              AND "TIME_DIMENSION"."DAY"<=TO_DATE('2005-02-21 00:00:00', 
'yyyy-mm-dd hh24:mi:ss') AND

              "TIME_DIMENSION"."MIN_OPENING_DT"<=TO_DATE('2005-02-21 
00:00:00', 'yyyy-mm-dd hh24:mi:ss'))

   4 - filter("FACT_TABLE"."CLOSED_DT">=TO_DATE('2005-02-20 00:00:00', 
'yyyy-mm-dd

              hh24:mi:ss') AND "FACT_TABLE"."CLOSED_DT"<=TO_DATE('2005-02-21 
00:00:00', 'yyyy-mm-dd

              hh24:mi:ss') AND "FACT_TABLE"."OPEN_DT"<=TO_DATE('2005-02-21 
00:00:00', 'yyyy-mm-dd

              hh24:mi:ss'))

----- Original Message ----- 
From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
To: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, March 04, 2005 8:41 AM
Subject: RE: Anyway to optimize the optimizer



--
http://www.freelists.org/webpage/oracle-l

Other related posts: