wake-up-neo.net

Oracle PL/SQL - Sind NO_DATA_FOUND-Ausnahmen für die Leistung gespeicherter Prozeduren schlecht?

Ich schreibe eine gespeicherte Prozedur, die viel Konditionierung erfordert. Mit dem allgemeinen Wissen aus der C # .NET-Codierung, dass Ausnahmen die Leistung beeinträchtigen können, habe ich es immer vermieden, sie auch in PL/SQL zu verwenden. Meine Konditionierung in dieser gespeicherten Prozedur dreht sich meistens darum, ob ein Datensatz existiert oder nicht, was ich auf zwei Arten tun könnte:

SELECT COUNT(*) INTO var WHERE condition;
IF var > 0 THEN
   SELECT NEEDED_FIELD INTO otherVar WHERE condition;
....

-oder-

SELECT NEEDED_FIELD INTO var WHERE condition;
EXCEPTION
WHEN NO_DATA_FOUND
....

Der zweite Fall erscheint mir etwas eleganter, da ich dann NEEDED_FIELD verwenden kann, das ich in der ersten Anweisung nach der Bedingung im ersten Fall auswählen müsste. Weniger Code. Wenn die gespeicherte Prozedur jedoch schneller mit COUNT (*) ausgeführt wird, kann ich mich gerne etwas mehr um die Verarbeitungsgeschwindigkeit kümmern.

Irgendwelche Hinweise? Vermisse ich eine andere Möglichkeit?

BEARBEITENIch hätte erwähnen sollen, dass dies alles bereits in einem FOR LOOP verschachtelt ist. Ich bin mir nicht sicher, ob dies bei der Verwendung eines Cursors einen Unterschied macht, da ich glaube, dass ich den Cursor nicht als Select im FOR LOOP deklarieren kann.

23
AJ.

Ich würde dazu keinen expliziten Cursor verwenden. Steve F. rät nicht mehr, explizite Cursor zu verwenden, wenn ein impliziter Cursor verwendet werden könnte. 

Die Methode mit count(*) ist unsicher. Wenn eine andere Sitzung die Zeile löscht, die die Bedingung nach der Zeile mit count(*) und vor der Zeile mit select ... into erfüllt, löst der Code eine Ausnahme aus, die nicht behandelt wird.

Die zweite Version des ursprünglichen Beitrags hat dieses Problem nicht und wird im Allgemeinen bevorzugt.

Allerdings gibt es bei der Ausnahme einen geringfügigen Overhead, und wenn Sie sich zu 100% sicher sind, dass sich die Daten nicht ändern, können Sie die count(*) verwenden, aber ich empfehle es dagegen.

Ich habe diese Benchmarks unter Oracle 10.2.0.1 unter 32-Bit-Windows ausgeführt. Ich schaue nur auf die verstrichene Zeit. Es gibt andere Testkabelbäume, die weitere Details liefern können (z. B. Latch-Zählungen und verwendeter Speicher).

SQL>create table t (NEEDED_FIELD number, COND number);

Tabelle erstellt.

SQL>insert into t (NEEDED_FIELD, cond) values (1, 0);

1 Zeile erstellt.

declare
  otherVar  number;
  cnt number;
begin
  for i in 1 .. 50000 loop
     select count(*) into cnt from t where cond = 1;

     if (cnt = 1) then
       select NEEDED_FIELD INTO otherVar from t where cond = 1;
     else
       otherVar := 0;
     end if;
   end loop;
end;
/

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

Abgelaufen: 00: 00: 02.70

declare
  otherVar  number;
begin
  for i in 1 .. 50000 loop
     begin
       select NEEDED_FIELD INTO otherVar from t where cond = 1;
     exception
       when no_data_found then
         otherVar := 0;
     end;
   end loop;
end;
/

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

Abgelaufen: 00: 00: 03.06

30
RussellH

Da SELECT INTO davon ausgeht, dass eine einzelne Zeile zurückgegeben wird, können Sie eine Anweisung des Formulars verwenden:

SELECT MAX(column)
  INTO var
  FROM table
 WHERE conditions;

IF var IS NOT NULL
THEN ...

Das SELECT gibt Ihnen den Wert, wenn einer verfügbar ist, und den Wert NULL anstelle einer NO_DATA_FOUND-Ausnahme. Der durch MAX () eingeführte Overhead ist minimal zu null, da die Ergebnismenge eine einzelne Zeile enthält. Es hat auch den Vorteil, dass es relativ zu einer Cursor-basierten Lösung kompakt ist und nicht anfällig für Parallelitätsprobleme ist, wie die zweistufige Lösung im ursprünglichen Beitrag.

7
Noah Yetter

Eine Alternative zu @ Steve's Code. 

DECLARE
  CURSOR foo_cur IS 
    SELECT NEEDED_FIELD WHERE condition ;
BEGIN
  FOR foo_rec IN foo_cur LOOP
     ...
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END ;

Die Schleife wird nicht ausgeführt, wenn keine Daten vorhanden sind. Cursor-FOR-Schleifen sind der Weg zu gehen - sie helfen, viel Housekeeping zu vermeiden. Eine noch kompaktere Lösung:

DECLARE
BEGIN
  FOR foo_rec IN (SELECT NEEDED_FIELD WHERE condition) LOOP
     ...
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END ;

Was funktioniert, wenn Sie die vollständige select-Anweisung zur Kompilierzeit kennen.

6
DCookie

@DCookie

Ich möchte nur darauf hinweisen, dass Sie die Zeilen mit den Worten weglassen können

EXCEPTION  
  WHEN OTHERS THEN    
    RAISE;

