NOT IN & NULL problem

[navink123]

I have to extract data from a table using a query as follows

select * from table A
where A.somecoulmn NOT IN(select anothercolumn from b)
AND
(..........);

The problem I have is that, in table 'A' 50 % of the rows have Null values for column 'somecolumn'. So the above query pulls only the remaining 50 % rows.

I am using the following now

select * from table a
where
(
a.somecoulmn IS NULL OR a.somecoulmn NOT IN(select anothercolumn from b)
)
AND
(.....);
;

Though this works, it is taking a long time. Is there any other way I can achieve the above.

One more doubt. Would it be more faster if I update table A with 0 for 'somecolumn' where its value is null and use my first query.

 

[karluk]

I think your fundamental problem is that Oracle doesn't include null values in indexes. Most likely your first query is using an index which can't be used by the second, since the index no longer references all the rows you are trying to retrieve.

I like your suggestion of replacing the nulls with some actual value that will substitute for the nulls. There's a very good chance that this strategy will fix this particular query. The downside, of course, is that you may not want your table design to reflect the peculiarities of a specific query. There may be other issues which we don't know about that would tend to favor retaining nulls.

 

[vc123]

Hello,

Given:


t1:

X
-
null
null
null
null
1
2
3
4
5

t2:

X
-
3
4

If you execute 'select * from t1 where x not in (select x from t2);', you will lose all the null values from t1 (as you already know). It's not because of Oracle indexes not storing nulls, but the reason is that the predicate 'null in (something)' always evaluates to 'false' -- you cannot compare nulls.

What you can do, though, is :

select * from t1 where not exists (select 1 from t2 where x=t1.x);

.... or ...

select t1.* from t1, t2 where t1.x=t2.x(+) and t2.x is null;

Rgds.

 

[sem]

I don't think that indexing a field to retrieve more than 50% of rows is a good idea. I suppose that this query takes long time because it's really not easy to retrieve 50% of rows from a large table. Can you provide real structures, execution plans? I agree with vc123 that in many cases NOT EXISTS may help, but to be sure we need more details.

Regards, Dima

http://seminihin.narod.ru