is Null causing full table scan
I am realitively new to SQL development but it is my understanding that utilizing the "Is Null" operation in SQL will force full tablescans. I do not want this for tuning purposes within my query. Is there a Hint I can use or a trick I can use to get around this issue with the Is Null operation. thanks
Re: is Null causing full table scan
Quote:
Originally posted by antilles
I am realitively new to SQL development but it is my understanding that utilizing the "Is Null" operation in SQL will force full tablescans.
I'm worried that you are incorrectly generalizing the issue.
SELECT * FROM FRED <-- Tablescan
SELECT * FROM FRED WHERE FRED_PK = 1 <-- Index
SELECT * FROM FRED WHERE FIRST_NAME IS NULL <-- Tablescan
SELECT * FROM FRED WHERE FRED_PK = 1 AND FIRST_NAME IS NULL <-- Index
So, using IS NULL does not 'force a tablescan'. Rather, the IS NULL construct cannot be resolved by a standard B*Tree index. As slimdave has noted, there are other ways to resolve IS NULL using indexes. But just adding IS NULL to a WHERE clause does NOT force a tablescan.
Make sense?
- Chris