?
Solved

Improving this Pl/sql

Posted on 2014-03-27
10
Medium Priority
?
445 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 1920 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 80 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

718 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