News zu Oracle
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 |
--------------------------------------------------------------------------------------------------------
Share this article