Thread: How can I optimize this query

I have the following query

UPDATE accumulator1 SET accumulator1.status = "User Excluded"
WHERE accumulator1.name NOT IN
(SELECT DISTINCT accumulator1.nameFROM accumulator1, diaaffectedstmtsWHERE diaaffectedstmts.stmt like '*'+
accumulator1.name+'*' and 
diaaffectedstmts.xref_type <>  "D");

How can I optimize this query?

Thanks
Jain

On Tue, Sep 09, 2003 at 13:39:10 +0530, Jainendra Kumar P <JAINK@infosys.com> wrote:
> I have the following query
> 
> UPDATE accumulator1 SET accumulator1.status = "User Excluded"
> WHERE accumulator1.name NOT IN
> (SELECT DISTINCT accumulator1.name
>  FROM accumulator1, diaaffectedstmts
>  WHERE diaaffectedstmts.stmt like '*'+ accumulator1.name +'*' and
> diaaffectedstmts.xref_type <>  
>  "D");
> 
> How can I optimize this query?

It will probably work better in 7.4. But for now you might try replacing
NOT IN with NOT EXISTS. As long as accumulator1.name isn't going to be
NULL you should be able to rewrite the subselect to find rows where
name in the subselect matches name in the outer select.