RE: Optimization of Partitioned Outer Joins

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Mon, 3 Jan 2005 00:07:11 +0100
Message-ID: <2CF83791A616BB4DA203FFD13007824A01E6AFBD@MSXVS02.trivadis.com>

Hi Jonathan

>To avoid confusion, could you post the execution >paths of both queries ?

Here we go...

the query I would like to execute

SELECT t.fiscal_month_number,

nvl(sum(s.amount_sold),0) amount_sold FROM sales s PARTITION BY (channel_id)

             RIGHT OUTER JOIN (
               SELECT time_id, fiscal_month_number
               FROM times t
               WHERE t.fiscal_year =3D 1998
               AND t.fiscal_quarter_number =3D 2
             ) t USING (time_id)

WHERE channel_id =3D 9
GROUP BY t.fiscal_month_number;

| Id | Operation | Name |

| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | VIEW | |
| 3 | MERGE JOIN PARTITION OUTER| |
| 4 | SORT JOIN | |
| 5 | VIEW | |

|*  6 |       TABLE ACCESS FULL      | TIMES |
|*  7 |     SORT PARTITION JOIN      |       |


|   8 |      PARTITION RANGE ALL     |       |

|*  9 |       TABLE ACCESS FULL      | SALES |
----------------------------------------------

Predicate Information (identified by operation id):

6 - filter("T"."FISCAL_YEAR"=3D1998 AND =

"T"."FISCAL_QUARTER_NUMBER"=3D2)
   7 - access("T"."TIME_ID"=3D"S"."TIME_ID")
       filter("T"."TIME_ID"=3D"S"."TIME_ID")

9 - filter("S"."CHANNEL_ID"=3D9)

2) same as query 1 with USE_NL hint

SELECT /*+ use_nl(s t) */ t.fiscal_month_number,

nvl(sum(s.amount_sold),0) amount_sold FROM sales s PARTITION BY (channel_id)

             RIGHT OUTER JOIN (
               SELECT time_id, fiscal_month_number
               FROM times t
               WHERE t.fiscal_year =3D 1998
               AND t.fiscal_quarter_number =3D 2
             ) t USING (time_id)

WHERE channel_id =3D 9
GROUP BY t.fiscal_month_number;

| Id | Operation | Name |

| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | VIEW | |
| 3 | NESTED LOOPS PARTITION OUTER| |
| 4 | BUFFER SORT | |
| 5 | VIEW | |

|*  6 |       TABLE ACCESS FULL        | TIMES |
|*  7 |     FILTER                     |       |


|   8 |      SORT PARTITION JOIN       |       |

|   9 |       PARTITION RANGE ALL      |       |

|* 10 |        TABLE ACCESS FULL       | SALES |
------------------------------------------------

Predicate Information (identified by operation id):

6 - filter("T"."FISCAL_YEAR"=3D1998 AND = "T"."FISCAL_QUARTER_NUMBER"=3D2)
7 - filter("T"."TIME_ID"=3D"S"."TIME_ID") 10 - filter("S"."CHANNEL_ID"=3D9)

The problem here is that I would like to see the filter 7 to be applied = in step 10 as access predicate. Of course since the index scan is "not = possible" the NL makes no sense here...

3) the modified query (TIMES is joined once more to enable an index = access... please notice that in my first post I copy/pasted the wrong = query, sorry for that!)

SELECT t.fiscal_month_number,

nvl(sum(s.amount_sold),0) amount_sold FROM sales s JOIN (

               SELECT time_id
               FROM times
               WHERE fiscal_year =3D 1998
               AND fiscal_quarter_number =3D 2
             ) t1 USING (time_id)
             PARTITION BY (channel_id)
             RIGHT OUTER JOIN (
               SELECT time_id, fiscal_month_number
               FROM times
               WHERE fiscal_year =3D 1998
               AND fiscal_quarter_number =3D 2
             ) t USING (time_id)

WHERE channel_id =3D 9
GROUP BY t.fiscal_month_number;

| Id | Operation | Name |

| 0 | SELECT STATEMENT | |
| 1 | SORT GROUP BY | |
| 2 | VIEW | |
| 3 | MERGE JOIN PARTITION OUTER | |
| 4 | SORT JOIN | |
| 5 | VIEW | |

|*  6 |       TABLE ACCESS FULL                | TIMES             |
|*  7 |     SORT PARTITION JOIN                |                   |


|   8 |      VIEW                              |                   |

|   9 |       TABLE ACCESS BY LOCAL INDEX ROWID| SALES             |

|  10 |        NESTED LOOPS                    |                   |

|* 11 |         TABLE ACCESS FULL              | TIMES             |


|  12 |         PARTITION RANGE ITERATOR       |                   |

|  13 |          BITMAP CONVERSION TO ROWIDS   |                   |

|  14 |           BITMAP AND                   |                   |

|* 15 |            BITMAP INDEX SINGLE VALUE   | SALES_TIME_BIX    |
|* 16 |            BITMAP INDEX SINGLE VALUE   | SALES_CHANNEL_BIX |
--------------------------------------------------------------------

Predicate Information (identified by operation id):

   6 - filter("FISCAL_YEAR"=3D1998 AND "FISCAL_QUARTER_NUMBER"=3D2)
   7 - access("T"."TIME_ID"=3D"from$_subquery$_004"."TIME_ID")
       filter("T"."TIME_ID"=3D"from$_subquery$_004"."TIME_ID")
  11 - filter("FISCAL_YEAR"=3D1998 AND "FISCAL_QUARTER_NUMBER"=3D2)
  15 - access("S"."TIME_ID"=3D"TIME_ID")
  16 - access("S"."CHANNEL_ID"=3D9)

This query is much faster (ca. factor 5) and does much less LIO (ca. = factor 3.5). Of course in this case there is no big difference.... but = in a real star schema it will be a real problem...

>Which table are you calling
>the inner table - from your choice of table to 'add',
>it looks like you are considering the TIMES table
>to be the inner, but the original times table is
>the preserved table in the outer join, so for a
>nested loop it would be the outer table.

The inner table in relation to the NL, i.e. SALES. (in this schema SALES = is the fact table and, therefore, I don't want to see a FTS on it...)

Thanks
Chris

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

Received on Sun Jan 02 2005 - 17:04:30 CST