bulk collect mit "for update"

Lauf ich in ein Interessantes und unerwartetes Problem bei der Verarbeitung der Datensätze in Oracle (11g) mit der BULK COLLECT.

Den folgenden code lief Super, die Verarbeitung über alle Millionen plus Aufzeichnungen mit sich ein Problem:

-- Define cursor
cursor My_Data_Cur Is
Select col1
      ,col2
from My_Table_1;
…

-- Open the cursor
open My_Data_Cur;

-- Loop through all the records in the cursor
loop

  -- Read the first group of records
  fetch My_Data_Cur
  bulk collect into My_Data_Rec
  limit 100;

  -- Exit when there are no more records to process
  Exit when My_Data_Rec.count = 0;

  -- Loop through the records in the group
  for idx in 1 .. My_Data_Rec.count
  loop
    … do work here to populate a records to be inserted into My_Table_2 …
  end loop;

  -- Insert the records into the second table
  forall idx in 1 .. My_Data_Rec.count
  insert into My_Table_2…;

  -- Delete the records just processed from the source table
  forall idx in 1 .. My_Data_Rec.count
  delete from My_Table_1 …;

  commit;
end loop;

Da am Ende der Verarbeitung jeder Gruppe von 100 Datensätzen (limit 100) wir löschen die Datensätze nur gelesen und verarbeitet, ich dachte, es wäre eine gute Idee, fügen Sie die "for update" - syntax, um die cursor-definition so, dass ein anderer Prozess konnte nicht aktualisieren von Datensätzen, zwischen der Zeit, die Daten zu Lesen und die Zeit, die der Datensatz gelöscht wird.

So, das einzige, was in dem code den ich geändert habe war...

cursor My_Data_Cur
is
  select col1
        ,col2
from My_Table_1
for update;

Bei mir lief der PL/SQL-Paket auch nach dieser änderung, der job verarbeitet nur 100 Datensätze aus und beendet sich dann. Ich bestätigte diese änderung verursacht wurde das Problem durch das entfernen der "for update" aus dem cursor und wieder einmal ist das Paket verarbeitet, alle Datensätze aus der Quelltabelle.

Irgendwelche Ideen, warum das hinzufügen der "for update" - Klausel würde dazu führen, diese änderung im Verhalten? Irgendwelche Vorschläge auf, wie man dieses Problem umgehen? Ich werde versuchen, ab einer exklusiven Transaktion auf die Tabelle am Anfang des Prozesses, aber dies ist nicht eine Idee, Lösung, weil ich wirklich nicht wollen, um die gesamte Tabelle sperren, die die Verarbeitung der Daten.

Vielen Dank im Voraus für Eure Hilfe,

Gewähren

InformationsquelleAutor Grant | 2014-02-07

  1. 1

    Das problem ist, dass Sie versuchen zu tun, einen Abruf über ein commit.

    Beim öffnen My_Data_Cur mit der for update Klausel, Oracle ist zu sperren, die jede Zeile in der My_Data_1 Tabelle, bevor Sie es zurückgeben kann, in der alle Zeilen. Wenn Sie commit Oracle hat zu release alle diese Schlösser (die Schlösser Oracle schafft nicht span-Transaktionen). Da der cursor hat nun nicht mehr die Schlösser, die Sie angefordert, Oracle schließen, den cursor da kann es nicht mehr genügen for update - Klausel. Der zweite Holen, daher muss die 0 Zeilen zurück.

    Die logische Konsequenz fast immer zu entfernen, die commit tun und die ganze Sache in einer einzigen Transaktion. Wenn Sie wirklich, wirklich, wirklich brauchen, separate Transaktionen, benötigen Sie zum öffnen und schließen der cursor bei jeder iteration der Schleife. Wahrscheinlich würden Sie wollen, etwas zu tun, beschränken Sie den cursor, um erst dann wieder 100 Zeilen jedes mal, wenn es geöffnet wird (D. H. eine rownum <= 100 - Klausel), so dass Sie nicht den Aufwand für den Besuch jeder Zeile zu platzieren, das Schloss und dann in jeder Zeile eine andere als die 100, die Sie bearbeitet und gelöscht werden, um die Sperre jedes mal durch die Schleife.

    Justin - Danke für die schnelle Antwort und ich Schätze die Tatsache, dass Sie aufgeführt eine Reihe von Optionen!

    InformationsquelleAutor Justin Cave

  2. 1

    Hinzufügen, um Justin ' s Erklärung.

    Sollten Sie gesehen haben, die folgende Fehlermeldung angezeigt.Nicht sicher, wenn Ihr Exception - handler unterdrückt.

    Und die Nachricht selbst erklärt eine Menge!

    Für diese Art von Updates, ist es besser, erstellen Sie eine shadow-Kopie der Haupt-Tabelle, und lassen Sie das öffentliche synonym verweisen. Während einige batch-id, erstellt ein privates synonym zu unserer Haupt-Tabelle, und führen Sie batch-Operationen, um es einfacher für die Wartung.

    Error report -
