OraFAQ Forum: Performance Tuning » Need to improve query performance

Home » RDBMS Server » Performance Tuning » Need to improve query performance

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Need to improve query performance [message #287579]

Wed, 12 December 2007 14:30

vijay_82
Messages: 1
Registered: December 2007

Junior Member

I have this query

create table ZZTSR071ABMMD000 nologging as
select a16.STATE_ID STATE_ID,
a15.CAL_QTR_START_DT CAL_QTR_START_DT,
a13.AZ_BRD_PRDGRP_ID AZ_BRAND_ID,
a12.MMA_PAYER_ID AZ_HCA_CUST_ID,
a18.AZ_MKT_ID AZ_MKT_ID,
sum(a11.TRX_COUNT) TRX
from CDW_RS.MTHLY_RX_DATA_BRAND_HCP_FCT a11
join CDW_RS.CUST_IMSPLAN_MMAPAYER_REL_V a12
on (a11.AZ_PLAN_CUST_ID = a12.IMS_PLAN_ID)
join CDW_RS.PROD_BRDPRDGRP_FCT_REL_LKP_V a13
on (a11.AZ_PROD_ID = a13.AZ_BRD_JOIN_ID)
join CDW_ADS.DIST_CHAN_DIM a14
on (a11.DIST_CHAN_SK = a14.DIST_CHAN_SK)
join CDW_RS.CAL_MONTH_LKP_V a15
on (a11.CAL_MONTH_DT_SK = a15.CAL_DT_SK)
join CDW_ADS.GEO_DIM a16
on (a11.CUST_INCTV_ZIP_CD = a16.ZIP)
join CDW_RS.PROD_BRAND_MKT_REL_LKP_V a17
on (a13.AZ_BRD_PRDGRP_ID = a17.AZ_BRAND_ID)
join CDW_RS.PROD_SBMKT_LKP_V a18
on (a17.AZ_SUB_MKT_ID = a18.AZ_SUB_MKT_ID)
where (a18.AZ_MKT_ID in (104485)
and a15.CAL_QTR_START_DT in (To_Date('2006-07-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), To_Date('2006-10-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), To_Date('2007-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), To_Date('2007-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))
and a16.STATE_ID in ('CA', 'AL', 'FL', 'TX', 'NY', 'PA', 'NC', 'OH', 'TN', 'MO', 'GA')
and a14.DIST_CHAN_LEVEL_1_ID in ('M', 'R'))
group by a16.STATE_ID,
a15.CAL_QTR_START_DT,
a13.AZ_BRD_PRDGRP_ID,
a12.MMA_PAYER_ID,
a18.AZ_MKT_ID

This query takes about 25 mins to execute. I need to reduce the time as much as possible. I'm attaching the plan for the same.
Any suggestions in improving the performance is welcome.

Thanks,
Vijay