Re: simple star transformation problem

From: ddks <ddks_at_usa.net>
Date: 15 Jul 2005 15:32:32 -0700
Message-ID: <1121466752.575078.173350@o13g2000cwo.googlegroups.com>

bitmap_merge_area_size 1048576.

My main issue is the plan Oracle is using for this query. If I re-write the query with this:
SELECT
  D_PRODUCT.DEPARTMENT,
  SUM(F_SALE_TRANSACTION.SALES_AMT),
  D_DATE.MONTH
FROM

  D_PRODUCT,
  F_SALE_TRANSACTION,
  D_DATE

WHERE
( F_SALE_TRANSACTION.DATE_ID=D_DATE.DATE_ID )
  AND
( F_SALE_TRANSACTION.PRODUCT_ID=D_PRODUCT.PRODUCT_ID )
  AND
  D_DATE.DATE_ID in
(20050701,20050702,20050703,20050704,20050705,20050706,20050707,20050708,20050709,20050710,20050711,20050712,20050713,20050714,20050715) GROUP BY
  D_PRODUCT.DEPARTMENT,
  D_DATE.MONTH The plan changes to:
SELECT STATEMENT Hint=CHOOSE
  SORT GROUP BY
    HASH JOIN

      HASH JOIN
        INLIST ITERATOR		  	 	 	 	      	             	 TABLE ACCESS BY
INDEX ROWID	D_DATE
            INDEX RANGE SCAN	PK_DATE
PARTITION RANGE INLIST
          TABLE ACCESS FULL	F_SALE_TRANSACTION
      TABLE ACCESS FULL	D_PRODUCT

which looks a lot better (no nested loops etc) and execution time drops to <50 seconds. I'll now be collecting histograms (a.k.a. column level stats) because it seems that Oracle doesn't know how many rows the BETWEEN statement returns but it works correctly when I replace it with an IN LIST statement. I am not keen on histograms as it is not a very stable piece but I'll give it a try. Again any feedback/suggestions please let me know.
Thanks,
Daniel Received on Fri Jul 15 2005 - 17:32:32 CDT