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.
SELECT *
FROM p1
WHERE recdt = (
SELECT MAX ( recdt )
FROM p2
WHERE p1.script = p2.script
AND p1.param = p2.param
AND p1.hostname = p2.hostname
AND p1.item = p2.item
AND p2.flags NOT IN ( ? ) )
AND p1.script = ?
AND p1.param = ?
AND p1.hostname = ?
AND p1.flags NOT IN ( ? );
Thanks in advance.