Home » SQL & PL/SQL » SQL & PL/SQL » FORALL gets executed twice (Oracle 10g R2)
FORALL gets executed twice [message #321104] Mon, 19 May 2008 01:48 Go to next message
vjnair
Messages: 5
Registered: March 2008
Junior Member
Hi,

Here is my code which I use to update 65K records in a table. I first retrieve the records, do some processing on the same table and then update it. It was taking a bit more time than expected so, I switched on the dbms_profiler package. While using a limit of 2500, instead of seeing the FORALL statement getting executed 26 times, I see the exact double i.e. 52 I am a bit clueless as to how this is happening..Any pointers could be helpful..The dbms_output.put_line() also gets printed out thrice only

OPEN C_T_EQ_MASTER FOR 'SELECT * FROM T_MASTER_EQUITY WHERE T_PROCESS_STATUS =''A''';
      LOOP
        FETCH C_T_EQ_MASTER BULK COLLECT INTO V_TT_T_EQ_MASTER LIMIT 2500;
        FOR I IN 1..V_TT_T_EQ_MASTER.COUNT
        LOOP
              BEGIN
              SELECT T.INSTRUMENT_ID INTO V_INSTRUMENT_ID FROM INSTRUMENT_ALT T WHERE
              T.INSTRUMENT_ALT_ID =V_TT_T_EQ_MASTER(I).T_ALT_ID  AND T.ALT_ID_TYPE = V_TT_T_EQ_MASTER(I).T_ALT_ID_TYPE;
              
              V_TT_INSTRUMENT_ID.EXTEND;
              V_TT_INSTRUMENT_ID(V_TT_INSTRUMENT_ID.LAST)   := V_INSTRUMENT_ID;
              V_TT_ALT_INSTR_ID.EXTEND;
              V_TT_ALT_INSTR_ID(V_TT_ALT_INSTR_ID.LAST)   := V_TT_T_EQ_MASTER(I).T_ALT_ID;              
              V_TT_ALT_INSTR_TYPE.EXTEND;
              V_TT_ALT_INSTR_TYPE(V_TT_ALT_INSTR_TYPE.LAST)   := V_TT_T_EQ_MASTER(I).T_ALT_ID_TYPE;              
              EXCEPTION
                   WHEN NO_DATA_FOUND THEN
                   V_TT_INSTRUMENT_ID.EXTEND;
                   V_TT_INSTRUMENT_ID(V_TT_INSTRUMENT_ID.LAST) := NULL;
                   V_TT_ALT_INSTR_ID.EXTEND;
                   V_TT_ALT_INSTR_ID(V_TT_ALT_INSTR_ID.LAST)   := V_TT_T_EQ_MASTER(I).T_ALT_ID;              
                   V_TT_ALT_INSTR_TYPE.EXTEND;
                   V_TT_ALT_INSTR_TYPE(V_TT_ALT_INSTR_TYPE.LAST)   := V_TT_T_EQ_MASTER(I).T_ALT_ID_TYPE;   
              END;
        END LOOP;      
              BEGIN
              dbms_output.put_line('GOING TO CALL FORALL');
              FORALL I in V_TT_ALT_INSTR_ID.FIRST..V_TT_ALT_INSTR_ID.LAST 
              UPDATE T_MASTER_EQUITY T SET
                     T.T_INSTRUMENT_ID       = V_TT_INSTRUMENT_ID(I)
              WHERE  T.T_ALT_ID = V_TT_ALT_INSTR_ID(I) AND T.T_ALT_ID_TYPE = V_TT_ALT_INSTR_TYPE(I); --This gets called 52 times
              END;


              COMMIT;
              V_TT_INSTRUMENT_ID.DELETE;
              V_TT_ALT_INSTR_ID.DELETE;
              V_TT_ALT_INSTR_TYPE.DELETE;
              EXIT WHEN V_TT_T_EQ_MASTER.COUNT < 2500;
              V_TT_T_EQ_MASTER.DELETE;
    END LOOP;
    CLOSE C_T_EQ_MASTER;
Re: FORALL gets executed twice [message #321112 is a reply to message #321104] Mon, 19 May 2008 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
26*2500 = 65000 tiny number, you should use a single SQL statement.

Regards
Michel
Re: FORALL gets executed twice [message #321115 is a reply to message #321104] Mon, 19 May 2008 02:21 Go to previous messageGo to next message
vjnair
Messages: 5
Registered: March 2008
Junior Member
Hello Michel,

Thanks for the reply, but could you explain a bit more in detail...it is kind of cryptic for a newbie like me

Tks...Vijay
Re: FORALL gets executed twice [message #321117 is a reply to message #321115] Mon, 19 May 2008 02:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
something like:
update T_MASTER_EQUITY
set T.T_INSTRUMENT_ID = (select ... from T_MASTER_EQUITY, INSTRUMENT_ALT where <join conditions>)
where T_PROCESS_STATUS ='A'
/

Regards
Michel
Re: FORALL gets executed twice [message #321123 is a reply to message #321104] Mon, 19 May 2008 03:02 Go to previous messageGo to next message
vjnair
Messages: 5
Registered: March 2008
Junior Member
Thanks Michel.

Yes that can be done, but is there any reason why the FORALL gets executed twice ?

Tks...Vijay
Re: FORALL gets executed twice [message #321141 is a reply to message #321123] Mon, 19 May 2008 04:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I strongly suspect that the updates are not being executed twice, and that this is a mistake in interpretting the results of DBMS_PROFILER.

Can you show us the evidence that makes you believe that they are?
Re: FORALL gets executed twice [message #321155 is a reply to message #321141] Mon, 19 May 2008 04:37 Go to previous messageGo to next message
vjnair
Messages: 5
Registered: March 2008
Junior Member
Hi,

I have attached a sample profiler report....

sampler number of records = 5009
limit = 2500

It shows the FORALL statement getting executed 6 times instead of 3

Thanks..Vijay
  • Attachment: profiler.csv
    (Size: 5.40KB, Downloaded 99 times)
Re: FORALL gets executed twice [message #321156 is a reply to message #321155] Mon, 19 May 2008 04:46 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe just a problem with dbms_profiler for this instruction as the other lines of the same blocks show 3.

Regards
Michel
Previous Topic: How to join two SQL queries
Next Topic: view needed on the derived column
Goto Forum:
  


Current Time: Wed Dec 07 10:20:03 CST 2016

Total time taken to generate the page: 0.16390 seconds