CODEWITHSUNDEEP
oracle-database
Ardahan Kisbet
Ardahan Kisbet

Reputation: 659

Are there any impact of update statement on for loop statement in oracle?

I have nested for loop which iterates same table. In inner loop I update a column in same table. But in for loop condition I check that updated column and I need to check this column not in the beginning but dynamically, so my for loop iterations will maybe greatly decrease.

Am I doing this correct or is for statement will not see updated column?

declare
control number(1);
dup number(10);
res varchar2(5);--TRUE or FALSE

BEGIN
dup :=0;
control :=0;
FOR aRow IN (SELECT MI_PRINX, geoloc,durum, ROWID FROM ORAHAN where durum=0)    
LOOP
 FOR bRow IN (SELECT MI_PRINX, geoloc, ROWID FROM ORAHAN WHERE ROWID>aRow.ROWID AND durum=0)
  LOOP
    BEGIN
       --dbms_output.put_line('aRow' || aRow.Mi_Prinx || ' bRow' || bRow.Mi_Prinx);
    select SDO_GEOM.RELATE(aRow.geoloc,'anyinteract', bRow.Geoloc,0.02) into res from dual;
    if (res='TRUE')
      THEN 
        Insert INTO ORAHANCROSSES values (aRow.MI_PRINX,bRow.MI_PRINX);
        UPDATE ORAHAN SET DURUM=1 where rowid=bRow.Rowid; 
        control :=1;
        --dbms_output.put_line(' added');
    END IF;
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX
        THEN
          dup := dup+1;
          --dbms_output.put_line('duplicate');
          --continue;
    END;
  END LOOP;
  IF(control =1)
    THEN
      UPDATE ORAHAN SET DURUM=1 WHERE rowid=aRow.Rowid;
  END IF;
  control :=0;
END LOOP;
dbms_output.put_line('duplicate: '||dup);
END ;

Note: I use oracle 11g and pl/sql developer Sorry my english.

Upvotes: 0

Views: 128

Answers (1)

HAL 9000
HAL 9000

Reputation: 3985

Yes, the FOR statement will not see the updated DURUM column because the FOR statement will see all data as they were when the query was started! This is called read consistency and Oracle accomplishes this by using the generated UNDO data. That means it'll have more and more work to do (==run slower) as your FOR loop advances and the base table is updated!

It also means that your implementation will eventually run into a ORA-01555: snapshot too old error when the UNDO tablespace is exhausted.

You'll be probably better off using a SQL MERGE statement which should also run much faster.

e.g.:

Merge Into ORAHANCROSSES C
Using (Select aROW.MI_PRINX  aROW_MI_PRIX, 
              aROW.GEOLOC    aROW_GEOLOC,
              bROW.MI_PRINX  bROW_MI_PRIX, 
              bROW.GEOLOC    bROW_GEOLOC,
              SDO_GEOM.RELATE(aRow.geoloc,'anyinteract', bRow.Geoloc,0.02) RES
       From   ORAHAN aROW,
              ORAHAN bROW
       Where  aROW.ROWID < bROW.ROWID
       ) Q
On    (C.MI_PRIX1 = Q.aROW_MI_PRIX
  and  C.MI_PRIX2 = Q.bROW_MI_PRIX)
When Matched Then
     Delete Where Q.RES = 'FALSE'
When Not Matched Then
     Insert Values (Q.aROW_MI_PRIX, Q.bROW_MI_PRIX)
     Where Q.RES = 'TRUE'
;

I'm not sure what you're trying to accomplish by ROWID>aRow.ROWID though

To use a certain order (in this case MI_PRINX) use the following technique:

Merge Into ORAHANCROSSES C
Using (With D as (select T.*, ROWNUM RN from (select MI_PRINX, GEOLOC from ORAHAN order by MI_PRINX) T)
       Select aROW.MI_PRINX  aROW_MI_PRIX, 
              aROW.GEOLOC    aROW_GEOLOC,
              bROW.MI_PRINX  bROW_MI_PRIX, 
              bROW.GEOLOC    bROW_GEOLOC,
              SDO_GEOM.RELATE(aRow.geoloc,'anyinteract', bRow.Geoloc,0.02) RES
       From   D aROW,
              D bROW
       Where  aROW.RN < bROW.RN
       ) Q
On    (C.MI_PRIX1 = Q.aROW_MI_PRIX
  and  C.MI_PRIX2 = Q.bROW_MI_PRIX)
When Matched Then
     Delete Where Q.RES = 'FALSE'
When Not Matched Then
     Insert Values (Q.aROW_MI_PRIX, Q.bROW_MI_PRIX)
     Where Q.RES = 'TRUE'
;

In case the query is taking too long, you might select * from v$session_longops where seconds_remaining >0 to find out when it'll be finished.

Upvotes: 1

Related Questions