-
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
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|