Re: simple star transformation problem

"ddks" <ddks_at_usa.net> wrote in message
news:1121446763.001098.316330_at_o13g2000cwo.googlegroups.com...
> Can somebody explain why the following query is not executed very
> efficiently.
>
> 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 BETWEEN 20050701 AND 20050715
> GROUP BY
> D_PRODUCT.DEPARTMENT,
> D_DATE.MONTH
>
> The execution plan is:
> 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
>
> Some addtional info:
> The fact table has 140,000,000 rows and is partitioned by date_id
> (300,000 rows per partition).
>
> The product table has 250,000 rows and several indexes. Department is
> an attribute of the product table and month is an attribute of the date
> table. Both are indexed of course.
>
> The date selected should select approx 2,000,000 rows which are then
> aggregated to 99 rows (99 departments). The query takes about 4 minutes
> to execute and doesn't scale at all (expanding date range etc).
>
> All tables are analyzed and bitmap indexes exist on the foreign keys.
>
> We are running Oracle 9i R2 with Olap features on Sun with 8 CPU and 8
> GB of memory. Star Transformation is enabled and optimizer_mode is
> CHOOSE.
>
> What should I be looking at to improve things? Create a global date_id
> index across partitions or ? Any help or suggestions is greatly
> appreciated.
>
> Thanks,
> Daniel
>

Hi,

How big is your bitmap merge area size (init parameter) ? Have you tried increasing it ?

Matthias Received on Fri Jul 15 2005 - 13:54:54 CDT