OraFAQ Forum: Performance Tuning » Heavy query with GL

Home » RDBMS Server » Performance Tuning » Heavy query with GL

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

Heavy query with GL [message #268668]

Wed, 19 September 2007 05:39

tarmenel
Messages: 63
Registered: February 2007
Location: Israel

Member

Hi there,

I'm posting this here because this is more SQL related than E-Biz suite related. I'm trying to query the General Ledger tables but it is extremely heavy and not sure if there's something in my query that may be a problem. What I need to do is take two sets of books in the organization and compare them against each other on one line for each line, please could someone take a look and see if I could optimize it:

Select je_headers.Name,
       je_headers.doc_sequence_value doc_num,
       je_headers.period_name,
       gcc.concatenated_segments accnt1001, -- jel1001.code_combination_id,
       je_headers.currency_code,
       Sum(nvl(jel1001.entered_dr, 0)) - Sum(nvl(jel1001.entered_cr, 0)) entered1001,
       Sum(nvl(jel1001.accounted_dr, 0)) - Sum(nvl(jel1001.accounted_cr, 0)) accounted1001,
       gcc2009.concatenated_segments accnt2009,
       Sum(nvl(jel2009.entered_dr, 0)) - Sum(nvl(jel2009.entered_cr, 0)) entered2009,
       Sum(nvl(jel2009.accounted_dr, 0)) - Sum(nvl(jel2009.accounted_cr, 0)) accounted2009,
       decode(gcc.code_combination_id-gcc2009.code_combination_id,0,'TRUE','FALSE') SameAcc
From   gl_je_lines jel1001,
       gl_code_combinations_kfv gcc,
       gl_je_lines jel2009,
       gl_code_combinations_kfv gcc2009,
       (Select jeh1001.je_header_id h1001,
               jeh2009.je_header_id h2009,
               jeh1001.Name,
               jeh1001.doc_sequence_value,
               jeh1001.currency_code,
               jeh1001.period_name
        From   gl.gl_je_headers jeh1001, gl.gl_je_headers jeh2009
        Where  jeh2009.set_of_books_id = 2009
        And    jeh2009.parent_je_header_id = jeh1001.je_header_id
              --*******************************
           --   And    jeh1001.Name = 'PLS Cellcom ILS'
        --And    jeh1001.doc_sequence_value = '710104'
        --*******************************
        ) je_headers
Where  jel1001.je_line_num = jel2009.je_line_num
--And    jel1001.code_combination_id = jel2009.code_combination_id
And    jel1001.period_name = jel2009.period_name
And    jel1001.je_header_id = je_headers.h1001
And    jel2009.je_header_id = je_headers.h2009
And    jel1001.code_combination_id = gcc.code_combination_id
And    jel2009.code_combination_id = gcc2009.code_combination_id
Group  By je_headers.Name,
          je_headers.doc_sequence_value,
          jel1001.je_header_id,
          gcc.concatenated_segments,
          je_headers.currency_code,
          je_headers.period_name,
          gcc2009.concatenated_segments,
          gcc.code_combination_id,gcc2009.code_combination_id

Report message to a moderator

Re: Heavy query with GL [message #268792 is a reply to message #268668]

Wed, 19 September 2007 13:55

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

All of us have ERP installed on our PDA's with your indexes,
so we can really fast to recreate the problem without
TKPROF, EXpalin, etc.

Michael

Report message to a moderator

Previous Topic:	Query Tuning Problem (tkprof o/p,query & explain plan attached)
Next Topic:	Tuning of query (Not DBA Level Tuning)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jun 11 12:36:52 CDT 2024