>Can someone help me figure out why the following update statement is not fully optimized? Thanks!
>
>*** sys(3054) tells me that it is using the curaccount.cchanged index, and the curaccount. xline index,
>**but it doesn't say it is using the account clineid index, and it says the optimization for table account is none.
>*-- as a result I had to abandon this code and replace it with the seek/looping code below it
>*** I have numerous updates statements of the exact same form, they all run slow, haven't tested them all yet.
>
>
>
>
>*-- there is an index on account.clineid called clineid
>*-- there is an index on curaccount.clineid called xline
>*-- there is an index on curaccount.cchanged called xchange
>
>SET ENGINEBEHAVIOR 80 && tried with and without this
>PUBLIC cmemvar
>=SYS(3054,11,"cmemvar")
>UPDATE account SET cacc = curaccount.cacc, cid = curaccount.cid from account join curaccount on account.clineid = curaccount.clineid where curaccount.cchanged='Y'
>?cmemvar

It's telling you that there's no optimization for Account because there are no filters on Account. That line refers to filtering, not joins. For joins, if both sides are indexed, Rushmore chooses the side where it thinks the index gives a bigger bang. What are the relative sizes of the two tables?

Tamar