2
Expand|Select|Wrap|Line Numbers
- select gb.SET_OF_BOOKS_ID "SOB_ID"
- , gcc.CODE_COMBINATION_ID "CCID"
- , gp.period_name
- , gb.ACTUAL_FLAG
- , gcc.segment1
- , gcc.segment2
- , gcc.segment3
- , gcc.segment4
- , gcc.segment5
- , gcc.segment6
- , gcc.segment7
- , gcc.segment8
- , gcc.segment9
- , gcc.segment10
- , gcc.segment11
- , gcc.segment12
- , gcc.segment13
- , gcc.segment14
- /*, gcc.segment15
- , gcc.segment16
- , gcc.segment17
- , gcc.segment18
- , gcc.segment19
- , gcc.segment20
- , gcc.segment21
- , gcc.segment22
- , gcc.segment23
- , gcc.segment24
- , gcc.segment25
- , gcc.segment26
- , gcc.segment27
- , gcc.segment28
- , gcc.segment29
- , gcc.segment30*/
- , gb.currency_code
- , gb.period_year
- , gb.period_num
- , (nvl(gb.BEGIN_BALANCE_DR,0)-nvl(gb.BEGIN_BALANCE_CR,0)) Open_Bal
- , nvl(gb.PERIOD_NET_DR,0) Period_Dr
- , nvl(gb.PERIOD_NET_CR,0) Period_Cr
- , (nvl(gb.BEGIN_BALANCE_DR,0)-nvl(gb.BEGIN_BALANCE_CR,0)+nvl(gb.PERIOD_NET_DR,0)-nvl(gb.PERIOD_NET_CR,0)) Close_Bal
- , nvl(gb.BEGIN_BALANCE_DR,0) Open_Bal_Dr
- , nvl(gb.BEGIN_BALANCE_CR,0) Open_Bal_Cr
- , nvl(gb.BEGIN_BALANCE_DR,0) + nvl(gb.PERIOD_NET_DR,0) Close_Bal_Dr
- , nvl(gb.BEGIN_BALANCE_CR,0) + nvl(gb.PERIOD_NET_CR,0) Close_Bal_Cr
- from gl_balances gb
- , gl_code_combinations gcc
- , gl_periods gp
- where gcc.CODE_COMBINATION_ID = gb.CODE_COMBINATION_ID
- and gb.period_year = gp.period_year
- and gb.period_num = gp.period_num
- and gb.period_type = gp.period_type
- and gcc.summary_flag != 'Y'
QUERY LOGIC:-
Query Logic is developed to NET of the balances if the SOURCE is 'Payables' else need not.
MODIFIED QUERY LOGIC:-
Expand|Select|Wrap|Line Numbers
- select /*+ index(gcc GL_CODE_COMBINATIONS_U1) ,
- gl GL_JE_LINES_U1 */
- distinct gb.SET_OF_BOOKS_ID "SOB_ID"
- , gcc.CODE_COMBINATION_ID "CCID"
- , gp.period_name
- , gb.ACTUAL_FLAG
- , gcc.segment1
- , gcc.segment2
- , gcc.segment3
- , gcc.segment4
- , gcc.segment5
- , gcc.segment6
- , gcc.segment7
- , gcc.segment8
- , gcc.segment9
- , gcc.segment10
- , gcc.segment11
- , gcc.segment12
- , gcc.segment13
- , gcc.segment14
- /*, gcc.segment15
- , gcc.segment16
- , gcc.segment17
- , gcc.segment18
- , gcc.segment19
- , gcc.segment20
- , gcc.segment21
- , gcc.segment22
- , gcc.segment23
- , gcc.segment24
- , gcc.segment25
- , gcc.segment26
- , gcc.segment27
- , gcc.segment28
- , gcc.segment29
- , gcc.segment30 */
- , gb.currency_code
- , gb.period_year
- , gb.period_num
- , (nvl(gb.BEGIN_BALANCE_DR,0)-nvl(gb.BEGIN_BALANCE_CR,0)) Open_Bal
- , (nvl(gb.PERIOD_NET_DR,0)) Period_Dr
- , (nvl(gb.PERIOD_NET_CR,0)) Period_Cr
- , (nvl(gb.BEGIN_BALANCE_DR,0)-nvl(gb.BEGIN_BALANCE_CR,0)+nvl(gb.PERIOD_NET_DR,0)-nvl(gb.PERIOD_NET_CR,0)) Close_Bal
- , (nvl(gb.BEGIN_BALANCE_DR,0)) Open_Bal_Dr
- , (nvl(gb.BEGIN_BALANCE_CR,0)) Open_Bal_Cr
- --Added by Altaf 29-NOV-2007
- ,(decode(gh.je_source,'Payables',
- to_number( NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0) + NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0)),
- nvl(gb.BEGIN_BALANCE_DR,0) + nvl(gb.PERIOD_NET_DR,0))) Close_Bal_Dr
- ,(decode(gh.je_source,'Payables',
- DECODE(sign( NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0) + NVL (gb.period_net_dr, 0) - NVL (gb.period_net_cr, 0)),-1,0,0),
- (nvl(gb.BEGIN_BALANCE_CR,0) + nvl(gb.PERIOD_NET_CR,0))) ) Close_Bal_Cr
- --Added by Altaf 29-NOV-2007
- from gl_balances gb
- , gl_code_combinations gcc
- , gl_periods gp
- , gl_je_headers gh
- , gl_je_lines gl
- , gl_sets_of_books gs
- where gcc.CODE_COMBINATION_ID = gb.CODE_COMBINATION_ID
- and gb.period_year = gp.period_year
- and gb.period_num = gp.period_num
- and gb.period_type = gp.period_type
- and gb.CODE_COMBINATION_ID = gl.CODE_COMBINATION_ID
- and gl.je_header_id = gh.je_header_id
- and gl.period_name = gh.period_name
- and gb.SET_OF_BOOKS_ID = gs.SET_OF_BOOKS_ID
- and gb.SET_OF_BOOKS_ID = gl.SET_OF_BOOKS_ID
- and gb.SET_OF_BOOKS_ID = gh.SET_OF_BOOKS_ID
- and gs.SET_OF_BOOKS_ID = 1
- AND GB.PERIOD_YEAR =(select to_char(trunc(add_months(sysdate,-3)),'RRRR') from dual)
- AND GB.period_name = (select to_char(trunc(add_months(sysdate,-3)),'MON-RR') from dual)
I had discussion with the DBA to increase size of TEMPSPACE but i was told it is already 32 GB and cannot be extended more.
In this case what steps i should follow to increase the performance of the modified query so that materialized view gets created or any other steps need to follow.
There are 20000000 [2 cored] records in GL_JE_LINES itself.
STATISTICS GATHERED:- EXPLAIN PLAN
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 19 K 20177
SORT UNIQUE 19 K 3 M 20177
HASH JOIN 19 K 3 M 19589
HASH JOIN 19 K 2 M 18271
TABLE ACCESS FULL GL_PERIODS 920 18 K 5
HASH JOIN 33 K 2 M 18264
TABLE ACCESS BY INDEX ROWID GL_BALANCES 24 K 1 M 10103
INDEX RANGE SCAN GL_BALANCES_N2 1 535
TABLE ACCESS FULL DUAL 1 2
TABLE ACCESS FULL DUAL 1 2
TABLE ACCESS BY INDEX ROWID GL_JE_LINES 739 15 K 2
NESTED LOOPS 2 M 102 M 6671
NESTED LOOPS 3 K 74 K 59
INDEX UNIQUE SCAN GL_SETS_OF_BOOKS_U2 1 3
TABLE ACCESS FULL GL_JE_HEADERS 3 K 64 K 58
INDEX RANGE SCAN GL_JE_LINES_U1 35 K 1
TABLE ACCESS BY INDEX ROWID GL_CODE_COMBINATIONS 302 K 25 M 826
INDEX FULL SCAN GL_CODE_COMBINATIONS_U1 1 26
