Select statement return result very slow

[Florian]

Hi Uthuras,

it would be helpful to see the access path. However sometimes it makes
sense to rewrite a "not in" to gain better access paths:

I tried the following - hopefully similar - statements in my
environment. Watch the timerons of the different ways to get the same
result set:

Your Original:
61328199680 Timerons
NOT IN to NOT EXISTS:
113382 Timerons

select count(*)
from dss.fncsvcar a
where not exists (select 1 from dss.ar b where b.ar_id=a.ar_id )
NOT IN to 0=(count(*):
52955704 Timerons

select count(*)
from dss.fncsvcar a
where 0=(select count(*) from dss.ar b where b.ar_id=a.ar_id )
NOT IN flattened (my favorite one):
87563 Timerons

select count(*) from dss.fncsvcar a left outer join dss.ar b
on a.ar_id=b.ar_id
where b.ar_id IS NULL
Please check the result if you want to use one of the above
statements, i haven't doublechecked them :)

cheers
Florian

(mybe this post opens a new thread, i got a problem with the google
news-function,sorry for that)

hi all,

One of my customer having warehouse database running on DB2. Recently
when they execute the following query at db2 command line
the exepected result take ages before return result

select count(*) from dss.fncsvcar where ar_id no in (select ar_id fromdss.ar)

Both tables ar_id is not a key field. However, indexes are created
the following order

fncsvcar
--------
create index dss.fncsvcaridx on dss.fncsvcar(ar _id asc);

ar
---
create unique index dss.aridx on dss.ar(ar_id asc);
The fncsvcar table has 3 million records and ar table has 7 million
records. Is there any other way to improve this query?