News zu Oracle

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

Funktionen um Spalten verhindern, dass dieser Teil der Klausel einen (konven­tio­nellen) 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. 
icon-arrow_right_medium-violet-blue.svg

Share this article

Share on facebook
Facebook 
Share on twitter
Twitter 
Share on linkedin
LinkedIn 
Share on xing
XING 
Share on whatsapp
WhatsApp 
Share on email
Email