is Null causing full table scan
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: is Null causing full table scan

Hybrid View

  1. #1
    Join Date
    Jan 2002
    Posts
    58

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339

    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  4. #4
    Join Date
    Jan 2002
    Posts
    58
    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?

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    What % of the rows would you expect to be NULL?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    ... 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"
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Jan 2002
    Posts
    58
    I would say 1 to 3 percent

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    how many rows in ticket and status tables?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Jan 2002
    Posts
    58
    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
  •  



Click Here to Expand Forum to Full Width