1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
|
set echo on
set timing on
drop table test purge;
create table test (a date);
insert into test
select rownum / (24 * 60) + sysdate - 1000
from dba_objects, dba_objects
where rownum <= 10000000;
select count(*)
from test
where (-- condition "normale"
TO_CHAR(a, 'YYYY') = TO_CHAR(SYSDATE, 'YYYY')
AND TO_CHAR(a, 'WW') = (TO_CHAR(SYSDATE, 'WW') - 1)
)
OR (-- oui ... 1ère semaine de l'année - 1 = dernière semaine de l'année précédente (= 52) !
TO_CHAR(a, 'YYYY') = (TO_CHAR(SYSDATE, 'YYYY') - 1)
AND TO_CHAR(a, 'WW') = '52'
AND TO_CHAR(SYSDATE, 'WW') = '01'
)
COUNT(*)
----------------------
10080
1 rows selected
16,722ms elapsed
declare
fin date;
debut date;
cnt number;
begin
fin := next_day (trunc(sysdate) - 7, 'lundi');
debut := next_day (trunc(sysdate) - 14, 'lundi');
select count(*)
into cnt
from test
where a >= debut
and a < fin;
end;
anonymous block completed
695ms elapsed |
Partager