Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Anyway to optimize the optimizer

Re: Anyway to optimize the optimizer

From: jaromir nemec <jaromir_at_db-nemec.com>
Date: Sun, 6 Mar 2005 19:10:44 +0100
Message-ID: <007b01c52277$d1477160$3c02a8c0@JARAWIN>


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:

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:

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'))

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Mar 06 2005 - 13:15:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US