"Richard Foote" <richard.foote_at_bigpond.nospam.com> wrote in message
news:fs9Ce.50375$oJ.32717_at_news-server.bigpond.net.au...
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
> news:dbai22$mmm$1_at_nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
>> "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.
>>
>
> Hi Jonathan,
>
> I assume you meant to say "this query *isn't* really a candidate for a
> star transformation ?
>
> Cheers
>
> Richard
>
Too right -- I checked the complicated bits
I had written before I posted, but forgot to
check the easy 'is / is not' bit. Not the first
time I've done that.
I've left the entire chain uncut, so that anyone
who reads it can see the error and correction.
--
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
Received on Sat Jul 16 2005 - 11:32:41 CDT
