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

Verfasst von Thilo Solbrig, Oracle Certified Master
13 August 2021
08:00
Datenbank, DBA Tipp, Oracle Database, Performance Tuning, SQL Tuning

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 |
--------------------------------------------------------------------------------------------------------

Hier findest du weitere Posts zu den Themen SQL Tuning bzw. Performance Tuning aus unserem News Bereich.

Share this article

SQL Tuning Tipp: NVL() oder andere Funk­tio­nen in WHERE-Klauseln

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