| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Daylight Saving Code
"Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message news:<xClGa.1222963$F1.143777_at_sccrnsc04>...
> As I said you can make it a function or you don't have to. See 2nd
> sentence. In my example I am updating multiple rows (assuming there are
> multiple rows in the table). OF course, I've actually tried this and
> actually found that as written it is very slow and I had to go to packages
> and use constants initialized in the package body. (using constants in a
> function did not work very efficiently - again the multiple evaluation
> problem.
>
> Alex,
> 1. You should be more careful reading what people wrote.
> 2. Benchmark it instead of immediately saying BS.
>
> Jim
>
Jim,
This is special for you. Leaving aside the stupid select which started
this thread (you'd much better off doing just
select next_day('01-APR-2003', 'Sunday') day1,
next_day('01-SEP-2003', 'Sunday') day2
from dual;)
Here are test results. Dont using oracle 8.1.7 on P166 machine with 96M RAM. Both tests actually ran several times to even out hitratio, results are consistent up to .02 seconds.
1 update fakem1
2 set d = (select sysdate + 1 from dual
3 where SYSDATE between (select
4 decode(to_char(to_date('04/01/2003','MM/DD/YYYY'),'D'),1,
5 to_date('04/01/2003','MM/DD/YYYY')+7, 2,
6 to_date('04/01/2003','MM/DD/YYYY')+6, 3,
7 to_date('04/01/2003','MM/DD/YYYY')+5, 4,
8 to_date('04/01/2003','MM/DD/YYYY')+4, 5,
9 to_date('04/01/2003','MM/DD/YYYY')+3, 6,
10 to_date('04/01/2003','MM/DD/YYYY')+2,
11 to_date('04/01/2003','MM/DD/YYYY')) sday1 from dual) and (select
12 decode(to_char(to_date('09/01/2003','MM/DD/YYYY'),'D'),1,
13 to_date('09/01/2003','MM/DD/YYYY')+7, 2,
14 to_date('09/01/2003','MM/DD/YYYY')+6, 3,
15 to_date('09/01/2003','MM/DD/YYYY')+5, 4,
16 to_date('09/01/2003','MM/DD/YYYY')+4, 5,
17 to_date('09/01/2003','MM/DD/YYYY')+3, 6,
18 to_date('09/01/2003','MM/DD/YYYY')+2,
19* to_date('09/01/2003','MM/DD/YYYY')) sday2 from dual))
SQL> /
10000 rows updated.
Elapsed: 00:00:52.55
2. Using package.
create or replace package faketest
as
daylight_switch date;
switch_back date;
procedure calcdates;
end faketest;
/
create or replace package body faketest
as
procedure calcdates is
begin
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')) ,
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'))
1 begin
2 update fakem1
3 set d = (select sysdate + 1 from dual where sysdate between
4 faketest.daylight_switch and faketest.switch_back);
5* end;
SQL> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:52.78
As you can see, BS case confirmed.
>
> --
> 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.0306130626.2e2c25d0_at_posting.google.com...
> > "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 Wed Jun 18 2003 - 23:57:50 CDT
 |
 |