Hi,

I've got this query that seems to take forever to execute (100% CPU for
hours):

select ...
from F
where F.FTGSTAT in ('1', '2')
and F.USTAT = '1'
and not exists (
select 1
from A
where F.ORGNR = A.ORGNR
and A.AESTAT in ('1', '2')
and A.AETYP = '1'
)

Returns this plan:

PLAN (A INDEX (A_FK_ORGNR,A_AESTAT,A_AESTAT))
PLAN (F INDEX (F_USTAT,F_FTGSTAT,F_FTGSTAT))

I have these indices (hopefully self-descriptive names, statistics in
paranthesis):

F_PK_ORGNR (0.000001)
F_FTGSTAT (0.200000)
F_USTAT (0.250000)
A_FK_ORGNR (0.000001)
A_PK_CFAR (0.000001)
A_AESTAT (0.200000)
A_AETYP (0.333333)
A_USTAT (0.250000)

I can create other indices if it would help.

Table A has 1-2 million records.
Table F has 0.5-1 million records.

The query should return about 200 records.

Any ideas?

Thanks,
Kjell
--
--------------------------------------
Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64