-
Query optimization
Hi,
I am looking at some one to review this QEP as this is taking more than 40 seconds to run this query.
Query :
Tables used : AGGR_PL_FISCAL_PERIOD = 8million rows
LU_ACCOUNT = 4million rows
LU_ACCOUNT = 1Million rows
select a11.TIME_PERIOD_IND_ID TIME_PERIOD_IND_ID,
a11.LEDGER_TYPE_ID LEDGER_TYPE_ID,
a11.FISCAL_PERIOD_ID FISCAL_PERIOD_ID,
sum(a11.PL_PERIOD_AMT_LOCAL) WJXBFS1
from AGGR_PL_FISCAL_PERIOD a11,
LU_ACCOUNT a12,
LU_ACCOUNT_LOD6 a13
where a11.ACCOUNT_ID = a12.ACCOUNT_ID and
a12.LOD6_ID = a13.LOD6_ID
and (a11.FISCAL_PERIOD_ID between TO_CHAR(ADD_MONTHS(TO_DATE( '200405','yyyymm'),-11),'yyyymm') and TO_CHAR(ADD_MONTHS(TO_DATE( '200405','yyyymm'),-0),'yyyymm')
and a11.LEDGER_TYPE_ID in (1877)
and a11.TIME_PERIOD_IND_ID in (1)
and a13.LOD6_OBJECT_ACCOUNT_SRCCD = '65100'
and ((a12.BUSINESS_UNIT_ID in (156569, 156590, 156591, 156947, 156948, 156949, 156950, 156951)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (156571, 156989, 156990, 156991, 157007, 157008, 157009, 157010)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (156607, 157011, 157027, 157028, 157029, 157030, 157031, 157047)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (156570, 156967, 156968, 156969, 156970, 156971, 156987, 156988)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (157514, 157497, 157498, 157499, 157500, 157501, 157512, 157513)
and a12.COMPANY_ID in (3023))
or (a12.BUSINESS_UNIT_ID in (156568, 156608, 156609, 156610, 156611, 156587, 156588, 156589)
and a12.COMPANY_ID in (3023))))
group by a11.TIME_PERIOD_IND_ID,
a11.LEDGER_TYPE_ID,
a11.FISCAL_PERIOD_ID
/
Will post the trace file in the next thread
There is always a better way to do the things.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|