Re: simple star transformation problem

"ddks" <ddks_at_usa.net> wrote in message
news:1121466752.575078.173350_at_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 )
> -- was
> -- AND > D_DATE.DATE_ID BETWEEN 20050701 AND 20050715
> --
> 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.

> The PREVIOUS execution plan, with BETWEEN::
> SELECT STATEMENT Hint=CHOOSE
> SORT GROUP BY
> NESTED LOOPS
> NESTED LOOPS
> TABLE ACCESS BY INDEX ROWID D_DATE
> INDEX RANGE SCAN PK_DATE
> PARTITION RANGE ITERATOR
> TABLE ACCESS FULL F_SALE_TRANSACTION
> TABLE ACCESS BY INDEX ROWID D_PRODUCT
> INDEX UNIQUE SCAN PK_PRODUCT
>

Two points:
First, this query is really a candidate for a star transformation. The star transformation is designed to optimise queries where you can find a small amount of data in the fact table by restricting on several dimensions. You aren't restricting on PRODUCT at all, you are only restricting on date.

Secondly, when you introducing the date_id as a numeric instead of a proper date, you conceal meaning from the optimizer, and make it do the wrong arithmetic. In this case, you have numbers around the 20,000,000 value and want a range of 15 - if your date_id varies from (say) 20010101 to 20050714, then you appear to want a range of 15 from a possible low/high of 40613, or 1 row in 2,700 even though you know you want 15 rows out of 1,656 or 1 row in 110. This has probably made Oracle come up with an extremely low cardinality for the join from dates to facts - which is why is has chosen to do a nested loop into products.

There are added complications because of the different ways in which Oracle may have forwarded the literal predicate through transitive closure. It would be a good idea to use dbms_xplan to see more details of the execution plan, including the predicate usage.

I suspect the big different in performance comes from the join to products. Assuming the fact table holds one date_id per partition (although the statistics in your first post suggest it might be two per partition) then the nested loop join into the fact table from the date table will be doing the same amount of work as the hash join, viz one scan of one partition for each row selected from the date table.

Since your predicate can be applied to either the FACT table of the Date table, you might like to move it across. Because of transitive closure, it shouldn't make any difference to the operation - but there are a couple of oddities with join cardinality and transitive closure which might happen to appear in your example.

-- 
Regards

Jonathan Lewis

Now waiting on the publishers:    Cost Based Oracle - Volume 1

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 8th July 2005