I am facing an issue, I have some records being imported from Oracle ERP system to my dataware house system, when I run the query alone I am getting the results in less than 5 seconds and its using the index of EPR as expected. But when I add the same query to a INSERT statment its take very long time for the result to come and when I checked the explain plan it seems to be the indexes are not being used.
The query which I used is as below.
INSERT INTO ebi.weekly_report
(week_num, insert_date, Order_source, order_number, line_ssd, line_creation_date, item_number, ordered_quantity,
order_type,line_number,line_status,planner)
SELECT /*+ driving_site(ot, oh, ol, msi, oos) */ 2 lv_week,sysdate,
oos.name AS SOURCE,
oh.ORDER_NUMBER,
ol.SCHEDULE_SHIP_DATE AS LINE_SSD,
ol.CREATION_DATE ORDER_CREATION_DATE,
msi.SEGMENT1 AS ITEM_NUMBER, ol.ORDERED_QUANTITY ,
ot.NAME AS ORDER_TYPE,
ol.LINE_NUMBER||'.'||ol.SHIPMENT_NUMBER ||'.'||ol.OPTION_NUMBER||'.'||ol.COMPONENT_NUMBER as LINE_NUMBER,
ol.FLOW_STATUS_CODE,
(select /*+ driving_site(mp) */ mp.PLANNER_CODE||' '||mp.DESCRIPTION
from apps.MTL_PLANNERS@prod mp
where mp.ORGANIZATION_ID=msi.ORGANIZATION_ID
and mp.PLANNER_CODE=msi.PLANNER_CODE
) PLANNER
FROM
apps.OE_TRANSACTION_TYPES_TL@prod ot,
apps.OE_ORDER_HEADERS_ALL@prod oh,
apps.OE_ORDER_LINES_ALL@prod ol,
apps.MTL_SYSTEM_ITEMS@prod msi,
apps.oe_order_sources@prod oos
WHERE oos.order_source_id = oh.order_source_id
AND oh.OPEN_FLAG='Y'
AND oh.ORDER_TYPE_ID=ot.TRANSACTION_TYPE_ID
and ot.name not in ('INTERCOMPANY_OMAR/COF_MY_ORDER')
and ot.name not like '%RMA%'
and msi.planning_make_buy_code = 1
AND ot.LANGUAGE='US'
and ol.ORDERED_QUANTITY >0
AND ol.HEADER_ID=oh.HEADER_ID
AND ol.ORG_ID=oh.ORG_ID
AND ol.SHIP_FROM_ORG_ID=428
AND ol.FLOW_STATUS_CODE not IN ('CLOSED','CANCELLED')
AND msi.ORGANIZATION_ID=ol.SHIP_FROM_ORG_ID
AND msi.INVENTORY_ITEM_ID=ol.INVENTORY_ITEM_ID
and nvl(ol.schedule_ship_date,sysdate-8)<trunc(sysdate)-7;
I am using Oracle Database version 11g.
Thanks in advance,