Re: Two records a year apart

1. You are using to_date around an already date column which will disable any index on that column.
2. You are using in with a correlated sub-query which is not usual.
3. You are subtracting 365 from the date which 1 year in four is not one year ago.

Try something like

update mcd.sumps a set a.comp = 1
where exists

   ( select * from mcd.sumps b
     where a.mstore = b.mstore and b.mperiod = add_months( a.mperiod, -12 )    )

Assuming an index on mstore,mperiod, this should report 1 full table scan and 1 index search.

Philip Morrow <cracker_at_mymorrow.com> wrote in message news:_ZY08.319763$oj3.65754398_at_typhoon.tampabay.rr.com...
> I've got a table similar to this
>
> mperiod date
> mstore number
> tc number
> tq number
> tp number
> comp number
>
> I'm using this SQL to determine if, for any one record of say store 154 if
> there is a corisponding record for that store one year prior. It seems to
> be taking a long time... any suggestions on a different way to do this
would
> help, or suggestions on a better way to write this SQL would help too.
>
> The explain plan lists two full table accesses.
>
> update mcd.sumps a set a.comp = 1 where a.mstore in (
> select b.mstore from mcd.sumps b where (to_date(a.mperiod) -
> to_date(b.mperiod)) = 365);
>
>
Received on Tue Jan 15 2002 - 14:31:01 CST