On May 13, 10:25=A0am, Desmodromic <davies...@[EMAIL PROTECTED]
> wrote:
> The following query has a high cost (over 4 million), uses a nested
> loop join and runs much slower than I would like. Does anyone know if
> is possible to rewrite this query more efficiently (probably without
> the correlated subquery)? If so, could you please show me how.
>
> =A0SELECT *
> =A0 FROM p1
> =A0 WHERE recdt =3D (
> =A0 =A0 =A0 SELECT MAX ( recdt )
> =A0 =A0 =A0 =A0 FROM p2
> =A0 =A0 =A0 =A0 WHERE p1.script =3D p2.script
> =A0 =A0 =A0 =A0 =A0 AND p1.param =3D p2.param
> =A0 =A0 =A0 =A0 =A0 AND p1.hostname =3D p2.hostname
> =A0 =A0 =A0 =A0 =A0 AND p1.item =3D p2.item
> =A0 =A0 =A0 =A0 =A0 AND p2.flags NOT IN ( ? ) )
> =A0 =A0 AND p1.script =3D ?
> =A0 =A0 AND p1.param =3D ?
> =A0 =A0 AND p1.hostname =3D ?
> =A0 =A0 AND p1.flags NOT IN ( ? );
>
> Thanks in advance.
OK, I've spent a bit more time on it and have rewritten it as:
select *
from p1
where (recdt, script, param, hostname, item)
in (select max(recdt), script, param, hostname, item
from p2
where script =3D ?
AND param =3D ?
AND hostname =3D ?
AND flags NOT IN ( ? )
group by script, param, hostname, item)
AND flags NOT IN ( ? );
Cost is down to about 8000 and it runs in 5 seconds. I'd still welcome
any further rewrite advice.
Thanks.
|
