Solved

Improving this Pl/sql

Posted on 2014-03-27
10
444 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

635 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