Tuning a large order by clause

From: James A. Williams <jwilliam_at_aglresources.com>
Date: 13 Nov 2001 11:48:00 -0800
Message-ID: <5003a2b9.0111131147.853a06e@posting.google.com>

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 Received on Tue Nov 13 2001 - 13:48:00 CST