Re: Daylight Saving Code

"Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message news:<LcaGa.171841$M01.80694_at_sccrnsc02>...
> nope. tried that first. Why is his way slower?
>
> let's say he makes it a function so one can do:(I'm making it a function so
> I don't type the whole thing, but you don't have to make it a function, you
> can type the darn thing)
> update mytable set mydate=convert_to_daylight(mydate) where ...
>
> For every row it is going to have to evaluate to_date something like 15 or
> 16 times. In my case once he calls the package to_date is evaluated (to set
> up the constants) 15 or 16 times total.
>

BS. There is no need to create a function. Just write subquery :

update mytable set mydate = (select ...)

Oracle, at least since v8.0, calculates expressions with literals only once per SQL statement. So, in your case, it does 16 calculations, exactly like with you package. Of course, if separate update statement is run for every row updated, it's going to take much more time. But who said you need to do that? Just run update for all rows you need to update.

> So 15 times * # of rows is going to be slower than 15 times per session that
> calls the package. I took an update from 45 minutes to 2 doing this very
> technique. (thanks to the asktom site. Thanks Tom Kyte)
>
> Jim
>
>
>
> --
> Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
> with family. Remove the negative part, keep the minus sign. You can figure
> it out.
> "Alex Filonov" <afilonov_at_yahoo.com> wrote in message
> news:336da121.0306120643.7cbcd533_at_posting.google.com...
> > "Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message
> news:<fgUFa.1206020$F1.142726_at_sccrnsc04>...
> > > I've done this before and you are better off using a package. (I am not
> sure
> > > your code works, there were some dates during the late 70's where the
> > > daylight savings was something like January and Feb.) Also in a package
> you
> > > can declare some constants and thus if you are using it in an update
> > > statement it will be much more efficient.
> > > (the constants are private to the package body and thus initialized once
> per
> > > session when the package is first used - otherwise you are calling
> to_date
> > > to_char a zillion times per row in an update statement. (very
> inefficient)
> > > Jim
> > >
> >
> > Nah. Oracle has operations with dual hardcoded in the server, so they
> > are incredibly fast. Package would be slower and use more resources
> > (those global variables are allocated somehow).
> >
> > > --
> > > Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
> > > with family. Remove the negative part, keep the minus sign. You can
> figure
> > > it out.
> > > "Aruneesh Salhotra" <asalhotra_at_customink.com> wrote in message
> > > news:a29eb1e7.0306110843.5c979d6e_at_posting.google.com...
> > > > I created a oracle query which detects the start and end date for
> > > > dayight saving. I eventually used this, and passed the current year so
> > > > to detect start/end dates for daylight saving for each year.
> > > >
> > > > I wanted to share the same with everyone.
> > > >
> > > > select SYSDATE x, 0 r1 from dual where SYSDATE between (select
> > > > decode(to_char(to_date('04/01/2003','MM/DD/YYYY'),'D'),1,
> > > > to_date('04/01/2003','MM/DD/YYYY')+7, 2,
> > > > to_date('04/01/2003','MM/DD/YYYY')+6, 3,
> > > > to_date('04/01/2003','MM/DD/YYYY')+5, 4,
> > > > to_date('04/01/2003','MM/DD/YYYY')+4, 5,
> > > > to_date('04/01/2003','MM/DD/YYYY')+3, 6,
> > > > to_date('04/01/2003','MM/DD/YYYY')+2,
> > > > to_date('04/01/2003','MM/DD/YYYY')) sday1 from dual) and (select
> > > > decode(to_char(to_date('09/01/2003','MM/DD/YYYY'),'D'),1,
> > > > to_date('09/01/2003','MM/DD/YYYY')+7, 2,
> > > > to_date('09/01/2003','MM/DD/YYYY')+6, 3,
> > > > to_date('09/01/2003','MM/DD/YYYY')+5, 4,
> > > > to_date('09/01/2003','MM/DD/YYYY')+4, 5,
> > > > to_date('09/01/2003','MM/DD/YYYY')+3, 6,
> > > > to_date('09/01/2003','MM/DD/YYYY')+2,
> > > > to_date('09/01/2003','MM/DD/YYYY')) sday2 from dual);
Received on Fri Jun 13 2003 - 09:26:52 CDT