CODEWITHSUNDEEP
sqloracle-databasequery-optimization
Muhammad Nadeem
Muhammad Nadeem

Reputation: 366

query optimization required

I have the following query:

select oeh.header_id, oeh.order_number, oeh.ordered_date,oeh.sold_to_org_id as customer_id ,arc.customer_name as customer_name, oeh.INVOICE_TO_ORG_ID
, oel.attribute1 attribute1,  oel.attribute6 attribute6, oel.line_id, oel.line_number, oel.ordered_quantity, disc.wip_entity_id, disc.date_closed, disc.date_released, disc.date_released, disc.date_completed
from (APPS.oe_order_headers_all oeh INNER JOIN APPS.oe_order_lines_all oel
      ON oeh.org_id = oel.org_id -- not indexed
      and oeh.header_id = oel.header_id) -- both indexed
      INNER JOIN APPS.ar_customers arc 
      ON arc.customer_id = oeh.sold_to_org_id -- both indexed
      INNER JOIN XXCUS.xxgex_assemblies asm
      ON oel.line_id = asm.line_id -- BOTH INDEXED
      INNER JOIN APPS.wip_discrete_jobs disc
      ON disc.primary_item_id = asm.inventory_item_id -- both indexed
     where oel.link_to_line_id is null -- indexed
      and oeh.ordered_date > '31-DEC-2013'
      and disc.status_type NOT IN (1,7) -- Not Cancelled and Unreleased )
      and (
          ( disc.status_type in (3,4,6) )
          or
          (  disc.date_completed > TRUNC(SYSDATE) - 400 
             and disc.status_type = 12 -- CLOSED 
          )
          )
    and disc.source_line_id is not null 
    and disc.source_code = 'WICDOL'
    and oeh.order_number between 1400000 and 1420050;

the columns in where clause are mostly indexed, my query returned 7 lines, and the cost in explain plan is 2990.

How can i use NOT EXIST instead of

and disc.status_type NOT IN (1,7)

Any suggestions to optimize it?

Upvotes: 1

Views: 153

Answers (2)

Nick
Nick

Reputation: 21

Seems that your access path either disc.source_code or oeh.order_number, whichever is more selective. Hope one of them is indexed. Also you can add index on org_id, if its more selective than header_id. As @StanislavL said, you can remove disc.status_type NOT IN (1,7).

Upvotes: 1

Jaco B
Jaco B

Reputation: 1043

Suggestions to optimize your query:

  1. Add Index on APPS.oe_order_headers_all.org_id (only this column in index)
  2. Add Index on APPS.wip_discrete_jobs.status_type (only this column in index)
  3. Move and disc.status_type NOT IN (1,7) to be the first condition in the where clause.

I am not sure that NOT EXISTS would be the correct choice here.

Upvotes: 2

Related Questions