troubleshooting Question

oracle database query

Avatar of anumoses
anumosesFlag for United States of America asked on
DatabasesOracle Database
12 Comments2 Solutions116 ViewsLast Modified:
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;
/
ASKER CERTIFIED SOLUTION
Mark Geerlings
Database Administrator, retired

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros