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

Comments:

Hi Andy

I tested the solution in your artical. It works when the type of the column is number.

But when the type of the column is varchar, the exeuction plan still choose full table scan, even I tried to create the index like create index E1 on EMP (DEPT_ID, 'AAA');

Is it possible for this solution to be used in a varchar type column ?

Thank you

Cheers
Lucas

Posted by Lucas on April 25, 2013 at 09:22 PM EST #

Hi there thanks for the update, however I do not think it matters on DATA TYPE, as to whether this works. It will be more likely down to cardinality and clustering factor. Ask Tom has a good article on this: http://asktom.oracle.com/pls/apex/f?p100:11:0::::P11_QUESTION_ID:2458637000346464034

In the above link there is also a link to other Tom articles featuring nulls and indexes which is worth a read.

As a follow up I also researched Charles Hooper as he has done a lot of work in this area. http://hoopercharles.wordpress.com/2012/02/28/repeat-after-me-null-values-are-not-stored-in-indexes

He actually identified four different ways to get around this issue and lots of examples.

1.Define a composite index with at least one other column that has a NOT NULL constraint (ideally this not null column leads the index).

2. Define a composite index with a numeric constant (such as 1) as the second column in the index, the column with potential nulls leading the index. *** This is as in my update ***

3. Bitmap Indexes always store NULLS. However take care with locking / contention issues if using. If frequent changes to the table by lots of sessions then may not be appropriate.

4. If number of nulls is small compared to number of rows in the table, then a function based index may be appropriate. Charles gives an example in his update as below:

DECODE(c3,NULL,1)
(CASE WHEN c3 IS NULL THEN 1 ELSE NULL END)
(NVL2(c3,NULL,1))

The above function converts a NULL to 1 and non-NULL to NULL.

Charles article is worth a read as he has great examples, especially in the comments section where the issue of whether an index is chosen versus a full table scan is also discussed with particular reference to Multi-block Read Count (MBRC) as well as testing with hints.

Similar to Tom this again leads me to answer your question that getting a 'IS NULL' to use an index is not dependant on DATA TYPE but on the data and statistics.

In my example I knew I had a small number of nulls in a large number of rows, therefore I guess the cost of a full table scan was high compared to that of the index. Hence the index is used.

Hope that helps.
Andy Baker.

Posted by Andy Baker on April 26, 2013 at 08:20 AM EST #

Post a Comment:
  • HTML Syntax: NOT allowed
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 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today