"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
Received on Sat Jul 16 2005 - 10:11:07 CDT
