Thursday Apr 25, 2013

'IS NULL' SQL Tuning

NULLFrequently at client sites some very slow queries contain the SQL involving the ‘IS NULL’ criteria in the where clause. It is commonly known Oracle does not store NULL values in indexes so even though an index may exist for the column it is NOT usable in this case. However there is a solution to this situation and FULL table scans can be avoided!

Example: SQL> Select name from emp where dept_id is null;

So in the above example even if the dept_id is indexed on the table emp, due to the ‘IS NULL’ criteria it will NOT be used.

Many developers believe that this type of statement cannot be tuned and that a FULL table scan is the only option. However here is a solution to this issue, simply create an appropriate index but add in an additional constant value to the end of the index so that now NULL values are actually stored!

Example: SQL> create index E1 on EMP (DEPT_ID, -1);

Running an explain plan and executing the SQL it can be seen that the new index will now be used as NULL values are now indexed.

Real life example: At a particular customer site there was a situation where a table had over 11 million rows. A SQL statement had a lot of table joins but only 2 constraints in the where clause as below.

AND T28.PRIV_FLG = ‘N’

AND T28.INTEGRTION_ID IS NULL

From analyzing the table almost every row had the PRIV_FLG = ‘N’ so that was not going to help, however looking at the integration_id from the 11 million rows, only 51,167 were null values. The field 'integration_id' was already indexed however this would not be used due to only wanting NULL values. By indexing as in the above example the new index helped optimize the query significantly as the number of rows in the driving table had been significantly reduced without any full table scans!

About

About Me Image
Andy Baker, Senior Principal Consultant for Oracle Consulting Services (@Bakers_byte), shares his news, views and ideas about the Oracle Database with a focus on innovation and emerging technologies.

Search

Archives
« April 2013 »
SunMonTueWedThuFriSat
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
19
20
21
22
23
24
26
27
28
29
30
    
       
Today