SQL Question
Consider the following statement.
select first_name, last_name from table_adress where address_id
is null;
Eventhough address_id is indexed uniquely, there is a full table scan.
Is there a way to force index use without changing the sql statement?
Ans1:
Null means nothing. Null is not considered to be an equality, so indexes
are not used. Also, whenever a row has a null value for an index, the rowid
will not be entered in the index. There is no way to prevent a full table
scan with this particular statement.
Hth,
Sybrand Bakker, Oracle DBA
Ans2:
Your problem is that NULL's are not indexed, so the only way to determine
if the ADDRESS_ID column contains a NULL is to scan the table.
Ans3:
I quite agree with previous explainings, but there is idea how you
can force using index. Why don't you fill this field with some special
value ? (-1 for example). So you can check this field if it equals -1.
Reply:
An index doesn't contain NULL-values, so there's no index which is
useable in this case.
Ans4:
Sybrand, Andrew and Serge are right about b-tree indexes, but is the
column a candidate for a bitmap index, which will store null values.
Chris.
Reply:
What you could do is create a concatenated index on address_id and
any other small column in the same table. You could even add a dummy
column with a default value of 'x'.
The NULL, 'x' combinations are included in b-tree indexes.
I tried this out sometime ago and it works.
This is better than creating a dummy value like -1 or so because that
affects the functionality of the software.