Select satement return result very slow

[uthuras]

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 from
dss.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?

Thanks
Uthuras

[Norbert Munkel]

Hi,

ut*****@hotmail.com wrote:

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

which means, that this query ran within reasonable time before and the
behaviour then suddenly changed?

How old are statistics? (stats_time from syscat.tables)

regards,

Norbert

[Mark A]

<ut*****@hotmail.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

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 from
dss.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?

Thanks
Uthuras

Your query is doing a tablespace scan in the subselect each time. Try this:

SELECT
count(*)
FROM dss.fncsvcar a
WHERE a.ar_id not in
(SELECT ar_id
FROM dss.ar b
WHERE b.ar_id = a.ar_id )

Also, make sure you have run runstats command since you have created the
indexes.

[Mark Yudkin]

The IN returns 7 million rows that you have to scan. Try to convince DB2 to
do a semijoin (see the documentation on the relevant registry variable)
and/or reformulate your query to use EXISTS:
select count(*) from dss.fncsvcar f where exists (select * from dss.ar d
where d.ad_id = f.ar_id)

<ut*****@hotmail.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

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 from
dss.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?

Thanks
Uthuras