News zu Oracle

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

Funk­tio­nen um Spalten ver­hin­dern, dass dieser Teil der Klausel einen (kon­ven­tio­nel­len) Index verwenden kann. Zudem ist es dem Optimizer (ohne weiteres) nicht möglich, die Kosten für die Funk­ti­ons­wer­te zu ermitteln. Er geht an dieser Stelle von festen Stan­dard­wer­ten aus. Grund­sätz­lich kann man diesen Li­mi­tie­run­gen durch function-based Indexes und/oder Extended Sta­tis­tics begegnen. Beides zieht aber in jedem Fall Mehr­auf­wand (Ver­wal­tung, höherer DML-Aufwand etc) nach sich. 

Häufig lassen sich bessere Al­ter­na­ti­ven 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. Per­for­mance Tuning aus unserem News Bereich. 
icon-arrow_right_medium-violet-blue.svg

Share this article

Facebook 
Twitter 
LinkedIn 
XING 
WhatsApp 
Email