Re: Two records a year apart
From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Thu, 17 Jan 2002 11:59:13 -0000
Message-ID: <3c46bc93$0$8509$ed9e5944_at_reading.news.pipex.net>
Date: Thu, 17 Jan 2002 11:59:13 -0000
Message-ID: <3c46bc93$0$8509$ed9e5944_at_reading.news.pipex.net>
and if you are on 8iEE you could create a function based index on to_date(mperiod).
-- Niall Litchfield [Quoted] Oracle DBA Audit Commission UK ***************************************** Please include version and platform and SQL where applicable It makes life easier and increases the likelihood of a good answer ****************************************** "Henk Hultink" <hhu_at_stopspam.stoas.nl> wrote in message news:3c4553af_at_news.wau.nl...Received on Thu Jan 17 2002 - 12:59:13 CET
> Hi Philip,
>
> IMO if you use EXISTS instead of in you'll get a correlated subquery and
not
> two fulle table scans.
>
> update mcd.sumps a
> set a.comp = 1
> where EXISTS (
> select b.mstore
> from mcd.sumps b
> where (to_date(a.mperiod) - to_date(b.mperiod)) = 365);
>
> Besides, maybe you'd better use MONTHS_BETWEEN = 12 to prevent errors due
to
> leap years
>
> ... where MONTHS_BETWEEN (a.mperiod, b.mperiod) = 12)
>
> Regards,
> --
> H. Hultink
> Software Engineer
> Stoas, Division IOAL
> Wageningen, The Netherlands
> "Activating Knowledge"
> http://www.stoas.nl
>
> e-mail: hhu_at_stopspam.stoas.nl
> "Philip Morrow" <cracker_at_mymorrow.com> wrote in message
> news:FYY08.319756$oj3.65752308_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);
> >
> >
> >
> >
>
>