1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Optimize a sql program

Discussion in 'SQL PL/SQL' started by nikolas, Feb 20, 2015.

  1. nikolas

    nikolas Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Hello,

    I need your help. I would like optimize a sql program below. I use bulk and array. Do you have idea ?

    Code (Text):
    create table RmergeS as select * from test.RmergeS
        create or replace procedure mergeRS is
        TYPE R_array IS TABLE OF testR%ROWTYPE;
        TYPE S_array IS TABLE OF testS%ROWTYPE;
       
        arrayOfR R_array;
        arrayOfS S_array;
       
        i number;
        j number;
        hasMatch number;
       
        begin
            delete from RmergeS;
            select * bulk collect into arrayOfR from testR order by B;
            select * bulk collect into arrayOfS from testS order by B;
               
                for i in 1..arrayOfR.count loop
                    for j in 1..arrayOfS.count loop
                        IF arrayOfR(i).b = arrayOfS(j).b then
                            hasMatch := 1;
                            INSERT INTO RmergeS (A, B, C) VALUES(arrayOfR(i).A, arrayOfR(i).B, arrayOfS(j).C);
                            COMMIT;
                            EXIT WHEN hasMatch = 1 and arrayOfR(i).b <> arrayOfS(j).b;
                        end if;
                    end loop;
                end loop;
    end;

    Thanks for you help,
    Nikolas
     
  2. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Hello Nikolas,

    Could you please elaborate your expectation/requirement. From the above code I understand that you have used Bulk collect concept in the block. and you are testing the performance using collections .

    I could find discrepancy in the table creation script 'create table RmergeS as select * from test.RmergeS'

    We will try to help you to resolve your issues/requirement
     
  3. nikolas

    nikolas Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    hello RG Hegde,

    I would like reduce this SQL program. I think it is possible to optimise this program, probably in loops.

    Niko
     
  4. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    Yes Nikolas
    Could you please explain what you are trying to do with this code in words. Then we could help you to achieve/optimize that.
     
  5. RG Hegde

    RG Hegde Forum Advisor

    Messages:
    185
    Likes Received:
    35
    Trophy Points:
    310
    Location:
    Pune
    could you please confirm the statement 'create table RmergeS as select * from test.RmergeS '

    Please explain in detail and clear . We could try to help you out .
     
    nikolas likes this.
  6. nikolas

    nikolas Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    I have two tables, table R(A,B), table S(B,C), Rbis(A,B), RmergeS(A,B,C).

    I must to recreate a table RmergeS. I must to write a pl/sql program which insert in the table RmergeS, the query result:

    select * from R natural join S;

    On the other and, I must to write an other PL/SQL program to calculate: (select * from R) minus (select * from Rbis).

    Niko
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
  8. nikolas

    nikolas Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Hello,

    Do you need help to write two PL/SQL program to calculate (select * from R) minus (select * from Rbis) and (select * from R) union (select * from Rbis) with bulk.

    Niko
     
  9. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    you write two programs, using examples from the references provided earlier.
    if you has of error in your code , write about it, here then will we to help.

    for you it is possible to write two programs, but to you there will be no advantage... since you don't learn...

    additional links:
    BULK COLLECT Clause
    Bulk Processing with BULK COLLECT
     
  10. nikolas

    nikolas Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    hi,

    I have a question: How to write if the value of R is not exist in Rbis then

    I try:

    Code (Text):
    create or replace procedure RMINUSRBIS is

    TYPE R_array IS TABLE OF R%ROWTYPE;
    TYPE Rbis_array IS TABLE OF S%ROWTYPE;

    arrayOfR R_array;
    arrayOfRbis Rbis_array;

    i number;
    j number;
    hasMatch number;

    begin
      delete from TableRminusRbis;
      select * bulk collect into arrayOfR from R order by A;
      select * bulk collect into arrayOfRbis from RBIS order by A;
        for i in 1..arrayOfR.count loop
          for j in 1..arrayOfRbis.count loop
           [COLOR="red"] IF arrayOfR(i).b = arrayOfRbis(j).b then[/COLOR]
              hasMatch := 1;
              INSERT INTO TableRminusRbis (A, B) VALUES(arrayOfR(i).A, arrayOfR(i).B);
              EXIT WHEN hasMatch = 1 and arrayOfR(i).b <> arrayOfRbis(j).b;
            end if;
          end loop;
        COMMIT;
        end loop;
    end RMINUSRBIS;
     
  11. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Simple example(one of candidate solutions):

    Code (SQL):

    CREATE OR REPLACE PROCEDURE rminusrbis IS
     
    TYPE r_array IS TABLE OF r%rowtype;
    TYPE rbis_array IS TABLE OF s%rowtype;
     
    arrayofr r_array;
    arrayofrbis rbis_array;
     
    i NUMBER;
    j NUMBER;

     
    BEGIN
      DELETE FROM tablerminusrbis;
      SELECT * bulk collect INTO arrayofr FROM r ORDER BY a;
      SELECT * bulk collect INTO arrayofrbis FROM rbis ORDER BY a;
     
        i := arrayofr.FIRST;  
       
        while i IS NOT NULL loop
         
          FOR j IN 1 .. arrayofrbis.COUNT
          loop
              IF arrayofr(i).a = arrayofrbis(j).a
                  AND
                arrayofr(i).b = arrayofrbis(j).b
             THEN          
                 arrayofr.DELETE(i);exit;
             END IF;
             
          END loop;
           
          i := arrayofr.NEXT(i);
         
        END loop;  
       
        forall z IN indeces OF arrayofr
        INSERT INTO tablerlminusrbis VALUES (arrayofr(z).a,arrayofr(z).b);
       
        commit;
       
    END RMINUSRBIS;
     
     
    nikolas likes this.
  12. nikolas

    nikolas Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    #####################################################
     
  13. nikolas

    nikolas Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Hi,

    Just a little question,I have modify the code but I have an error ?

    In Apex Oracle, I have this error :

    Compilation failed,line 24 (09:36:54)
    PLS-00302: component 'A' must be declaredCompilation failed,line 24 (09:36:54)
    PL/SQL: Statement ignored

    Code (SQL):
    CREATE OR REPLACE PROCEDURE rminusrbis IS
     
    TYPE r_array IS TABLE OF r%rowtype;
    TYPE rbis_array IS TABLE OF s%rowtype;
     
    arrayofr r_array;
    arrayofrbis rbis_array;
     
    i NUMBER;
    j NUMBER;
    z NUMBER;
     
    BEGIN
      DELETE FROM tablerminusrbis;
      SELECT * bulk collect INTO arrayofr FROM R ORDER BY A;
      SELECT * bulk collect INTO arrayofrbis FROM Rbis ORDER BY A;
     
        i := arrayofr.FIRST;  
     
        while i IS NOT NULL loop
     
          FOR j IN 1 .. arrayofrbis.COUNT
          loop
              IF arrayofr(i).a = arrayofrbis(j).a
                  AND
                arrayofr(i).b = arrayofrbis(j).b
             THEN          
                 arrayofr.DELETE(i);
                 exit;
             END IF;
     
          END loop;
     
          i := arrayofr.NEXT(i);
     
        END loop;  
     
        FOR z IN 1 .. arrayofr.COUNT loop
        INSERT INTO tablerminusrbis VALUES (arrayofr(z).a,arrayofr(z).b);
        END loop;  
        commit;
     
    END RMINUSRBIS;
    Niko
     
  14. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    give an example of a call of the procedure...
    what current schema by a call?

    why you removed from the forall code?


    p.s. variables z, j can be removed - they superfluous.

    FOR LOOP Statement
     
    nikolas likes this.
  15. nikolas

    nikolas Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    I have try this
    rminusrbis();

    and this
    begin
    execute rminusrbis();
    end;

    I have modify the forall code because I have an error when I would like save the procedure.
    Compilation failed,line 37 (11:57:03)
    PLS-00103: Encountered the symbol "OF" when expecting one of the following: . ( * @ % & - + / at mod remainder rem .. <an exponent (**)> || multiset The symbol ".. was inserted before "OF" to continue.
     
  16. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    It is necessary to use or execute immediate, or having called procedures...
    if procedure is in other schema, use the full qualifier

    Code (SQL):

    BEGIN
        rminusrbis;
    END;
     
     
  17. nikolas

    nikolas Active Member

    Messages:
    10
    Likes Received:
    0
    Trophy Points:
    80
    Thanks for your help Sergey. I better understand the principle of the bulk. At the beginning, I needed a boost. I closing the topic.

    Thanks ;-) Niko