Das gleiche Ergebnis erhalten Sie, wenn Sie den Ausnahmeblock alle zusammen verlassen. Die für die Ausnahme gemeldete Zeilennummer ist die Zeile, in der die Ausnahme tatsächlich ausgelöst wird, nicht die Zeile im Ausnahmeblock, in der sie erneut ausgelöst wurde.

4
RussellH

Stephen Darlington macht einen sehr guten Punkt, und Sie können sehen, dass, wenn Sie meinen Benchmark so ändern, dass er eine realistischere Tabelle verwendet, wenn ich die Tabelle auf 10000 Zeilen auffüllte, indem Sie Folgendes verwenden:

begin 
  for i in 2 .. 10000 loop
    insert into t (NEEDED_FIELD, cond) values (i, 10);
  end loop;
end;

Führen Sie dann die Benchmarks erneut aus. (Ich musste die Anzahl der Schleifen auf 5000 reduzieren, um vernünftige Zeiten zu erhalten).

declare
  otherVar  number;
  cnt number;
begin
  for i in 1 .. 5000 loop
     select count(*) into cnt from t where cond = 0;

     if (cnt = 1) then
       select NEEDED_FIELD INTO otherVar from t where cond = 0;
     else
       otherVar := 0;
     end if;
   end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.34

declare
  otherVar  number;
begin
  for i in 1 .. 5000 loop
     begin
       select NEEDED_FIELD INTO otherVar from t where cond = 0;
     exception
       when no_data_found then
         otherVar := 0;
     end;
   end loop;
end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.10

Die Methode mit der Ausnahme ist jetzt mehr als doppelt so schnell. Also für fast alle Fälle die Methode:

SELECT NEEDED_FIELD INTO var WHERE condition;
EXCEPTION
WHEN NO_DATA_FOUND....

ist der Weg zu gehen. Es wird korrekte Ergebnisse liefern und ist im Allgemeinen das schnellste. 

3
RussellH

Wenn es wichtig ist, müssen Sie wirklich beide Optionen bewerten!

Allerdings habe ich immer die Ausnahmemethode verwendet, weil es besser ist, die Datenbank nur einmal zu schlagen.

2

Ja, Sie vermissen den Einsatz von Cursorn

DECLARE
  CURSOR foo_cur IS 
    SELECT NEEDED_FIELD WHERE condition ;
BEGIN
  OPEN foo_cur;
  FETCH foo_cur INTO foo_rec;
  IF foo_cur%FOUND THEN
     ...
  END IF;
  CLOSE foo_cur;
EXCEPTION
  WHEN OTHERS THEN
    CLOSE foo_cur;
    RAISE;
END ;

zwar ist dies mehr Code, aber es wird nicht EXCEPTIONs als Flusskontrolle verwendet, da ich die meisten meiner PL/SQL aus Steve Feuersteins PL/SQL-Programmierbuch gelernt habe, glaube ich, dass es eine gute Sache ist.

Ob das schneller ist oder nicht, weiß ich nicht (ich mache heutzutage sehr wenig PL/SQL).

1
Steve Bosman

Anstatt verschachtelte Cursor-Schleifen zu haben, wäre ein effizienterer Ansatz die Verwendung einer Cursor-Schleife mit einem äußeren Join zwischen den Tabellen.

BEGIN
    FOR rec IN (SELECT a.needed_field,b.other_field
                  FROM table1 a
                  LEFT OUTER JOIN table2 b
                    ON a.needed_field = b.condition_field
                 WHERE a.column = ???)
    LOOP
       IF rec.other_field IS NOT NULL THEN
         -- whatever processing needs to be done to other_field
       END IF;
    END LOOP;
END;
1
pablo

Die erste (exzellente) Antwort lautete:

Die Methode mit count () ist unsicher. Wenn eine andere Sitzung die Zeile löscht, die die Bedingung nach der Zeile mit der Anzahl (*) und vor der Zeile mit Select ... erfüllt, löst der Code eine Ausnahme aus, die nicht behandelt wird.

Nicht so. Innerhalb einer bestimmten logischen Arbeitseinheit ist Oracle vollkommen konsistent. Selbst wenn jemand das Löschen der Zeile zwischen einem Count und einem ausgewählten Oracle festschreibt, erhält Oracle für die aktive Sitzung die Daten aus den Protokollen. Wenn dies nicht möglich ist, erhalten Sie die Fehlermeldung "Schnappschuss zu alt".

sie müssen nicht open verwenden, wenn Sie for-Loops verwenden.

declare
cursor cur_name is  select * from emp;
begin
for cur_rec in cur_name Loop
    dbms_output.put_line(cur_rec.ename);
end loop;
End ;

oder 

declare
cursor cur_name is  select * from emp;
cur_rec emp%rowtype;
begin
Open cur_name;
Loop
Fetch cur_name into  Cur_rec;
   Exit when cur_name%notfound;
    dbms_output.put_line(cur_rec.ename);
end loop;
Close cur_name;
End ;
0

Kann ein totes Pferd hier schlagen, aber ich habe den Cursor für die Schleife mit einer Benchmarking versehen, und das hat ungefähr ebenso funktioniert wie die Methode no_data_found:

declare
  otherVar  number;
begin
  for i in 1 .. 5000 loop
     begin
       for foo_rec in (select NEEDED_FIELD from t where cond = 0) loop
         otherVar := foo_rec.NEEDED_FIELD;
       end loop;
       otherVar := 0;
     end;
   end loop;
end;

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

Abgelaufen: 00: 00: 02.18

0
RussellH

Die Anzahl (*) löst niemals eine Ausnahme aus, da sie immer die tatsächliche Anzahl oder 0 - 0 zurückgibt, egal was passiert. Ich würde zählen zählen.

0
Art