Sehr häufig trifft man in einer Datenbank auf WHERE-Klauseln der folgenden Art:
...
WHERE (:v_name IS NULL OR last_name = :v_name)
AND (:v_odate IS NULL OR TO_CHAR(order_date,'DD-MM-YYYY') = :v_odate)
AND (:v_cid IS NULL OR cust_id = :v_cid)Diese Statements stammen oft von Entwicklungsframeworks, manchmal werden sie auch direkt so geschrieben, weil sie aus Abfragemasken für Kundeneingaben stammen. Diese sind leicht zu erkennen, weil sie meist dem Schema folgen „Entweder Spalte ist leer oder Spalte enthält Wert“.
Der Grund warum das so seltsam formuliert wird, ist, dass der Benutzer möglicherweise in das eine oder andere Feld nichts eingibt, und vielleicht nur nach einem Namen sucht. Dann wären order_date und die Kundennummer eben NULL.
Das Problem dabei ist das OR. Auf Grund des ORs für jede Spalte schätzt der CBO einerseits die Selektivität falsch ein und andererseits werden die Kosten für Indexzugriffe oft genug so „teuer“ veranschlagt, dass das Resultat ein Full Table Scan ist.
Ganz besonders problematisch ist auch noch das TO_CHAR (order_date,’DD-MM-YYYY‘), da hier eine Funktion auf die Spalte order_date ausgeübt wird – hier müsste einen Function Based Index zum Einsatz kommen, damit der CBO überhaupt einen Index-Zugriff durchführt.
Wie kann man das die die gleiche Funktionalität erreichen und gleichzeitig dem CBO das Leben deutlich leichter machen?
...
WHERE last_name = NVL(:v_name,last_name)
AND order_date = TO_DATE(NVL(:v_odate,order_date),'DD-MM-YYYY')
AND cust_id = NVL(:v_cid,cust_id)Warum führt dies zum gleichen Ergebnis?
Sehen wir uns das am Beispiel für den Namen genauer an. Die Originalbedingung war:
(:v_name IS NULL OR last_name = :v_name)Somit erreichen wir was?
Wenn der Benutzer keinen Namen eingibt, gilt „:v_name IS NULL“, egal was der Name ist, der Benutzer will DIESEN Datensatz potentiell sehen. Mit „OR last_name = :v_name“ wir auf den Namen eingeschränkt, sofern der Benutzer einen angegeben hat.
Die Formulierung „last_name = NVL(:v_name,last_name)“ erreicht genau das Gleiche. Hat der Benutzer NULL eingegeben, ergibt das last_name=last_name und der Datensatz ist somit relevant und wenn es eine Eingabe gab, wird danach gesucht.
Das ganze funktioniert selbstverständlich auch für LIKEs:
last_name LIKE NVL(:v_name,last_name)Welche Vorteile hat diese Schreibweise für den CBO?
Da auf einer Seite immer die Spalte ohne Funktion steht, kann er potentiell passende Indizes identifizieren. Function Based Indizes werden definitiv nicht benötigt und wenn bestimmte Spalten seitens der Benutzer
oft eingegeben werden, kann man auch mit multicolumn Indizes arbeiten, um den Zugriff noch effizienter zu gestalten.
Nehmen wir an, dass der Name in den meisten Fällen eingegeben wird, das Bestelldatum nur selten und die Kundennummer (die vermutlich ohnehin schon den PK darstellt) sicher indiziert ist, wäre folgender Suchindex sinnvoll:
CREATE INDEX S_LAST_NAME_ORDER_DATE_IDX ON ORDER (LAST_NAME, ORDER_DATE);Und schon wird die Abfrage schnell sein, egal welche Felder der Benutzer eingibt. Im Fall vom Bestelldatum ist die Suche nicht ganz so effizient wie über den Namen oder die Kundennummer, aber sicher immer noch besser als bei der Originalquery.