News:
SQL Tuning Tipp: NVL() oder andere Funktionen in WHERE-Klauseln

Wann immer möglich, sollte in der WHERE-Klausel darauf verzichtet werden, Funktionen um Tabellenspalten zu legen.

Icon Unternehmen

Funktionen um Spalten verhindern, dass dieser Teil der Klausel einen (konventionellen) Index verwenden kann. Zudem ist es dem Optimizer (ohne weiteres) nicht möglich, die Kosten für die Funktionswerte zu ermitteln. Er geht an dieser Stelle von festen Standardwerten aus. Grundsätzlich kann man diesen Limitierungen durch function-based Indexes und/oder Extended Statistics begegnen. Beides zieht aber in jedem Fall Mehraufwand (Verwaltung, höherer DML-Aufwand etc) nach sich. 

Häufig lassen sich bessere Alternativen finden.

NVL() in der WHERE Klausel

SELECT *
FROM employees e
WHERE nvl(first_name,'unknown') = 'unknown';

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    69 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     1 |    69 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Eine saubere Alternative

SELECT *
FROM employees e
WHERE (first_name='unknown' or first_name is null);

---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |     2 |   266 |     3   (0)| 00:00:01 |
|   1 |  VIEW                                 | VW_ORE_38F5D95B |     2 |   266 |     3   (0)| 00:00:01 |
|   2 |   UNION-ALL                           |                 |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES       |     1 |    69 |     2   (0)| 00:00:01 |
|*  4 |     INDEX SKIP SCAN                   | EMP_NAME_IX     |     1 |       |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES       |     1 |    69 |     1   (0)| 00:00:01 |
|*  6 |     INDEX SKIP SCAN                   | EMP_NAME_IX     |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

TO_CHAR()-Funktion um eine Tabellenspalte

SELECT *
FROM employees e
WHERE to_char(hire_date,'YYYY-MM-DD') > '2019-01-01';

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     5 |   345 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     5 |   345 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Eine saubere Alternative

SELECT *
FROM employees e
WHERE hire_date > to_date('2019-01-01','YYYY-MM-DD');

--------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |     1 |    69 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES        |     1 |    69 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | EMP_HIRE_DATE_IX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------