Solved

Improving this Pl/sql

Posted on 2014-03-27
10
435 Views
Last Modified: 2014-03-28
Hi experts.
I need help in how to improve this pl/sql.
Any clues?

CREATE OR REPLACE procedure SYSTEM.dw_service_orders is
   CURSOR c1 is
   SElect * from dw.dw_service_orders@mapdb9 where to_char(transaction_date,'YY')='14';
   c1_rec dw.dw_service_orders%ROWTYPE;
BEGIN
    execute immediate 'alter session enable parallel DML';
   OPEN c1;
   LOOP
    fetch c1 into c1_rec;
    exit when c1%NOTFOUND;
      insert into dw.dw_service_orders values c1_rec;
       if c1%ROWCOUNT > 100
       then
       commit;
       END IF;
     END LOOP;
   CLOSE c1;
   commit;
END;
/
0
Comment
Question by:LindaC
  • 5
  • 4
10 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 480 total points
Comment Utility
Just do one sql, don't chunk it


CREATE OR REPLACE PROCEDURE SYSTEM.dw_service_orders
IS
BEGIN
    EXECUTE IMMEDIATE 'alter session enable parallel DML';

    INSERT INTO dw.dw_service_orders
        SELECT *
          FROM dw.dw_service_orders@mapdb9
         WHERE TO_CHAR(transaction_date, 'YY') = '14';

    COMMIT;
END;
/

you might want to add the /*+ APPEND */ hint to the insert as well if you don't need to worry about reusing empty space
0
 
LVL 8

Author Comment

by:LindaC
Comment Utility
I wanted to commit between any specific number of records because of undo issues and doing other migrations.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
With an INSERT the undo should be minimal unless you have lots of indexes and/or triggers on the insert.
0
 
LVL 8

Author Comment

by:LindaC
Comment Utility
It is a *big* table. Would prefer committing any number of records.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
ok, that's slower and consumes more resources but still possible.
If you want to pursue that then use array processing.

That is instead of fetching one row and writing one row,  fetch 1000 rows and write 1000 rows.

The bigger the better, ideally you'd do all of them in one step but you can make it smaller if you want.

CREATE OR REPLACE PROCEDURE dw_service_orders
IS
    TYPE input_tab IS TABLE OF dw.dw_service_orders%ROWTYPE
        INDEX BY PLS_INTEGER;

    CURSOR cur
    IS
        SELECT *
          FROM dw.dw_service_orders@mapdb9
         WHERE TO_CHAR(transaction_date, 'YY') = '14';

    v_tab input_tab;
BEGIN
    OPEN cur;

    LOOP
        FETCH cur BULK COLLECT INTO v_tab LIMIT 1000;

        EXIT WHEN v_tab.COUNT = 0;

        FORALL i IN v_tab.FIRST .. v_tab.LAST
            INSERT INTO dw.dw_service_orders
                 VALUES v_tab(i);

        COMMIT;
    END LOOP;
END;

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 8

Author Comment

by:LindaC
Comment Utility
But I don't want to SELECT * FROM source_table?  
This is a little bit confusing.  Can it be possible to post the script using my original script?
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
also,  if transaction_date on the source table is an indexed DATE column then you probably want to do something like this for your where clause instead of the to_char thing

WHERE transaction_date >= trunc(sysdate,'yyyy');

if you're trying to load data for the current year

if you've only got a few tens of thousands of rows it probably won't make much difference, should be fast enough either way.

If you've got millions or billions of row then it could make a very big difference

you might also want to put a cap on it too

WHERE transaction_date >= trunc(sysdate,'yyyy')
and transaction_date < add_months(trunc(sysdate,'yyyy'),12);
0
 
LVL 8

Author Comment

by:LindaC
Comment Utility
Checking
0
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 20 total points
Comment Utility
Also just to add, I had a similar scenario in one of my production systems

In the table dw.dw_service_orders@mapdb9 check for some unique identifiers

such as order_type or order_number range(from and to number) i believe those columns may have got indexed, then use an input variable to your procedure to specify the range or distinct identifier

ie.,

CREATE OR REPLACE procedure SYSTEM.dw_service_orders(p_input in varchar2) is
   CURSOR c1 is
   SElect * from dw.dw_service_orders@mapdb9 where to_char(transaction_date,'YY')='14' and order_type=p_input;
   c1_rec dw.dw_service_orders%ROWTYPE;

by doing this way, you can split the job in multiple processing modes and increase the parallelism and there by decrease the time to run the whole process..
0
 
LVL 8

Author Closing Comment

by:LindaC
Comment Utility
Thanks.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now