November 3, 2008 at 3:52 am
i'm sure i read somewhere (a long time ago) that if you index a column with NULL values and use the filter "WHERE col1 IS / IS NOT NULL" the optimiser will only ever do a scan of the index as opposed to a seek.
I've tested this and it keeps coming back using index seeks, did i dream that i read this or is there some truth in it??
My test used a table with 1500 rows with only 5 of them with NULL values in col1 using query:
select descr from test
where descr IS NULL
cheers,
Chris
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
November 5, 2008 at 2:18 am
anyone???
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
November 7, 2008 at 2:24 am
I guess the ifnull() function that will make your query using index scan. I've come across this kind of query many times. Not the is null or is not null in the where clause. Since null can be seek within an index. But if most of the rows in that column is null then index scan will still be used. So it still depend on the statistics, available indexes, and the query optimizer engine to decide.
Cheers,
Ivan Budiono
Ivan Budiono
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply