Home » Fusion Middleware & Colab Suite » Business Intelligence » Problem with query (Oracle 10.2.0.4 (RHEL 5))
Problem with query [message #484536] |
Wed, 01 December 2010 05:23 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
indrajit2002
Messages: 53 Registered: November 2007 Location: INDIA
|
Member |
|
|
Hi I have a query used for a OBIEE report which takes around 3 and a half minutes to execute. But the client wants it to get executed in 30 seconds. But the problem is I cannot take an explain plan for the query through sqlplus and the explain plan taken out from toad can not be pasted here. I am typping the full query here. Can anyone help me in suggesting what needs to be done?
WITH
SAWITH0 AS (select T156337.ROW_WID as c3,
T156337.MCAL_YEAR_WID as c4,
ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_YEAR_WID ORDER BY T156337.MCAL_YEAR_WID DESC) as c5,
T156337.MCAL_PERIOD_WID as c6,
ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_YEAR_WID, T156337.MCAL_PERIOD_WID ORDER BY T156337.MCAL_YEAR_WID DESC, T156337.MCAL_PERIOD_WID DESC) as c7
from
W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */
where ( T156337.MCAL_CAL_NAME = 'AHS Accounting' ) ),
SAWITH1 AS (select Case when case SAWITH0.c5 when 1 then SAWITH0.c3 else NULL end is not null then Rank() OVER ( ORDER BY case SAWITH0.c5 when 1 then SAWITH0.c3 else NULL end ASC NULLS LAST ) end as c1,
Case when case SAWITH0.c7 when 1 then SAWITH0.c3 else NULL end is not null then Rank() OVER ( PARTITION BY SAWITH0.c4 ORDER BY case SAWITH0.c7 when 1 then SAWITH0.c3 else NULL end ASC NULLS LAST ) end as c2,
SAWITH0.c3 as c3,
SAWITH0.c4 as c4,
SAWITH0.c6 as c5
from
SAWITH0),
SAWITH2 AS (select distinct min(SAWITH1.c1) over (partition by SAWITH1.c4) as c1,
min(SAWITH1.c2) over (partition by SAWITH1.c4, SAWITH1.c5) as c2,
SAWITH1.c3 as c3
from
SAWITH1),
SAWITH3 AS (select T156337.MCAL_QTR_WID as c3,
T156337.MCAL_YEAR_WID as c4,
T156337.MCAL_PERIOD_NAME as c5,
T156337.ROW_WID as c6,
ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_YEAR_WID ORDER BY T156337.MCAL_YEAR_WID DESC) as c7,
T156337.MCAL_PERIOD_WID as c8,
ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_YEAR_WID, T156337.MCAL_PERIOD_WID ORDER BY T156337.MCAL_YEAR_WID DESC, T156337.MCAL_PERIOD_WID DESC) as c9
from
W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */
where ( T156337.MCAL_CAL_NAME = 'AHS Accounting' ) ),
SAWITH4 AS (select Case when case SAWITH3.c7 when 1 then SAWITH3.c6 else NULL end is not null then Rank() OVER ( ORDER BY case SAWITH3.c7 when 1 then SAWITH3.c6 else NULL end ASC NULLS LAST ) end as c1,
Case when case SAWITH3.c9 when 1 then SAWITH3.c6 else NULL end is not null then Rank() OVER ( PARTITION BY SAWITH3.c4 ORDER BY case SAWITH3.c9 when 1 then SAWITH3.c6 else NULL end ASC NULLS LAST ) end as c2,
SAWITH3.c3 as c3,
SAWITH3.c4 as c4,
SAWITH3.c5 as c5,
SAWITH3.c8 as c6
from
SAWITH3),
SAWITH5 AS (select distinct min(SAWITH4.c1) over (partition by SAWITH4.c4) as c1,
min(SAWITH4.c2) over (partition by SAWITH4.c4, SAWITH4.c6) as c2,
SAWITH4.c3 as c3,
SAWITH4.c4 as c4,
SAWITH4.c5 as c5
from
SAWITH4),
SAWITH6 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5
from
(select sum(T146219.BUDGET_LOC_AMT * T146219.GLOBAL1_EXCHANGE_RATE) as c1,
T91397.GL_ACCOUNT_NUM as c2,
T91397.GL_ACCOUNT_NAME as c3,
SAWITH5.c4 as c4,
SAWITH5.c3 as c5,
ROW_NUMBER() OVER (PARTITION BY T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, SAWITH5.c3 ORDER BY T91397.GL_ACCOUNT_NUM ASC, T91397.GL_ACCOUNT_NAME ASC, SAWITH5.c3 ASC) as c6
from
W_LEDGER_D T146058 /* Dim_W_LEDGER_D */ ,
W_GL_ACCOUNT_D T91397 /* Dim_W_GL_ACCOUNT_D */ ,
W_BUDGET_D T146170 /* Dim_W_BUDGET_D */ ,
W_ACCT_BUDGET_F T146219 /* Fact_W_ACCT_BUDGET_F */ ,
SAWITH2,
SAWITH5
where ( T91397.ROW_WID = T146219.GL_ACCOUNT_WID and T146058.ROW_WID = T146219.LEDGER_WID and T146170.ROW_WID = T146219.BUDGET_WID and T146219.PERIOD_END_DT_WID = SAWITH2.c3 and SAWITH5.c1 = SAWITH2.c1 and T146058.LEDGER_NAME = 'AHS Ledger' and T146219.X_ACCT_CURRENCY_CODE = 'CAD' and SAWITH5.c5 = 'NOV-09' and T146170.APPLICATION_SOURCE <> 'GL_PSFT_STD' and (T91397.GL_ACCOUNT_NUM in ('14050003', '42522000', '44500000', '49000000', '56025000', '69500002')) and SAWITH5.c2 >= SAWITH2.c2 )
group by T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, SAWITH5.c3, SAWITH5.c4
) D1
where ( D1.c6 = 1 ) ),
SAWITH7 AS (select T156337.ROW_WID as c3,
T156337.MCAL_YEAR_WID as c4,
ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_YEAR_WID ORDER BY T156337.MCAL_YEAR_WID DESC) as c5,
T156337.MCAL_PERIOD_WID as c6,
ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_YEAR_WID, T156337.MCAL_PERIOD_WID ORDER BY T156337.MCAL_YEAR_WID DESC, T156337.MCAL_PERIOD_WID DESC) as c7
from
W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */
where ( T156337.MCAL_CAL_NAME = 'AHS Accounting' ) ),
SAWITH8 AS (select Case when case SAWITH7.c5 when 1 then SAWITH7.c3 else NULL end is not null then Rank() OVER ( ORDER BY case SAWITH7.c5 when 1 then SAWITH7.c3 else NULL end ASC NULLS LAST ) end as c1,
Case when case SAWITH7.c7 when 1 then SAWITH7.c3 else NULL end is not null then Rank() OVER ( PARTITION BY SAWITH7.c4 ORDER BY case SAWITH7.c7 when 1 then SAWITH7.c3 else NULL end ASC NULLS LAST ) end as c2,
SAWITH7.c3 as c3,
SAWITH7.c4 as c4,
SAWITH7.c6 as c5
from
SAWITH7),
SAWITH9 AS (select distinct min(SAWITH8.c1) over (partition by SAWITH8.c4) as c1,
min(SAWITH8.c2) over (partition by SAWITH8.c4, SAWITH8.c5) as c2,
SAWITH8.c3 as c3
from
SAWITH8),
SAWITH10 AS (select T156337.MCAL_QTR_WID as c3,
T156337.MCAL_PERIOD_NAME as c4,
T156337.MCAL_YEAR_WID as c5,
T156337.ROW_WID as c6,
ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_YEAR_WID ORDER BY T156337.MCAL_YEAR_WID DESC) as c7,
T156337.MCAL_PERIOD_WID as c8,
ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_YEAR_WID, T156337.MCAL_PERIOD_WID ORDER BY T156337.MCAL_YEAR_WID DESC, T156337.MCAL_PERIOD_WID DESC) as c9
from
W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */
where ( T156337.MCAL_CAL_NAME = 'AHS Accounting' ) ),
SAWITH11 AS (select Case when case SAWITH10.c7 when 1 then SAWITH10.c6 else NULL end is not null then Rank() OVER ( ORDER BY case SAWITH10.c7 when 1 then SAWITH10.c6 else NULL end ASC NULLS LAST ) end as c1,
Case when case SAWITH10.c9 when 1 then SAWITH10.c6 else NULL end is not null then Rank() OVER ( PARTITION BY SAWITH10.c5 ORDER BY case SAWITH10.c9 when 1 then SAWITH10.c6 else NULL end ASC NULLS LAST ) end as c2,
SAWITH10.c3 as c3,
SAWITH10.c4 as c4,
SAWITH10.c5 as c5,
SAWITH10.c8 as c6
from
SAWITH10),
SAWITH12 AS (select distinct min(SAWITH11.c1) over (partition by SAWITH11.c5) as c1,
min(SAWITH11.c2) over (partition by SAWITH11.c5, SAWITH11.c6) as c2,
SAWITH11.c3 as c3,
SAWITH11.c4 as c4,
SAWITH11.c5 as c5
from
SAWITH11),
SAWITH13 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5
from
(select sum(case when T91707.OTHER_DOC_AMT is null then 0 else T91707.OTHER_DOC_AMT * T91707.GLOBAL1_EXCHANGE_RATE end ) as c1,
T91397.GL_ACCOUNT_NUM as c2,
T91397.GL_ACCOUNT_NAME as c3,
SAWITH12.c5 as c4,
SAWITH12.c3 as c5,
ROW_NUMBER() OVER (PARTITION BY T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, SAWITH12.c3 ORDER BY T91397.GL_ACCOUNT_NUM ASC, T91397.GL_ACCOUNT_NAME ASC, SAWITH12.c3 ASC) as c6
from
W_LEDGER_D T146058 /* Dim_W_LEDGER_D */ ,
W_GL_ACCOUNT_D T91397 /* Dim_W_GL_ACCOUNT_D */ ,
W_GL_OTHER_F T91707 /* Fact_W_GL_OTHER_F */ ,
W_STATUS_D T96094 /* Dim_W_STATUS_D_Generic */ ,
SAWITH9,
SAWITH12
where ( T91397.ROW_WID = T91707.GL_ACCOUNT_WID and T91707.DOC_STATUS_WID = T96094.ROW_WID and T91707.ACCT_PERIOD_END_DT_WID = SAWITH9.c3 and T91707.LEDGER_WID = T146058.ROW_WID and SAWITH12.c1 = SAWITH9.c1 and T91707.DELETE_FLG = 'N' and T91707.X_CURRENCY_CODE = 'CAD' and T146058.LEDGER_NAME = 'AHS Ledger' and SAWITH12.c4 = 'NOV-09' and (T96094.W_STATUS_CODE in ('POSTED', 'REVERSED')) and (T91397.GL_ACCOUNT_NUM in ('14050003', '42522000', '44500000', '49000000', '56025000', '69500002')) and SAWITH12.c2 >= SAWITH9.c2 )
group by T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, SAWITH12.c3, SAWITH12.c5
) D1
where ( D1.c6 = 1 ) ),
SAWITH14 AS (select T156337.ROW_WID as c3,
T156337.MCAL_QTR_WID as c4,
ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_QTR_WID ORDER BY T156337.MCAL_QTR_WID DESC) as c5,
T156337.MCAL_PERIOD_WID as c6,
ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_QTR_WID, T156337.MCAL_PERIOD_WID ORDER BY T156337.MCAL_QTR_WID DESC, T156337.MCAL_PERIOD_WID DESC) as c7
from
W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */
where ( T156337.MCAL_CAL_NAME = 'AHS Accounting' ) ),
SAWITH15 AS (select Case when case SAWITH14.c5 when 1 then SAWITH14.c3 else NULL end is not null then Rank() OVER ( ORDER BY case SAWITH14.c5 when 1 then SAWITH14.c3 else NULL end ASC NULLS LAST ) end as c1,
Case when case SAWITH14.c7 when 1 then SAWITH14.c3 else NULL end is not null then Rank() OVER ( PARTITION BY SAWITH14.c4 ORDER BY case SAWITH14.c7 when 1 then SAWITH14.c3 else NULL end ASC NULLS LAST ) end as c2,
SAWITH14.c3 as c3,
SAWITH14.c4 as c4,
SAWITH14.c6 as c5
from
SAWITH14),
SAWITH16 AS (select distinct min(SAWITH15.c1) over (partition by SAWITH15.c4) as c1,
min(SAWITH15.c2) over (partition by SAWITH15.c4, SAWITH15.c5) as c2,
SAWITH15.c3 as c3
from
SAWITH15),
SAWITH17 AS (select T156337.MCAL_QTR_WID as c3,
T156337.MCAL_PERIOD_NAME as c4,
T156337.MCAL_YEAR_WID as c5,
T156337.ROW_WID as c6,
ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_QTR_WID ORDER BY T156337.MCAL_QTR_WID DESC) as c7,
T156337.MCAL_PERIOD_WID as c8,
ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_QTR_WID, T156337.MCAL_PERIOD_WID ORDER BY T156337.MCAL_QTR_WID DESC, T156337.MCAL_PERIOD_WID DESC) as c9
from
W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */
where ( T156337.MCAL_CAL_NAME = 'AHS Accounting' ) ),
SAWITH18 AS (select Case when case SAWITH17.c7 when 1 then SAWITH17.c6 else NULL end is not null then Rank() OVER ( ORDER BY case SAWITH17.c7 when 1 then SAWITH17.c6 else NULL end ASC NULLS LAST ) end as c1,
Case when case SAWITH17.c9 when 1 then SAWITH17.c6 else NULL end is not null then Rank() OVER ( PARTITION BY SAWITH17.c3 ORDER BY case SAWITH17.c9 when 1 then SAWITH17.c6 else NULL end ASC NULLS LAST ) end as c2,
SAWITH17.c3 as c3,
SAWITH17.c4 as c4,
SAWITH17.c5 as c5,
SAWITH17.c8 as c6
from
SAWITH17),
SAWITH19 AS (select distinct min(SAWITH18.c1) over (partition by SAWITH18.c3) as c1,
min(SAWITH18.c2) over (partition by SAWITH18.c3, SAWITH18.c6) as c2,
SAWITH18.c3 as c3,
SAWITH18.c4 as c4,
SAWITH18.c5 as c5
from
SAWITH18),
SAWITH20 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5
from
(select sum(T146219.BUDGET_LOC_AMT * T146219.GLOBAL1_EXCHANGE_RATE) as c1,
T91397.GL_ACCOUNT_NUM as c2,
T91397.GL_ACCOUNT_NAME as c3,
SAWITH19.c5 as c4,
SAWITH19.c3 as c5,
ROW_NUMBER() OVER (PARTITION BY T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, SAWITH19.c3 ORDER BY T91397.GL_ACCOUNT_NUM ASC, T91397.GL_ACCOUNT_NAME ASC, SAWITH19.c3 ASC) as c6
from
W_LEDGER_D T146058 /* Dim_W_LEDGER_D */ ,
W_GL_ACCOUNT_D T91397 /* Dim_W_GL_ACCOUNT_D */ ,
W_BUDGET_D T146170 /* Dim_W_BUDGET_D */ ,
W_ACCT_BUDGET_F T146219 /* Fact_W_ACCT_BUDGET_F */ ,
SAWITH16,
SAWITH19
where ( T91397.ROW_WID = T146219.GL_ACCOUNT_WID and T146058.ROW_WID = T146219.LEDGER_WID and T146170.ROW_WID = T146219.BUDGET_WID and T146219.PERIOD_END_DT_WID = SAWITH16.c3 and SAWITH19.c1 = SAWITH16.c1 and T146058.LEDGER_NAME = 'AHS Ledger' and T146219.X_ACCT_CURRENCY_CODE = 'CAD' and SAWITH19.c4 = 'NOV-09' and T146170.APPLICATION_SOURCE <> 'GL_PSFT_STD' and (T91397.GL_ACCOUNT_NUM in ('14050003', '42522000', '44500000', '49000000', '56025000', '69500002')) and SAWITH19.c2 >= SAWITH16.c2 )
group by T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, SAWITH19.c3, SAWITH19.c5
) D1
where ( D1.c6 = 1 ) ),
SAWITH21 AS (select T156337.ROW_WID as c3,
T156337.MCAL_QTR_WID as c4,
ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_QTR_WID ORDER BY T156337.MCAL_QTR_WID DESC) as c5,
T156337.MCAL_PERIOD_WID as c6,
ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_QTR_WID, T156337.MCAL_PERIOD_WID ORDER BY T156337.MCAL_QTR_WID DESC, T156337.MCAL_PERIOD_WID DESC) as c7
from
W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */
where ( T156337.MCAL_CAL_NAME = 'AHS Accounting' ) ),
SAWITH22 AS (select Case when case SAWITH21.c5 when 1 then SAWITH21.c3 else NULL end is not null then Rank() OVER ( ORDER BY case SAWITH21.c5 when 1 then SAWITH21.c3 else NULL end ASC NULLS LAST ) end as c1,
Case when case SAWITH21.c7 when 1 then SAWITH21.c3 else NULL end is not null then Rank() OVER ( PARTITION BY SAWITH21.c4 ORDER BY case SAWITH21.c7 when 1 then SAWITH21.c3 else NULL end ASC NULLS LAST ) end as c2,
SAWITH21.c3 as c3,
SAWITH21.c4 as c4,
SAWITH21.c6 as c5
from
SAWITH21),
SAWITH23 AS (select distinct min(SAWITH22.c1) over (partition by SAWITH22.c4) as c1,
min(SAWITH22.c2) over (partition by SAWITH22.c4, SAWITH22.c5) as c2,
SAWITH22.c3 as c3
from
SAWITH22),
SAWITH24 AS (select T156337.MCAL_QTR_WID as c3,
T156337.MCAL_YEAR_WID as c4,
T156337.MCAL_PERIOD_NAME as c5,
T156337.ROW_WID as c6,
ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_QTR_WID ORDER BY T156337.MCAL_QTR_WID DESC) as c7,
T156337.MCAL_PERIOD_WID as c8,
ROW_NUMBER() OVER (PARTITION BY T156337.MCAL_QTR_WID, T156337.MCAL_PERIOD_WID ORDER BY T156337.MCAL_QTR_WID DESC, T156337.MCAL_PERIOD_WID DESC) as c9
from
W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */
where ( T156337.MCAL_CAL_NAME = 'AHS Accounting' ) ),
SAWITH25 AS (select Case when case SAWITH24.c7 when 1 then SAWITH24.c6 else NULL end is not null then Rank() OVER ( ORDER BY case SAWITH24.c7 when 1 then SAWITH24.c6 else NULL end ASC NULLS LAST ) end as c1,
Case when case SAWITH24.c9 when 1 then SAWITH24.c6 else NULL end is not null then Rank() OVER ( PARTITION BY SAWITH24.c3 ORDER BY case SAWITH24.c9 when 1 then SAWITH24.c6 else NULL end ASC NULLS LAST ) end as c2,
SAWITH24.c3 as c3,
SAWITH24.c4 as c4,
SAWITH24.c5 as c5,
SAWITH24.c8 as c6
from
SAWITH24),
SAWITH26 AS (select distinct min(SAWITH25.c1) over (partition by SAWITH25.c3) as c1,
min(SAWITH25.c2) over (partition by SAWITH25.c3, SAWITH25.c6) as c2,
SAWITH25.c3 as c3,
SAWITH25.c4 as c4,
SAWITH25.c5 as c5
from
SAWITH25),
SAWITH27 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5
from
(select sum(case when T91707.OTHER_DOC_AMT is null then 0 else T91707.OTHER_DOC_AMT * T91707.GLOBAL1_EXCHANGE_RATE end ) as c1,
T91397.GL_ACCOUNT_NUM as c2,
T91397.GL_ACCOUNT_NAME as c3,
SAWITH26.c4 as c4,
SAWITH26.c3 as c5,
ROW_NUMBER() OVER (PARTITION BY T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, SAWITH26.c3 ORDER BY T91397.GL_ACCOUNT_NUM ASC, T91397.GL_ACCOUNT_NAME ASC, SAWITH26.c3 ASC) as c6
from
W_LEDGER_D T146058 /* Dim_W_LEDGER_D */ ,
W_GL_ACCOUNT_D T91397 /* Dim_W_GL_ACCOUNT_D */ ,
W_GL_OTHER_F T91707 /* Fact_W_GL_OTHER_F */ ,
W_STATUS_D T96094 /* Dim_W_STATUS_D_Generic */ ,
SAWITH23,
SAWITH26
where ( T91397.ROW_WID = T91707.GL_ACCOUNT_WID and T91707.DOC_STATUS_WID = T96094.ROW_WID and T91707.ACCT_PERIOD_END_DT_WID = SAWITH23.c3 and T91707.LEDGER_WID = T146058.ROW_WID and SAWITH26.c1 = SAWITH23.c1 and T91707.DELETE_FLG = 'N' and T91707.X_CURRENCY_CODE = 'CAD' and T146058.LEDGER_NAME = 'AHS Ledger' and SAWITH26.c5 = 'NOV-09' and (T96094.W_STATUS_CODE in ('POSTED', 'REVERSED')) and (T91397.GL_ACCOUNT_NUM in ('14050003', '42522000', '44500000', '49000000', '56025000', '69500002')) and SAWITH26.c2 >= SAWITH23.c2 )
group by T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, SAWITH26.c3, SAWITH26.c4
) D1
where ( D1.c6 = 1 ) ),
SAWITH28 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5
from
(select sum(T146219.BUDGET_LOC_AMT * T146219.GLOBAL1_EXCHANGE_RATE) as c1,
T91397.GL_ACCOUNT_NUM as c2,
T91397.GL_ACCOUNT_NAME as c3,
T156337.MCAL_YEAR_WID as c4,
T156337.MCAL_QTR_WID as c5,
ROW_NUMBER() OVER (PARTITION BY T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, T156337.MCAL_QTR_WID ORDER BY T91397.GL_ACCOUNT_NUM ASC, T91397.GL_ACCOUNT_NAME ASC, T156337.MCAL_QTR_WID ASC) as c6
from
W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */ ,
W_LEDGER_D T146058 /* Dim_W_LEDGER_D */ ,
W_GL_ACCOUNT_D T91397 /* Dim_W_GL_ACCOUNT_D */ ,
W_BUDGET_D T146170 /* Dim_W_BUDGET_D */ ,
W_ACCT_BUDGET_F T146219 /* Fact_W_ACCT_BUDGET_F */
where ( T91397.ROW_WID = T146219.GL_ACCOUNT_WID and T146058.ROW_WID = T146219.LEDGER_WID and T146170.ROW_WID = T146219.BUDGET_WID and T146058.LEDGER_NAME = 'AHS Ledger' and T146219.PERIOD_END_DT_WID = T156337.ROW_WID and T146219.X_ACCT_CURRENCY_CODE = 'CAD' and T156337.MCAL_CAL_NAME = 'AHS Accounting' and T156337.MCAL_PERIOD_NAME = 'NOV-09' and (T91397.GL_ACCOUNT_NUM in ('14050003', '42522000', '44500000', '49000000', '56025000', '69500002')) and T146170.APPLICATION_SOURCE <> 'GL_PSFT_STD' )
group by T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, T156337.MCAL_QTR_WID, T156337.MCAL_YEAR_WID
) D1
where ( D1.c6 = 1 ) ),
SAWITH29 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5
from
(select sum(case when T91707.OTHER_DOC_AMT is null then 0 else T91707.OTHER_DOC_AMT * T91707.GLOBAL1_EXCHANGE_RATE end ) as c1,
T91397.GL_ACCOUNT_NUM as c2,
T91397.GL_ACCOUNT_NAME as c3,
T156337.MCAL_YEAR_WID as c4,
T156337.MCAL_QTR_WID as c5,
ROW_NUMBER() OVER (PARTITION BY T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, T156337.MCAL_QTR_WID ORDER BY T91397.GL_ACCOUNT_NUM ASC, T91397.GL_ACCOUNT_NAME ASC, T156337.MCAL_QTR_WID ASC) as c6
from
W_MCAL_DAY_D T156337 /* Dim_W_MCAL_DAY_D_Fiscal_Day */ ,
W_LEDGER_D T146058 /* Dim_W_LEDGER_D */ ,
W_GL_ACCOUNT_D T91397 /* Dim_W_GL_ACCOUNT_D */ ,
W_GL_OTHER_F T91707 /* Fact_W_GL_OTHER_F */ ,
W_STATUS_D T96094 /* Dim_W_STATUS_D_Generic */
where ( T91397.ROW_WID = T91707.GL_ACCOUNT_WID and T91707.DOC_STATUS_WID = T96094.ROW_WID and T91707.ACCT_PERIOD_END_DT_WID = T156337.ROW_WID and T91707.DELETE_FLG = 'N' and T91707.LEDGER_WID = T146058.ROW_WID and T91707.X_CURRENCY_CODE = 'CAD' and T146058.LEDGER_NAME = 'AHS Ledger' and T156337.MCAL_CAL_NAME = 'AHS Accounting' and T156337.MCAL_PERIOD_NAME = 'NOV-09' and (T96094.W_STATUS_CODE in ('POSTED', 'REVERSED')) and (T91397.GL_ACCOUNT_NUM in ('14050003', '42522000', '44500000', '49000000', '56025000', '69500002')) )
group by T91397.GL_ACCOUNT_NUM, T91397.GL_ACCOUNT_NAME, T156337.MCAL_QTR_WID, T156337.MCAL_YEAR_WID
) D1
where ( D1.c6 = 1 ) ),
SAWITH30 AS (select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3,
D1.c4 as c4,
D1.c5 as c5,
D1.c6 as c6,
D1.c7 as c7,
D1.c8 as c8,
D1.c9 as c9,
D1.c10 as c10,
D1.c11 as c11,
D1.c12 as c12,
D1.c13 as c13
from
(select case when SAWITH29.c2 is not null then SAWITH29.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH28.c2 is not null then SAWITH28.c2 when SAWITH27.c2 is not null then SAWITH27.c2 when SAWITH20.c2 is not null then SAWITH20.c2 end as c1,
case when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH28.c3 is not null then SAWITH28.c3 when SAWITH27.c3 is not null then SAWITH27.c3 when SAWITH20.c3 is not null then SAWITH20.c3 when SAWITH13.c3 is not null then SAWITH13.c3 when SAWITH29.c3 is not null then SAWITH29.c3 end as c2,
SAWITH29.c1 as c3,
SAWITH28.c1 as c4,
SAWITH28.c1 - SAWITH29.c1 as c5,
SAWITH27.c1 as c6,
SAWITH20.c1 as c7,
SAWITH20.c1 - SAWITH27.c1 as c8,
SAWITH13.c1 as c9,
SAWITH6.c1 as c10,
SAWITH6.c1 - SAWITH13.c1 as c11,
case when SAWITH27.c4 is not null then SAWITH27.c4 when SAWITH28.c4 is not null then SAWITH28.c4 when SAWITH6.c4 is not null then SAWITH6.c4 when SAWITH29.c4 is not null then SAWITH29.c4 when SAWITH20.c4 is not null then SAWITH20.c4 when SAWITH13.c4 is not null then SAWITH13.c4 end as c12,
case when SAWITH28.c5 is not null then SAWITH28.c5 when SAWITH6.c5 is not null then SAWITH6.c5 when SAWITH29.c5 is not null then SAWITH29.c5 when SAWITH27.c5 is not null then SAWITH27.c5 when SAWITH20.c5 is not null then SAWITH20.c5 when SAWITH13.c5 is not null then SAWITH13.c5 end as c13,
ROW_NUMBER() OVER (PARTITION BY case when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH28.c3 is not null then SAWITH28.c3 when SAWITH27.c3 is not null then SAWITH27.c3 when SAWITH20.c3 is not null then SAWITH20.c3 when SAWITH13.c3 is not null then SAWITH13.c3 when SAWITH29.c3 is not null then SAWITH29.c3 end , case when SAWITH27.c4 is not null then SAWITH27.c4 when SAWITH28.c4 is not null then SAWITH28.c4 when SAWITH6.c4 is not null then SAWITH6.c4 when SAWITH29.c4 is not null then SAWITH29.c4 when SAWITH20.c4 is not null then SAWITH20.c4 when SAWITH13.c4 is not null then SAWITH13.c4 end , case when SAWITH28.c5 is not null then SAWITH28.c5 when SAWITH6.c5 is not null then SAWITH6.c5 when SAWITH29.c5 is not null then SAWITH29.c5 when SAWITH27.c5 is not null then SAWITH27.c5 when SAWITH20.c5 is not null then SAWITH20.c5 when SAWITH13.c5 is not null then SAWITH13.c5 end , case when SAWITH29.c2 is not null then SAWITH29.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH28.c2 is not null then SAWITH28.c2 when SAWITH27.c2 is not null then SAWITH27.c2 when SAWITH20.c2 is not null then SAWITH20.c2 end ORDER BY case when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH28.c3 is not null then SAWITH28.c3 when SAWITH27.c3 is not null then SAWITH27.c3 when SAWITH20.c3 is not null then SAWITH20.c3 when SAWITH13.c3 is not null then SAWITH13.c3 when SAWITH29.c3 is not null then SAWITH29.c3 end ASC, case when SAWITH27.c4 is not null then SAWITH27.c4 when SAWITH28.c4 is not null then SAWITH28.c4 when SAWITH6.c4 is not null then SAWITH6.c4 when SAWITH29.c4 is not null then SAWITH29.c4 when SAWITH20.c4 is not null then SAWITH20.c4 when SAWITH13.c4 is not null then SAWITH13.c4 end ASC, case when SAWITH28.c5 is not null then SAWITH28.c5 when SAWITH6.c5 is not null then SAWITH6.c5 when SAWITH29.c5 is not null then SAWITH29.c5 when SAWITH27.c5 is not null then SAWITH27.c5 when SAWITH20.c5 is not null then SAWITH20.c5 when SAWITH13.c5 is not null then SAWITH13.c5 end ASC, case when SAWITH29.c2 is not null then SAWITH29.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH28.c2 is not null then SAWITH28.c2 when SAWITH27.c2 is not null then SAWITH27.c2 when SAWITH20.c2 is not null then SAWITH20.c2 end ASC) as c14
from
(
(
(
(
SAWITH6 full outer join SAWITH13 On nvl(SAWITH6.c5 , 88.0) = nvl(SAWITH13.c5 , 88.0) and nvl(SAWITH6.c5 , 99.0) = nvl(SAWITH13.c5 , 99.0) and nvl(SAWITH6.c2 , 'q') = nvl(SAWITH13.c2 , 'q') and nvl(SAWITH6.c2 , 'z') = nvl(SAWITH13.c2 , 'z') and nvl(SAWITH6.c3 , 'q') = nvl(SAWITH13.c3 , 'q') and nvl(SAWITH6.c3 , 'z') = nvl(SAWITH13.c3 , 'z')) full outer join SAWITH20 On nvl(SAWITH20.c2 , 'q') = nvl(case when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH13.c2 is not null then SAWITH13.c2 end , 'q') and nvl(SAWITH20.c2 , 'z') = nvl(case when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH13.c2 is not null then SAWITH13.c2 end , 'z') and nvl(SAWITH20.c3 , 'q') = nvl(case when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH13.c3 is not null then SAWITH13.c3 end , 'q') and nvl(SAWITH20.c3 , 'z') = nvl(case when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH13.c3 is not null then SAWITH13.c3 end , 'z') and nvl(SAWITH20.c5 , 88.0) = nvl(case when SAWITH6.c5 is not null then SAWITH6.c5 when SAWITH13.c5 is not null then SAWITH13.c5 end , 88.0) and nvl(SAWITH20.c5 , 99.0) = nvl(case when SAWITH6.c5 is not null then SAWITH6.c5 when SAWITH13.c5 is not null then SAWITH13.c5 end , 99.0)) full outer join SAWITH27 On nvl(SAWITH27.c2 , 'q') = nvl(case when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH20.c2 is not null then SAWITH20.c2 end , 'q') and nvl(SAWITH27.c2 , 'z') = nvl(case when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH20.c2 is not null then SAWITH20.c2 end , 'z') and nvl(SAWITH27.c3 , 'q') = nvl(case when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH13.c3 is not null then SAWITH13.c3 when SAWITH20.c3 is not null then SAWITH20.c3 end , 'q') and nvl(SAWITH27.c3 , 'z') = nvl(case when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH13.c3 is not null then SAWITH13.c3 when SAWITH20.c3 is not null then SAWITH20.c3 end , 'z') and nvl(SAWITH27.c5 , 88.0) = nvl(case when SAWITH6.c5 is not null then SAWITH6.c5 when SAWITH13.c5 is not null then SAWITH13.c5 when SAWITH20.c5 is not null then SAWITH20.c5 end , 88.0) and nvl(SAWITH27.c5 , 99.0) = nvl(case when SAWITH6.c5 is not null then SAWITH6.c5 when SAWITH13.c5 is not null then SAWITH13.c5 when SAWITH20.c5 is not null then SAWITH20.c5 end , 99.0)) full outer join SAWITH28 On nvl(SAWITH28.c2 , 'q') = nvl(case when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH20.c2 is not null then SAWITH20.c2 when SAWITH27.c2 is not null then SAWITH27.c2 end , 'q') and nvl(SAWITH28.c2 , 'z') = nvl(case when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH20.c2 is not null then SAWITH20.c2 when SAWITH27.c2 is not null then SAWITH27.c2 end , 'z') and nvl(SAWITH28.c3 , 'q') = nvl(case when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH13.c3 is not null then SAWITH13.c3 when SAWITH20.c3 is not null then SAWITH20.c3 when SAWITH27.c3 is not null then SAWITH27.c3 end , 'q') and nvl(SAWITH28.c3 , 'z') = nvl(case when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH13.c3 is not null then SAWITH13.c3 when SAWITH20.c3 is not null then SAWITH20.c3 when SAWITH27.c3 is not null then SAWITH27.c3 end , 'z') and nvl(SAWITH28.c5 , 88.0) = nvl(case when SAWITH6.c5 is not null then SAWITH6.c5 when SAWITH13.c5 is not null then SAWITH13.c5 when SAWITH20.c5 is not null then SAWITH20.c5 when SAWITH27.c5 is not null then SAWITH27.c5 end , 88.0) and nvl(SAWITH28.c5 , 99.0) = nvl(case when SAWITH6.c5 is not null then SAWITH6.c5 when SAWITH13.c5 is not null then SAWITH13.c5 when SAWITH20.c5 is not null then SAWITH20.c5 when SAWITH27.c5 is not null then SAWITH27.c5 end , 99.0)) full outer join SAWITH29 On nvl(SAWITH29.c2 , 'q') = nvl(case when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH20.c2 is not null then SAWITH20.c2 when SAWITH27.c2 is not null then SAWITH27.c2 when SAWITH28.c2 is not null then SAWITH28.c2 end , 'q') and nvl(SAWITH29.c2 , 'z') = nvl(case when SAWITH6.c2 is not null then SAWITH6.c2 when SAWITH13.c2 is not null then SAWITH13.c2 when SAWITH20.c2 is not null then SAWITH20.c2 when SAWITH27.c2 is not null then SAWITH27.c2 when SAWITH28.c2 is not null then SAWITH28.c2 end , 'z') and nvl(SAWITH29.c3 , 'q') = nvl(case when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH13.c3 is not null then SAWITH13.c3 when SAWITH20.c3 is not null then SAWITH20.c3 when SAWITH27.c3 is not null then SAWITH27.c3 when SAWITH28.c3 is not null then SAWITH28.c3 end , 'q') and nvl(SAWITH29.c3 , 'z') = nvl(case when SAWITH6.c3 is not null then SAWITH6.c3 when SAWITH13.c3 is not null then SAWITH13.c3 when SAWITH20.c3 is not null then SAWITH20.c3 when SAWITH27.c3 is not null then SAWITH27.c3 when SAWITH28.c3 is not null then SAWITH28.c3 end , 'z') and nvl(SAWITH29.c5 , 88.0) = nvl(case when SAWITH6.c5 is not null then SAWITH6.c5 when SAWITH13.c5 is not null then SAWITH13.c5 when SAWITH20.c5 is not null then SAWITH20.c5 when SAWITH27.c5 is not null then SAWITH27.c5 when SAWITH28.c5 is not null then SAWITH28.c5 end , 88.0) and nvl(SAWITH29.c5 , 99.0) = nvl(case when SAWITH6.c5 is not null then SAWITH6.c5 when SAWITH13.c5 is not null then SAWITH13.c5 when SAWITH20.c5 is not null then SAWITH20.c5 when SAWITH27.c5 is not null then SAWITH27.c5 when SAWITH28.c5 is not null then SAWITH28.c5 end , 99.0)
) D1
where ( D1.c14 = 1 ) )
select SAWITH30.c1 as c1,
SAWITH30.c2 as c2,
SAWITH30.c3 as c3,
SAWITH30.c4 as c4,
SAWITH30.c5 as c5,
SAWITH30.c6 as c6,
SAWITH30.c7 as c7,
SAWITH30.c8 as c8,
SAWITH30.c9 as c9,
SAWITH30.c10 as c10,
SAWITH30.c11 as c11,
SAWITH30.c12 as c12,
SAWITH30.c13 as c13
from
SAWITH30
order by c1, c2
|
|
|
|
|
Re: Problem with query [message #484606 is a reply to message #484542] |
Wed, 01 December 2010 23:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136607.jpg) |
ramoradba
Messages: 2456 Registered: January 2009 Location: AndhraPradesh,Hyderabad,I...
|
Senior Member |
|
|
1) save it in a file and execute it
2) Get the explain plan
And paste here Before that read the performance tuning guide for the requirements
Sriram
|
|
|
Goto Forum:
Current Time: Sat Sep 07 19:57:14 CDT 2024
|