Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    2

    Unanswered: Optimising Null Check in a Query. Need Help in Altering Table or a Better Solution.

    For a table:
    table1
    Id , not null.
    first_name, not null.
    last_name, nullable.
    last_call date, nullable.
    last_call_action, nullable.

    Now i need to check for all Id whose last_call_date is not null. and i have about 3.some million records and obviously the query hangs up pretty bad.

    I need a better design for this table or a better query to make this work.

    The actual query looks similar to this:

    select count(t1.Id), t2.filter_name ...... from table t1, table t2,....
    where t2.filter_id == t3.filter_id
    and ..............
    and t1.last_call_date is null.

    Things that i have already considered.
    1. Make an index on last_call_date , but since it is a nullable column the overhead would be very high. Also i am planning to update the tables using batch scripts with record counts reaching some millions.
    2. Alter the table to create a new column ( as stupid as it sounds ) which would be not nullable and would have a distinct value for all null values of last_call_date ( hence create an index over that ), which would be the last resort actually, but that would really change the system big time.
    3. Used a lot of techniques , ie rephrased the query , tried decode, nvl etc..

    And now i am stuck. AnyHelp would be gratefully appreciated.
    Thanks a lot.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    > Now i need to check for all Id whose last_call_date is not null.

    I presume you meant "... last_call_date is null."?

    An index on last_call_date will not help, because all-null entries are not stored in the index. I have no idea what you meant by "but since it is a nullable column the overhead would be very high"...?

    If the number of null last_call_dates is relatively high, then no index is likely to help; however, if it is small then a function-based index might help:

    create index xx on table1 (decode(last_call_date,null,'X'));

    Now you can make use of this index like this:

    select count(t1.Id), t2.filter_name ...... from table t1, table t2,....
    where t2.filter_id == t3.filter_id
    and ..............
    and decode(t1.last_call_date,null,'X') = 'X';

  3. #3
    Join Date
    Nov 2004
    Posts
    2
    wow that's a new find.
    i'll try that out... i came so close to this solution.
    Since the number of records are very high in PROD ( 4-5 billion records)... i need the query to take atmost .5 of seconds, since it would be called in the weblayer.

    Thanks for the suggestion.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •