Home » RDBMS Server » Performance Tuning » 'is Null' versus '=' (10g)
'is Null' versus '=' [message #361676] Thu, 27 November 2008 06:55 Go to next message
Messages: 96
Registered: April 2007
I'd like to know which one is faster: 'is null' or '='. I have a flag column which has values 'Y' and null. I wonder if it will be faster to find non-'Y' values by changing the where clause from
'my_flag is null' to
'my_flag = 'N' (provided I change all null values to 'N')

I have 890M rows so I thought to ask before I try assigning the nulls to 'N'.

thanks in advance.
Re: 'is Null' versus '=' [message #361691 is a reply to message #361676] Thu, 27 November 2008 09:05 Go to previous messageGo to next message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you set the values to N, then you can at least index them, which might make a difference if there is a very skewed distribution of values.

Otherwise, I wouldn't expect it to make a difference.
Re: 'is Null' versus '=' [message #361695 is a reply to message #361676] Thu, 27 November 2008 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 65089
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course you must check if this change will not impact an application.

Re: 'is Null' versus '=' [message #361745 is a reply to message #361695] Thu, 27 November 2008 20:27 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
To rephrase JR's response:

You cannot index NULL values, so if there are very few NULLs/Ns in the table then an index scan will be faster than a full table scan. This would make 'N' better.

However if there are more than 10% NULLs/Ns then an index won't help.

If you have few NULLs, rather than changing to 'N', you could use a function-based index like:
NVL2(my_flag, NULL, 'N')

This would have the advantage of turning Y into NULL and reducing the size of the index. Of course you would have to use the precise syntax in all WHERE clauses:
WHERE NVL2(my_flag, NULL, 'N') = 'N'
which is kind of non-intuitive.

You may also consider List-Partitioning on my_flag - that will give you the best query performance of all, but it may create problems if you update my_flag at all.

Ross Leishman
Previous Topic: Creating SQL tuning set and how it will help
Next Topic: Clob column on remote database
Goto Forum:

Current Time: Thu Jul 27 04:13:19 CDT 2017

Total time taken to generate the page: 0.11516 seconds