4 Replies Latest reply on Apr 19, 2014 12:10 PM by Jjun.Tan

    Applying a time taking sql script in parallel using pl/sql function


      Hi Experts,


      I am on Oracle on Linux. I have a sql script that runs for about 3 hours and I am checking if there is a way to make it go faster by using the pl/sql package : dbms_parallel_execute. Other appoaches of trying out with indexes etc. have already been used and after that it still takes 3 hours and so I am checking of how to make it go faster by using this pacakge.  The actualy sql script inserts huge amount of CLOB data and is likely to take 3 hours in production. My question is how to use this pacakge to apply it in chunks. I mean how to specify the chunks for this sql:


      Here is the simplified form of the sql and my actual sql is very similar with changed table names and it inserts huge amount of CLOBs:


      INSERT INTO emp1
         SELECT *
           FROM emp2 a
          WHERE     a.empno IN (SELECT empno
                                      FROM emp3
                                     WHERE name LIKE 'A%');




      My quesiton is how to specify chunks for the above sql using dbms_parallel_execute? I have not used this package and will be thankful for suggestions.