ORA-01002: fetch out of sequence
ORA-06512: at line 7
01002. 00000 -  "fetch out of sequence"
*Cause:    This error means that a fetch has been attempted from a cursor
           which is no longer valid.  Note that a PL/SQL cursor loop
           implicitly does fetches, and thus may also cause this error.
           There are a number of possible causes for this error, including:
           1) Fetching from a cursor after the last row has been retrieved
           and the ORA-1403 error returned.
           2) If the cursor has been opened with the FOR UPDATE clause,
           fetching after a COMMIT has been issued will return the error.
           3) Rebinding any placeholders in the SQL statement, then issuing
           a fetch before reexecuting the statement.
*Action:   1) Do not issue a fetch statement after the last row has been
           retrieved - there are no more rows to fetch.
           2) Do not issue a COMMIT inside a fetch loop for a cursor
           that has been opened FOR UPDATE.
           3) Reexecute the statement after rebinding, then attempt to
           fetch again.

    Können, können Sie auch ändern Sie die Logik durch die Verwendung von rowid

    Ein Beispiel für Docs:

    DECLARE
-- if "FOR UPDATE OF salary" is included on following line, an error is raised
   CURSOR c1 IS SELECT e.*,rowid FROM employees e;
   emp_rec  employees%ROWTYPE;
BEGIN
   OPEN c1;
   LOOP
     FETCH c1 INTO emp_rec; -- FETCH fails on the second iteration with FOR UPDATE
     EXIT WHEN c1%NOTFOUND;
     IF emp_rec.employee_id = 105 THEN
       UPDATE employees SET salary = salary * 1.05 WHERE rowid = emp_rec.rowid;
         -- this mimics WHERE CURRENT OF c1
     END IF;
     COMMIT;  -- releases locks
   END LOOP;
END;
/

    Sie haben zu Holen einen Datensatz Zeile für Zeile!! update es mit der ROWID UND BEGEHEN sofort
    . Und dann gehen Sie in die nächste Zeile!

    Aber durch diese, Sie müssen aufgeben, die Bulk Binding option.

    Dieser code ist eine dynamische SQL-und ich war nicht abfangen der Ausnahme richtig, das ist, warum ich nicht die Fehlermeldung erhalten. Der alte code nicht Massen sammeln und lief über 3 Tage. Ich schrieb mit der bulk collect und es läuft jetzt in etwa 6 Stunden. Ich habe die "FOR UPDATE" - parameter in meine umschreiben und es wurde gefangen, während ein code-review. Ich habe entfernt die VERPFLICHTEN, die nur bedeutet, dass der Prozess eine alles-oder-nichts-job, ich habe den COMMIT in den code nur so konnten wir halt den job, wenn notwendig, und nicht Locker ist die Arbeit schon getan. Vielen Dank für deinen input.
    Vielen Dank, ist Es nur bestätigen meine Arbeit Probleme mit mir!!

    InformationsquelleAutor Maheswaran Ravisankar

Schreibe einen Kommentar