RE: Optimization of Partitioned Outer Joins

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

Another info...

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

Without the partitioned outer join the same query runs as expected. = =3D=3D> i.e. to me it seams that the partitioned outer join prevents the = CBO to use the join predicate as access predicate!

SELECT t.fiscal_month_number,

       nvl(sum(s.amount_sold),0) amount_sold FROM sales s 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 |   TABLE ACCESS BY LOCAL INDEX ROWID| SALES             |
|   3 |    NESTED LOOPS                    |                   |
|*  4 |     TABLE ACCESS FULL              | TIMES             |
|   5 |     PARTITION RANGE ITERATOR       |                   |
|   6 |      BITMAP CONVERSION TO ROWIDS   |                   |
|   7 |       BITMAP AND                   |                   |
|*  8 |        BITMAP INDEX SINGLE VALUE   | SALES_TIME_BIX    |
|*  9 |        BITMAP INDEX SINGLE VALUE   | SALES_CHANNEL_BIX |
----------------------------------------------------------------

Predicate Information (identified by operation id):

---

   4 - filter("T"."FISCAL_YEAR"=3D1998 AND = "T"."FISCAL_QUARTER_NUMBER"=3D2)
   8 - access("TIME_ID"=3D"S"."TIME_ID")    9 - access("S"."CHANNEL_ID"=3D9)

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

Received on Sun Jan 02 2005 - 17:13:58 CST