simple star transformation problem

From: ddks <ddks_at_usa.net>
Date: 15 Jul 2005 09:59:23 -0700
Message-ID: <1121446763.001098.316330@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 Received on Fri Jul 15 2005 - 11:59:23 CDT