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
there are a number ofways of getting around this ...
i) bitmap index -- not suitable ofr tables with update/insert/deletes going on, ie. DW or DSS only.
ii) composite index -- if one of the indexed columns is not null, then null values in the other column(s) will be includedin the index
iii) function_based index. you could create an index on DECODE(my_column,null,'Y',null), which would index _only_ null values, and you can query on "DECODE(my_column,null,'Y',null) = 'Y'" to pull out all the "my_column is null" rows very quickly. Or you could supply a default by indexing on "NVL(my_number_column,-1)" for example.
So why do you want null's included inthe index? what kind of queries are you going to run that would benefit from it?
Re: is Null causing full table scan
I'm worried that you are incorrectly generalizing the issue.
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.
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.
Okay let me try to explain. we have to query this table and the column we are looking for is a date field and we want to return all of the rows where DATE is null. Basically, we need every row that has NOT been updated with a DATE yet. So it is basically as follows
and s.date is null;
In this instance would we not be generating FULL Table scans on the Status table due to the request for the parameter of s.date utilizing Is Null?
What % of the rows would you expect to be NULL?
... and also do you also access that table using predicates/joins based on the date column having a real value, like "s.date = " or "s.date betweeen ...", or "s.date = t.date"
I would say 1 to 3 percent
how many rows in ticket and status tables?
Tickets has about 5 million
Status 15 million
Click Here to Expand Forum to Full Width