We help IT Professionals succeed at work.

oracle database query

anumoses
anumoses asked
on
112 Views
Last Modified: 2018-02-02
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;
/

Open in new window

Comment
Watch Question

Nitin SontakkeDeveloper
CERTIFIED EXPERT

Commented:
You can use INSERT INTO SELECT instead. I haven't looked very closely at your code, but looks like it is doable pretty easily. Basically get rid of cursors written exclusively for the purpose of inserting records.

http://www.oracletutorial.com/oracle-basics/oracle-insert-into-select/

Author

Commented:
I do not what the the database to be hit multiple times that slows down the process. Any other method?
Geert GOracle dba
CERTIFIED EXPERT
Top Expert 2009

Commented:
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

Author

Commented:
Can you give me the code example from my code that I gave?
Database Administrator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Will it be faster to do multiple inserts for various conditions that I have if I follow the same method?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
can I use connect by level?
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
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.)
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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?

Author

Commented:
not the closest answer that I wanted. Had to close this
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.