RE: Optimization of Partitioned Outer Joins
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Mon, 3 Jan 2005 00:07:11 +0100
Hi Jonathan
>To avoid confusion, could you post the execution
>paths of both queries ?
Here we go...
1) 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
