Sagen Sie, ich habe eine Beispieltabelle:
%Vor%Und ich habe einen Beispiel-PL / SQL-Block, der eine Abfrage hat, die derzeit eine einzelne Zeile in ein Array auswählt:
%Vor% Was ich tun muss, ist die Implementierung einer Möglichkeit, eine einzelne Zeile in ein Array zu wählen, wie im obigen Block gezeigt, ODER , um alle Zeilen zu einem Array auszuwählen, wenn n_RequiredID , was tatsächlich ein user-input-Parameter ist, wird auf null gesetzt.
Und die Frage ist: Was ist die beste Vorgehensweise, um mit einer solchen Situation umzugehen?
Ich kann daran denken, die where -Klausel meiner Abfrage so zu ändern:
aber ich nehme an, das wird die Abfrage verlangsamen, wenn der Parameter nicht null ist, und ich erinnere mich, dass Kyte etwas wirklich Schlechtes über diesen Ansatz gesagt hat.
Ich kann mir auch vorstellen, die folgende PL / SQL-Logik zu implementieren:
%Vor%Aber würde zu komplex werden, wenn ich mehr als einen Parameter dieser Art treffe.
Was würdest du mir raten?
Ja, mit einer der folgenden Möglichkeiten:
%Vor%... sind nicht sargable . Sie werden funktionieren, aber die schlechtesten der verfügbaren Optionen ausführen.
Wenn Sie nur einen Parameter haben, sind die IF / ELSE und separate, maßgeschneiderte Anweisungen eine bessere Alternative.
Die nächste Option ist dynamisches SQL . Die Codierung von dynamischem SQL ist jedoch nutzlos, wenn Sie im ersten Beispiel die nicht sargierbaren Vergleichselemente übernehmen. Mit Dynamic SQL können Sie die Abfrage unter Berücksichtigung zahlreicher Pfade anpassen. Es riskiert aber auch SQL-Injection, also sollte es hinter parametrisierten Abfragen durchgeführt werden (vorzugsweise innerhalb gespeicherter Prozeduren / Funktionen in Paketen.
OMG_Ponies 'und Rob van Wijks Antworten sind völlig richtig, das ist nur ergänzend.
Es gibt einen schönen Trick, um die Verwendung von Bind-Variablen zu vereinfachen und trotzdem dynamisches SQL zu verwenden. Wenn Sie alle Bindungen in eine With-Klausel am Anfang einfügen, können Sie immer denselben Satz von Variablen binden, unabhängig davon, ob Sie sie verwenden wollen oder nicht.
Angenommen, Sie haben drei Parameter, die einen Datumsbereich und eine ID darstellen. Wenn Sie nur nach der ID suchen möchten, könnten Sie die Abfrage wie folgt zusammenstellen:
%Vor%Wenn Sie andererseits nach der ID und dem Datumsbereich suchen müssen, könnte dies wie folgt aussehen:
%Vor%Dies mag wie eine ungefähre Art der Handhabung erscheinen, aber das Endergebnis ist, dass, egal wie Sie Ihre dynamischen SQL-Geometrien kompliziert haben, der PL / SQL-Aufruf immer etwas ist, solange er nur diese drei Parameter benötigt wie:
%Vor%Nach meiner Erfahrung ist es besser, die SQL-Konstruktion etwas komplizierter zu machen, um sicherzustellen, dass es nur eine Zeile gibt, in der sie tatsächlich ausgeführt wird.
Der NVL-Ansatz funktioniert normalerweise gut. Der Optimierer erkennt dieses Muster und erstellt einen dynamischen Plan. Der Plan verwendet einen Index für einen einzelnen Wert und einen vollständigen Tabellenscan für einen NULL.
Beispieltabelle und Daten
%Vor%Mit verschiedenen Prädikaten ausführen
%Vor%Ausführungspläne abrufen
%Vor%Schlechte Pläne für COALESCE und IS NULL ODER
%Vor%Guter Plan für NVL
Die Operationen FILTER ermöglichen dem Optimierer, abhängig von den Eingabewerten zur Laufzeit einen anderen Plan auszuwählen.
Warnungen
FILTER Operationen und dieser NVL Trick sind nicht gut dokumentiert. Ich bin nicht sicher, welche Version diese Funktionen eingeführt hat, aber es funktioniert mit 11g. Ich hatte Probleme damit, dass FILTER mit einigen komplizierten Abfragen korrekt funktioniert, aber für einfache Abfragen wie diese ist es zuverlässig.