Reputation: 366
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
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
Reputation: 1043
Suggestions to optimize your query:
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