| 'is Null' versus '=' [message #361676] |
Thu, 27 November 2008 06:55  |
beetel
Messages: 96
Registered: April 2007
|
Member |
|
|
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  |
JRowbottom
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 #361745 is a reply to message #361695] |
Thu, 27 November 2008 20:27 |
rleishman
Messages: 3728
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:
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'
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
