Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2015

    Question Unanswered: Optimize a sql program


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

    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;
    		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);
    						EXIT WHEN hasMatch = 1 and arrayOfR(i).b <> arrayOfS(j).b;
    					end if;
    				end loop;
    			end loop;
    Thanks for you help,

  2. #2
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    COMMIT inside LOOP makes elapsed time longer when compared to single COMMIT at the end of loop.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2007
    What about a simple join? Something like (not tested because no test case was included)
    INSERT INTO RmergeS (A, B, C)
    select testR.A, testR.B, testS.C
    from testR inner join testS on testS.B = testR.B;
    You may still performance problems if B is not unique in any of those tables, however, you should rather change the design in that case.

  4. #4
    Join Date
    Feb 2015

    @56anacedent: For you, I must to move the commit before the last end loop ?

    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;

    -> I must to use bulk but i would like optimize the query with end loop


  5. #5
    Join Date
    Jun 2003
    West Palm Beach, FL


    Besides, this statement: "EXIT WHEN hasMatch = 1 and arrayOfR(i).b <> arrayOfS(j).b;"
    would never be true because it is inside this if: "IF arrayOfR(i).b = arrayOfS(j).b then"...

    To optimize, do what flyboy suggests.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts