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?

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

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

Select
t.name,
s.date
from
ticket t,
status s
where
t.id=s.id
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