Re: Tuning a large order by clause

ad hoc, there are two possible solutions/questions:

1. do you have indexes on the order-by fields?
2. try to analyze the tables like this: analyze table FACT_PREM_CONSUMPTION compute statistics for table; analyze table FACT_PREM_CONSUMPTION compute statistics for all indexes; (do this with the other tables, too)

   this will take a lot of time, but have in most cases    a great performance improvement.

hth
Rüdiger J. Schulz
rjs_at_berlin.de

jwilliam_at_aglresources.com (James A. Williams) wrote:
>The below query builds an aggregate table. The below order by causes a
>massive scan. I tried a composite index over the order by. I removed
>everything on the order by except for the account_id which has a
>single e-mail. I get a subsecond response.
>
>Can such an order by be tuned. Its traversing a million rows so the
>tempfiles are being hit.
>
>
>SELECT DIM_ACCOUNT.ACCOUNT_TYPE_CODE,
>FACT_PREM_CONSUMPTION.DELIVERY_GROUP_ID,
>DIM_PREMISE.METER_BILL_GROUP,
>FACT_PREM_CONSUMPTION.MARKETER_ID,
>FACT_PREM_CONSUMPTION.RELATIVE_MONTH_ID,
>FACT_PREM_CONSUMPTION.TARIFF_SCHEDULE_ID,
>FACT_PREM_CONSUMPTION.OFFICE_ID,
>FACT_PREM_CONSUMPTION.READING_ID,
>FACT_PREM_CONSUMPTION.NUMBER_OF_METER_POINTS,
>FACT_PREM_CONSUMPTION.PREMISE_CONSUMPTION_CCF,
>FACT_PREM_CONSUMPTION.GAS_THERMS, FACT_PREM_CONSUMPTION.AC_THERMS,
>FACT_PREM_CONSUMPTION.DDDC_CHARGE, FACT_PREM_CONSUMPTION.DDDC_FACTOR,
>FACT_PREM_CONSUMPTION.NUMBER_OF_MCF_METERS,
>FACT_PREM_CONSUMPTION.TOTAL_BASE_CHARGES,
>FACT_PREM_CONSUMPTION.NET_REVENUE_AMOUNT,
>FACT_PREM_CONSUMPTION.NUMBER_OF_RESIDENCES,
>FACT_PREM_CONSUMPTION.BASE_COMMODITY_BLK1_CHARGE,
>FACT_PREM_CONSUMPTION.BASE_COMMODITY_BLK2_CHARGE,
>FACT_PREM_CONSUMPTION.BASE_COMMODITY_BLK3_CHARGE,
>FACT_PREM_CONSUMPTION.BASE_COMMODITY_BLK4_CHARGE,
>FACT_PREM_CONSUMPTION.BASE_COMMODITY_BLK5_CHARGE,
>FACT_PREM_CONSUMPTION.VOLUME_BLK1,
>FACT_PREM_CONSUMPTION.VOLUME_BLK2, FACT_PREM_CONSUMPTION.VOLUME_BLK3,
>FACT_PREM_CONSUMPTION.VOLUME_BLK4, FACT_PREM_CONSUMPTION.VOLUME_BLK5,
>FACT_PREM_CONSUMPTION.TOTAL_BILLED_REGULATED_CUST,
>FACT_PREM_CONSUMPTION.HEAT_DEGREE_DAYS,
>DIM_ACCOUNT.ACCOUNT_STATUS_CODE,
>(SELECT MAX(FACT_REGULATED_BILLING.ETL_SEQUENCE_ID) FROM
>FACT_REGULATED_BILLING WHERE
>DIM_ACCOUNT.ACCOUNT_ID=FACT_REGULATED_BILLING.ACCOUNT_ID and
>FACT_REGULATED_BILLING.RELATIVE_MONTH_ID =
>FACT_PREM_CONSUMPTION.RELATIVE_MONTH_ID
>) ETL_SEQUENCE_ID_BILL
>FROM
>FACT_PREM_CONSUMPTION, DIM_PREMISE, DIM_ACCOUNT
>WHERE
>DIM_ACCOUNT.ACCOUNT_ID=FACT_PREM_CONSUMPTION.ACCOUNT_ID and
>DIM_PREMISE.PREMISE_ID=FACT_PREM_CONSUMPTION.PREMISE_ID and
>FACT_PREM_CONSUMPTION.RELATIVE_MONTH_ID IN (SELECT MONTH_ID FROM
>DIM_MONTH WHERE
>RELATIVE_MONTH_NUMBER >= TO_CHAR(ADD_MONTHS(SYSDATE,- 3),'YYYYMM')
>AND RELATIVE_MONTH_NUMBER <= TO_CHAR(SYSDATE,'YYYYMM'))
>ORDER BY
>FACT_PREM_CONSUMPTION.RELATIVE_MONTH_ID,
>FACT_PREM_CONSUMPTION.TARIFF_SCHEDULE_ID,
>FACT_PREM_CONSUMPTION.OFFICE_ID,
>FACT_PREM_CONSUMPTION.DELIVERY_GROUP_ID,FACT_PREM_CONSUMPTION.MARKETER_ID,
>FACT_PREM_CONSUMPTION.READING_ID,
>DIM_ACCOUNT.ACCOUNT_TYPE_CODE, DIM_PREMISE.METER_BILL_GROUP

-- 
__________________________________________________________
News suchen, lesen, schreiben mit http://newsgroups.web.de