Re: Datawarehousing query performance

I'm not sure if you are expecting the bitmap index to help on the first query, but I wouldn't expect it to. You are joining on id columns, and bitmap indexes tend to work well on columns with low cardinality.

For the first query you might try rewriting this by pushing the aggregation
of the fact_so_summary table into its own view inside the from clause and then joining to the detail tables in the outer query. This would look similar to:

SELECT t1.sum_non_cgi_count,

       dim_business_unit.business_unit_desc,
       ...
  FROM (SELECT sum(FACT_SO_SUMMARY.NON_CGI_COUNT) sum_non_cgi_count, 
               ..aggregate columns ..
               ..join columns...
          FROM FACT_SO_SUMMARY...
         GROUP BY...
       ) t1,
       DIM_BUSINESS_UNIT,
       DIM_DATE DIM_DATE2 

This would force Oracle to do the aggregation, restriction and group by on the summary table first and then join after. Depending on how much of the table you are returning this may be faster.

-Peter

jwilliam_at_aglresources.com (James A. Williams) wrote in message news:<5003a2b9.0204040953.1bf1395b_at_posting.google.com>...
> Running Oracle 8.1.7.3 on Solaris.
>
> Trying to get the two below queries to run better. Have not been able
> to get the below to pick up their bitmapped indexes on the fact table
> columns. Users want more as usual. Fresh run of dbms_stats to gather
> statistics for this small warehouse.
>
> QL> list
> 1 SELECT
> 2 SUM(FACT_SO_SUMMARY.NON_CGI_COUNT),
> 3 AVG(DECODE(FACT_SO_SUMMARY.NON_CGI_COUNT,1,
> 4 FACT_SO_SUMMARY.ELAPSED_WORK_TIME,NULL)),
> 5 DIM_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
> 6 DIM_DATE2.YEAR,
> 7 LPAD(TO_CHAR(DIM_DATE2.MONTH_NUMBER),2,0)||'-'||
> 8 SUBSTR(DIM_DATE2.MONTH_NAME,1,3),
> 9 SUM(FACT_SO_SUMMARY.COMPLETED_SO_COUNT),
> 10 SUM(FACT_SO_SUMMARY.CGI_COUNT)/SUM(FACT_SO_SUMMARY.CGI_COUNT +
> 11 FACT_SO_SUMMARY.NON_CGI_COUNT),
> 12 AVG(DECODE(FACT_SO_SUMMARY.NON_CGI_COUNT,1,
> 13 FACT_SO_SUMMARY.ELAPSED_ONSITE_TIME,NULL)),
> 14 AVG(DECODE(FACT_SO_SUMMARY.NON_CGI_COUNT,1,
> 15 FACT_SO_SUMMARY.ELAPSED_ENROUTE_TIME,NULL))
> 16 FROM
> 17 FACT_SO_SUMMARY,
> 18 DIM_BUSINESS_UNIT,
> 19 DIM_DATE DIM_DATE2
> 20 WHERE
> 21 (FACT_SO_SUMMARY.COMPLETED_DATE=DIM_DATE2.DATE_ID)
> 22 AND
> 23 (FACT_SO_SUMMARY.BUSINESS_UNIT_ID=DIM_BUSINESS_UNIT.BUSINESS_UNIT_ID)
> 24 AND
> 25 ((DIM_DATE2.GREGORIAN_DATE BETWEEN ADD_MONTHS
> 26 (TRUNC(SYSDATE,'MONTH'),-13) AND SYSDATE ))
> 27 GROUP BY
> 28 DIM_BUSINESS_UNIT.BUSINESS_UNIT_DESC, DIM_DATE2.YEAR,
> 29 LPAD(TO_CHAR(DIM_DATE2.MONTH_NUMBER),2,0)||'-'||
> 30* SUBSTR(DIM_DATE2.MONTH_NAME,1,3)
> SQL>
>
> 39 rows selected.
>
> Elapsed: 00:00:17.96
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=398 Card=11812 Byt
> es=791404)
>
> 1 0 SORT (GROUP BY) (Cost=398 Card=11812 Bytes=791404)
> 2 1 HASH JOIN (Cost=225 Card=31130 Bytes=2085710)
> 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'DIM_DATE' (Cost=33 C
> ard=69 Bytes=1932)
>
> 4 3 INDEX (RANGE SCAN) OF 'DIM_DATE_IND02' (NON-UNIQUE)
> (Cost=6 Card=69)
>
> 5 2 HASH JOIN (Cost=191 Card=409200 Bytes=15958800)
> 6 5 TABLE ACCESS (FULL) OF 'DIM_BUSINESS_UNIT' (Cost=1 C
> ard=4 Bytes=40)
>
> 7 5 TABLE ACCESS (FULL) OF 'FACT_SO_SUMMARY' (Cost=189 C
> ard=409200 Bytes=11866800)
>
>
>
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 12 db block gets
> 3112 consistent gets
> 2203 physical reads
> 0 redo size
> 7743 bytes sent via SQL*Net to client
> 647 bytes received via SQL*Net from client
> 4 SQL*Net roundtrips to/from client
> 4 sorts (memory)
> 0 sorts (disk)
> 39 rows processed
>
>
> SQL> get oess4
> 1 SELECT
> 2 DIM_DATE2.YEAR,
> 3 AVG(DECODE(DIM_SERVICE_ORDER.CUST_REQUEST_FLAG,'Y',
> 4 FACT_SO_SUMMARY.DAYS_TO_SERVICE,NULL)),
> 5 DIM_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
> 6 'Q'||TO_CHAR(DIM_DATE2.QUARTER_NUMBER)
> 7 FROM
> 8 DIM_DATE DIM_DATE2,
> 9 FACT_SO_SUMMARY,
> 10 DIM_SERVICE_ORDER,
> 11 DIM_BUSINESS_UNIT
> 12 WHERE
> 13 (FACT_SO_SUMMARY.SERVICE_ORDER_ID =
> DIM_SERVICE_ORDER.SERVICE_ORDER_ID) AND
> 14 (FACT_SO_SUMMARY.COMPLETED_DATE=DIM_DATE2.DATE_ID) AND
> 15 (FACT_SO_SUMMARY.BUSINESS_UNIT_ID =
> DIM_BUSINESS_UNIT.BUSINESS_UNIT_ID) AND
> 16 ((DIM_DATE2.GREGORIAN_DATE BETWEEN TRUNC(SYSDATE, 'YEAR') AND
> SYSDATE)
> 17 AND DIM_SERVICE_ORDER.CUST_REQUEST_FLAG = 'Y' AND
> 18 DIM_SERVICE_ORDER.WORK_COMPLETED_FLAG = 'Y' AND
> 19 DIM_SERVICE_ORDER.JOB_CODE IN
> 20 ('CC','LU', 'MOM01', 'MOM05',
> 21 'MOV01', 'MOV03', 'MOV05', 'MOV06',
> 22 'MOV08', 'MOV09', 'MOV11', 'MOV12',
> 23 'MOV13', 'MOV14', 'MOV17', 'OFF', 'ON',
> 24 'ON', 'UB', 'MOS01', 'MOP01'))
> 25 GROUP BY
> 26 DIM_DATE2.YEAR,
> 27 DIM_BUSINESS_UNIT.BUSINESS_UNIT_DESC,
> 28* 'Q'||TO_CHAR(DIM_DATE2.QUARTER_NUMBER)
>
>
> Elapsed: 00:00:14.36
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=627 Card=24 Bytes=
> 1440)
>
> 1 0 SORT (GROUP BY) (Cost=627 Card=24 Bytes=1440)
> 2 1 HASH JOIN (Cost=613 Card=78 Bytes=4680)
> 3 2 HASH JOIN (Cost=194 Card=2256 Bytes=103776)
> 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'DIM_DATE' (Cost=2
> Card=5 Bytes=100)
>
> 5 4 INDEX (RANGE SCAN) OF 'DIM_DATE_IND02' (NON-UNIQUE
> ) (Cost=2 Card=5)
>
> 6 3 HASH JOIN (Cost=191 Card=409200 Bytes=10639200)
> 7 6 TABLE ACCESS (FULL) OF 'DIM_BUSINESS_UNIT' (Cost=1
> Card=4 Bytes=40)
>
> 8 6 TABLE ACCESS (FULL) OF 'FACT_SO_SUMMARY' (Cost=189
> Card=409200 Bytes=6547200)
>
> 9 2 TABLE ACCESS (FULL) OF 'DIM_SERVICE_ORDER' (Cost=418 C
> ard=13185 Bytes=184590)
>
>
>
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 26 db block gets
> 9954 consistent gets
> 7669 physical reads
> 0 redo size
> 828 bytes sent via SQL*Net to client
> 425 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 3 rows processed
Received on Wed Apr 10 2002 - 01:49:41 CEST