oracle database query
I have a script that takes data from tables and loads into a temp table to calculate freight based on order types. eg. Order type (CUST, RO- Repair orders, PO- Purchase Orders, XFER- Transfer) Now there are about 3- 4 insert statements, Data is more that 100,000 lines. It takes about 11- 14 minutes. Is there a better way that the insert happens once ? Need help. Appreciate the help. Any examples will help.
prompt Querying freight ...
SET serveroutput ON SIZE 1000000
DECLARE
CURSOR c_progpcon
IS
SELECT cust_id
,div_no
FROM dss.program_processing_controls;
CURSOR c_custord(in_orgrole_id_customer IN dss.orders.orgrole_id_customer%TYPE)
IS
SELECT id order_id
,order_type
,order_number
,customer_po
FROM dss.orders
WHERE order_type = 'CUST'
AND orgrole_id_customer = in_orgrole_id_customer;
CURSOR c_outbound(in_order_id IN dss.orders.id%TYPE)
IS
SELECT ship.id ship_id
,ship.shipper_no
,shptrk.id shptrk_id
,shptrk.waybill
,shptrk.estimated_freight
,shptrk.actual_freight
,shptrk.dt_created
FROM dss.shipments ship
,dss.shipment_trackings shptrk
WHERE ship.order_id = in_order_id
AND shptrk.ship_id = ship.id
AND shptrk.dt_created BETWEEN TO_DATE('01-JAN-2017','dd-MON-yyyy')
AND TO_DATE('31-DEC-2017','dd-MON-yyyy');
CURSOR c_ordsch(in_order_id IN dss.orders.id%TYPE)
IS
SELECT ordsch.id ordsch_id
FROM dss.orders ord
,dss.ordered_items orditm
,dss.ordered_item_schedules ordsch
WHERE ord.id = in_order_id
AND orditm.order_id = ord.id
AND ordsch.orditm_id = orditm.id;
CURSOR c_inbound(in_orditm_id IN dss.ordered_items.id%TYPE)
IS
SELECT recshp.id recshp_id
,recshp.waybill
,recshp.estimated_freight
,recshp.actual_freight
,recshp.dt_created
FROM dss.built_items bltitm
,dss.received_shipments recshp
WHERE bltitm.orditm_id_rcvd = in_orditm_id
AND recshp.id = bltitm.recshp_id
AND recshp.dt_created BETWEEN TO_DATE('01-JAN-2017','dd-MON-yyyy')
AND TO_DATE('31-DEC-2017','dd-MON-yyyy')
UNION ALL
SELECT recshp.id recshp_id
,recshp.waybill
,recshp.estimated_freight
,recshp.actual_freight
,recshp.dt_created
FROM dss.received_items rcvitm
,dss.received_shipments recshp
WHERE rcvitm.orditm_id_rcvd = in_orditm_id
AND recshp.id = rcvitm.recshp_id
AND recshp.dt_created BETWEEN TO_DATE('01-JAN-2017','dd-MON-yyyy')
AND TO_DATE('31-DEC-2017','dd-MON-yyyy');
v_cust_processed NUMBER := 0;
v_custord_processed NUMBER := 0;
v_orgrole_id_customer dss.org_roles.id%TYPE;
v_estimated_freight_custord adwaram.order_freight.estimated_freight%TYPE;
v_actual_freight_custord adwaram.order_freight.actual_freight%TYPE;
v_orditm_id_core dss.exchange_cores.orditm_id%TYPE;
v_order_id_core dss.orders.id%TYPE;
v_bltitm_id_core dss.po_histories.bltitm_id%TYPE;
v_order_type dss.orders.order_type%TYPE;
v_order_number dss.orders.order_number%TYPE;
v_order_id_xfer dss.orders.id%TYPE;
v_order_id_inbound dss.orders.id%TYPE;
v_orditm_id_po ordered_items.id%TYPE;
BEGIN
FOR c_progpcon_rec IN c_progpcon
LOOP
v_cust_processed := v_cust_processed + 1;
SELECT orgrole_id
INTO v_orgrole_id_customer
FROM dss.customers
WHERE id = c_progpcon_rec.cust_id;
FOR c_custord_rec IN c_custord(v_orgrole_id_customer)
LOOP
v_custord_processed := v_custord_processed + 1;
-- outbound customer order
FOR c_outbound_rec IN c_outbound(c_custord_rec.order_id)
LOOP
INSERT INTO adwaram.order_freight
(order_type
,order_number
,shipper_no
,waybill
,actual_freight
,estimated_freight
,waybill_entered
,order_id
,ship_id
,shptrk_id
,recshp_id
,cust_id
,order_id_cust
,notes
,dt_created)
VALUES
(c_custord_rec.order_type
,c_custord_rec.order_number
,c_outbound_rec.shipper_no
,c_outbound_rec.waybill
,c_outbound_rec.actual_freight
,c_outbound_rec.estimated_freight
,c_outbound_rec.dt_created
,c_custord_rec.order_id
,c_outbound_rec.ship_id
,c_outbound_rec.shptrk_id
,NULL
,c_progpcon_rec.cust_id
,c_custord_rec.order_id
,'OUTBOUND CUST ORDER'
,SYSDATE);
END LOOP;
FOR c_ordsch_rec IN c_ordsch(c_custord_rec.order_id)
LOOP
-- get core
BEGIN
SELECT xccore.orditm_id
,pohist.bltitm_id
INTO v_orditm_id_po
,v_bltitm_id_core
FROM dss.exchange_units xcunit
,dss.exchange_cores xccore
,dss.po_histories pohist
WHERE xcunit.ordsch_id = c_ordsch_rec.ordsch_id
AND xccore.xcitm_id = xcunit.xcitm_id
AND pohist.orditm_id(+) = xccore.orditm_id;
IF v_bltitm_id_core IS NOT NULL
THEN
v_order_id_core := dss.pkg_inven.func_get_order(v_bltitm_id_core
,'ORDER_ID');
v_orditm_id_core := dss.pkg_inven.func_get_order(v_bltitm_id_core
,'ORDITM_ID');
ELSE
v_order_id_core := NULL;
END IF;
IF v_order_id_core IS NOT NULL
THEN
-- outbound order for received core (repair order or customer order)
FOR c_outbound_rec IN c_outbound(v_order_id_core)
LOOP
SELECT order_type
,order_number
INTO v_order_type
,v_order_number
FROM dss.orders
WHERE id = v_order_id_core;
INSERT INTO adwaram.order_freight
(order_type
,order_number
,shipper_no
,waybill
,actual_freight
,estimated_freight
,waybill_entered
,order_id
,ship_id
,shptrk_id
,recshp_id
,cust_id
,order_id_cust
,notes
,dt_created)
VALUES
(v_order_type
,v_order_number
,c_outbound_rec.shipper_no
,c_outbound_rec.waybill
,c_outbound_rec.actual_freight
,c_outbound_rec.estimated_freight
,c_outbound_rec.dt_created
,v_order_id_core
,c_outbound_rec.ship_id
,c_outbound_rec.shptrk_id
,NULL
,c_progpcon_rec.cust_id
,c_custord_rec.order_id
,'OUTBOUND '||v_order_type||' ORDER'
,SYSDATE);
END LOOP;
END IF;
-- xfer related to customer order
BEGIN
SELECT ord.id
INTO v_order_id_xfer
FROM dss.orders ord
,dss.ordered_items orditm
WHERE ord.order_type = 'XFER'
AND ord.div_no = c_progpcon_rec.div_no
AND orditm.order_id = ord.id
AND orditm.customer_po = c_custord_rec.customer_po;
FOR c_outbound_rec IN c_outbound(v_order_id_xfer)
LOOP
SELECT order_type
,order_number
INTO v_order_type
,v_order_number
FROM dss.orders
WHERE id = v_order_id_xfer;
INSERT INTO adwaram.order_freight
(order_type
,order_number
,shipper_no
,waybill
,actual_freight
,estimated_freight
,waybill_entered
,order_id
,ship_id
,shptrk_id
,recshp_id
,cust_id
,order_id_cust
,notes
,dt_created)
VALUES
(v_order_type
,v_order_number
,c_outbound_rec.shipper_no
,c_outbound_rec.waybill
,c_outbound_rec.actual_freight
,c_outbound_rec.estimated_freight
,c_outbound_rec.dt_created
,v_order_id_xfer
,c_outbound_rec.ship_id
,c_outbound_rec.shptrk_id
,NULL
,c_progpcon_rec.cust_id
,c_custord_rec.order_id
,'OUTBOUND '||v_order_type||' ORDER'
,SYSDATE);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
OR TOO_MANY_ROWS
THEN
NULL;
END;
-- inbound orders associate with exchange - v_orditm_id_core (if ro)
-- v_orditm_id_po (csp po)
IF v_orditm_id_core IS NOT NULL
THEN
FOR c_inbound_rec IN c_inbound(v_orditm_id_core)
LOOP
SELECT ord.order_type
,ord.order_number
,ord.id
INTO v_order_type
,v_order_number
,v_order_id_inbound
FROM dss.ordered_items orditm
,dss.orders ord
WHERE orditm.id = v_orditm_id_core
AND ord.id = orditm.order_id;
INSERT INTO adwaram.order_freight
(order_type
,order_number
,shipper_no
,waybill
,actual_freight
,estimated_freight
,waybill_entered
,order_id
,ship_id
,shptrk_id
,recshp_id
,cust_id
,order_id_cust
,notes
,dt_created)
VALUES
(v_order_type
,v_order_number
,NULL
,c_inbound_rec.waybill
,c_inbound_rec.actual_freight
,c_inbound_rec.estimated_freight
,c_inbound_rec.dt_created
,v_order_id_inbound
,NULL
,NULL
,c_inbound_rec.recshp_id
,c_progpcon_rec.cust_id
,c_custord_rec.order_id
,'INBOUND '||v_order_type||' ORDER'
,SYSDATE);
END LOOP;
END IF;
IF v_orditm_id_po IS NOT NULL
THEN
FOR c_inbound_rec IN c_inbound(v_orditm_id_po)
LOOP
SELECT ord.order_type
,ord.order_number
,ord.id
INTO v_order_type
,v_order_number
,v_order_id_inbound
FROM dss.ordered_items orditm
,dss.orders ord
WHERE orditm.id = v_orditm_id_po
AND ord.id = orditm.order_id;
INSERT INTO adwaram.order_freight
(order_type
,order_number
,shipper_no
,waybill
,actual_freight
,estimated_freight
,waybill_entered
,order_id
,ship_id
,shptrk_id
,recshp_id
,cust_id
,order_id_cust
,notes
,dt_created)
VALUES
(v_order_type
,v_order_number
,NULL
,c_inbound_rec.waybill
,c_inbound_rec.actual_freight
,c_inbound_rec.estimated_freight
,c_inbound_rec.dt_created
,v_order_id_inbound
,NULL
,NULL
,c_inbound_rec.recshp_id
,c_progpcon_rec.cust_id
,c_custord_rec.order_id
,'INBOUND '||v_order_type||' ORDER'
,SYSDATE);
END LOOP;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
OR TOO_MANY_ROWS
THEN
NULL;
END;
END LOOP;
END LOOP;
END LOOP;
COMMIT;
dbms_output.put_line(TO_CHAR(v_cust_processed)||' customers processed.');
dbms_output.put_line(TO_CHAR(v_custord_processed)||' customer orders processed.');
END;
/
Last Comment
ASKER
I do not what the the database to be hit multiple times that slows down the process. Any other method?
for starters
why do you have selects inside your loops ?
put those in the cursor definition
then you'll only have the inserts
the next step would be to move the cursor into the insert
that'll be easy for the outer loop
from the second, or third level it get's more complicated as you need to encompass the higher level cursors too
why do you have selects inside your loops ?
put those in the cursor definition
then you'll only have the inserts
the next step would be to move the cursor into the insert
that'll be easy for the outer loop
from the second, or third level it get's more complicated as you need to encompass the higher level cursors too
ASKER
Can you give me the code example from my code that I gave?
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Will it be faster to do multiple inserts for various conditions that I have if I follow the same method?
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
If you can do just one insert, that will be the fastest. I did not review your code in detail to understand why you have separate inserts now. But, if you can write case or decode logic to handle multiple conditions in just one statement, that will certainly be the fastest.
ASKER
can I use connect by level?
Yes. (But will that help you in this case? I don't know. I didn't take the time today either to read through exactly what you were querying with the separate queries you have now.)
I also didn't walk through all your code and attempt to figure out what you are doing. From the scan I did, I don't think you can do it all in a single insert but it looks like you might be able to do it in a few insert with selects without all the nested cursor loops.
If it will be faster or not, we have no way of knowing.
>>can I use connect by level?
That is used in hierarchical queries or as a trick to generate rows where there aren't any. I don't see where you have a hierarchical query.
Where do you think it will come in handy?
If it will be faster or not, we have no way of knowing.
>>can I use connect by level?
That is used in hierarchical queries or as a trick to generate rows where there aren't any. I don't see where you have a hierarchical query.
Where do you think it will come in handy?
ASKER
not the closest answer that I wanted. Had to close this
Oracle Database
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.
81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
http://www.oracletutorial.com/oracle-basics/oracle-insert-into-select/