Solved

Improving this Pl/sql

Posted on 2014-03-27
10
443 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 480 total points
ID: 39959843
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
ID: 39959865
I wanted to commit between any specific number of records because of undo issues and doing other migrations.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39959911
With an INSERT the undo should be minimal unless you have lots of indexes and/or triggers on the insert.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Author Comment

by:LindaC
ID: 39959936
It is a *big* table. Would prefer committing any number of records.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39959985
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
 
LVL 8

Author Comment

by:LindaC
ID: 39959996
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 74

Expert Comment

by:sdstuber
ID: 39960003
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
ID: 39960010
Checking
0
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 20 total points
ID: 39960864
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
ID: 39961187
Thanks.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SYS password changed. Now can't log in as SYS 27 62
Select the 2 most recent visit dates 5 31
Errror when importing data from Oracle to SQL 6 67
replicate in oracle 13 45
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

738 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