Desmodromic wrote:
> On May 13, 10:25 am, 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.
>>
>> 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.
>
> 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 = ?
> AND param = ?
> AND hostname = ?
> 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.
Try an OLAP function (may or may not be a winner):
SELECT * FROM (SELECT MAX(recdt) OVER () AS max, p2.*
FROM p2 WHERE p1.script = ?
AND p1.param = ?
AND p1.hostname = ?
AND p1.flags NOT IN ( ? )) AS X WHERE recdt = max;
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
|
