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;
/

Open in new window

LVL 6
anumosesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
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/
0
anumosesAuthor Commented:
I do not what the the database to be hit multiple times that slows down the process. Any other method?
0
Geert GOracle dbaCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

anumosesAuthor Commented:
Can you give me the code example from my code that I gave?
0
Mark GeerlingsDatabase AdministratorCommented:
I don't have time to completely re-write your code, but usually a one-step approach is the most-efficient way to get this job done.

That would look something like this:

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)
select [whatever you need]
from [your tables]
where [your conditions are true];
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anumosesAuthor Commented:
Will it be faster to do multiple inserts for various conditions that I have if I follow the same method?
0
slightwv (䄆 Netminder) Commented:
If recshp.dt_created stores the time portion of the dates, you might be missing some values using TO_DATE('31-DEC-2017','dd-MON-yyyy').  using TO_DATE without a TIME portion defaults it to zeros so if a row has a dt_created with 31-DEC-2017 11:30:10 your between will miss it.  It would need to be TO_DATE('31-DEC-2017 23:59:59','dd-MON-yyyy HH24:MI:SS')
0
Mark GeerlingsDatabase AdministratorCommented:
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.
0
anumosesAuthor Commented:
can I use connect by level?
0
Mark GeerlingsDatabase AdministratorCommented:
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.)
0
slightwv (䄆 Netminder) 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?
0
anumosesAuthor Commented:
not the closest answer that I wanted. Had to close this
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.