5 Replies Latest reply: Jan 3, 2013 4:41 PM by William Robertson RSS

    Why is this query running very slow

    user578908
      Hello experts,

      The query below is running super slow (takes about 20-30 mins to run) and I am not sure why. If I remove the "AND journal_line.period_name in ('NOV-12')" from the where clause query runs much faster. Any idea what is happening?
      Please help.

      Select journal_batch1.NAME,
      journal_line.period_name,
      gcc.segment8, gcc.segment1, gcc.segment7, gcc.segment2, journal_line.attribute3,
      sum(NVL (journal_line.entered_dr, 0)
      - NVL (journal_line.entered_cr, 0))
      FROM gl_je_batches journal_batch1,
      gl_je_headers je1,
      gl_ledgers ledger,
      gl_encumbrance_types gl_encumbrance_type,
      gl_budget_versions gl_budget_version,
      gl_je_headers je2,
      gl_je_headers je3,
      gl_je_categories journal_category,
      gl_je_sources journal_source,
      gl_daily_conversion_types gl_conversion_type,
      gl_je_lines journal_line,
      gl_code_combinations gcc,
      gl_periods gp,
      (SELECT pov.vendor_name,
      xte.transaction_number invoice_num,
      TO_CHAR (xal.ae_header_id) ae_header_id,
      TO_CHAR (xal.ae_line_num) ae_line_num
      FROM xla.xla_ae_lines xal,
      po_vendors pov,
      xla.xla_ae_headers xah,
      xla.xla_events xe,
      xla.xla_transaction_entities xte
      WHERE 1 = 1
      AND pov.vendor_id = xal.party_id
      AND xal.application_id = xah.application_id
      AND xal.ae_header_id = xah.ae_header_id
      AND xah.application_id = xe.application_id
      AND xah.event_id = xe.event_id
      AND xte.entity_code = 'AP_INVOICES'
      AND xe.application_id = xte.application_id
      AND xe.entity_id = xte.entity_id
      AND    xal.ae_header_id = 4094066 join from reference field 7
      AND xal.application_id = 200 -- hard coded for ap
      /*AND ae_line_num = 29 join from reference field 8*/
      ) ap_info
      WHERE 1 = 1
      AND (je1.je_batch_id = journal_batch1.je_batch_id)
      AND (je1.encumbrance_type_id = gl_encumbrance_type.encumbrance_type_id(+))
      AND (je1.budget_version_id = gl_budget_version.budget_version_id(+))
      AND je1.ledger_id = ledger.ledger_id
      AND (je1.parent_je_header_id = je2.je_header_id(+))
      AND (je1.reversed_je_header_id = je3.je_header_id(+))
      AND (je1.je_category = journal_category.je_category_name
      )
      AND (je1.je_source = journal_source.je_source_name)
      AND (je1.currency_conversion_type =
      gl_conversion_type.conversion_type
      )
      AND (journal_line.je_header_id = je1.je_header_id)
      AND (journal_line.code_combination_id =
      gcc.code_combination_id
      )
      AND je1.period_name = gp.period_name
      AND journal_line.reference_7 = ap_info.ae_header_id(+)
      AND journal_line.reference_8 = ap_info.ae_line_num(+)
      AND ledger.ledger_id = 1001
      AND journal_line.period_name in ('NOV-12')
      AND gcc.segment8 <> '4777'
      and gcc.segment1 = '67'
      and gcc.segment7 = '1'
      and gcc.segment2 = '25286'
      group by journal_batch1.NAME,
      journal_line.period_name,
      gcc.segment8,
      gcc.segment1, gcc.segment7,
      gcc.segment2, journal_line.attribute3