Kjell Rilbe wrote:

> 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.
>

I would start by "removing" the index on either F.FTGSTAT or F.USTAT, _and_
either A.AESTAT or A.AETYP. An index on A.ORGNR might be a good thing, and
both A.AESTAT or A.AETYP could be "removed" (depends on selectivity for
A.ORGNR).
You can do it stepwise by modifying the fields by appending an empty
string: e.g. change F.FTGSTAT to F.FTGSTAT||''
I don't remember whether a high value in statistics means high or low
selectivity so I don't know which field is the best candidate for a
change. Add an index for A.ORGNR, and try:
select ...
from SOMETABLE F
where F.FTGSTAT||'' in ('1', '2')
and F.USTAT = '1'
and not exists (select 1
from ANOTHERTABLE A
where A.ORGNR = F.ORGNR
and A.AESTAT||'' in ('1', '2')
and A.AETYP||'' = '1'
)
If indexes are actually dropped, all the concatenation business can be
dropped as well.

--
Aage J.