Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

Improving this Pl/sql

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
LindaC
Asked:
LindaC
  • 5
  • 4
2 Solutions
 
sdstuberCommented:
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
 
LindaCAuthor Commented:
I wanted to commit between any specific number of records because of undo issues and doing other migrations.
0
 
sdstuberCommented:
With an INSERT the undo should be minimal unless you have lots of indexes and/or triggers on the insert.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LindaCAuthor Commented:
It is a *big* table. Would prefer committing any number of records.
0
 
sdstuberCommented:
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
 
LindaCAuthor Commented:
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
 
sdstuberCommented:
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
 
LindaCAuthor Commented:
Checking
0
 
Wasim Akram ShaikCommented:
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
 
LindaCAuthor Commented:
Thanks.
0
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.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now