SQL Question

Database
Enthusiast

SQL Question

Is the following the best way to eliminate the days of October 2006 from the result?

select *
from sys_calendar.calendar
where (month_of_year in(1,2,3,4,5,6,7,8,9,11,12))
or (month_of_year = 10 and year_of_calendar <> 2006)
3 REPLIES
Enthusiast

Re: SQL Question

The below is best way to execute the query:

select *
from sys_calendar.calendar
where not(calendar_date between '2006-10-01' and '2006-10-31');

Explain plan's of two queries:

1) First, we lock a distinct SYS_CALENDAR."pseudo table" for read on
a RowHash to prevent global deadlock for SYS_CALENDAR.CALDATES.
2) Next, we lock SYS_CALENDAR.CALDATES for read.
3) We do an all-AMPs RETRIEVE step from SYS_CALENDAR.CALDATES by way
of an all-rows scan with a condition of (
"(SYS_CALENDAR.CALDATES.cdate < DATE '2006-10-01') OR
(SYS_CALENDAR.CALDATES.cdate > DATE '2006-10-31')") into Spool 1
(group_amps), which is built locally on the AMPs. The size of
Spool 1 is estimated with high confidence to be 55,118 rows. The
estimated time for this step is 0.27 seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.27 seconds.

1) First, we lock a distinct SYS_CALENDAR."pseudo table" for read on
a RowHash to prevent global deadlock for SYS_CALENDAR.CALDATES.
2) Next, we lock SYS_CALENDAR.CALDATES for read.
3) We do an all-AMPs RETRIEVE step from SYS_CALENDAR.CALDATES by way
of an all-rows scan with a condition of (
"(((SYS_CALENDAR.CALDATES.cdate MOD 10000 )/ 100 )= 1) OR
((((SYS_CALENDAR.CALDATES.cdate MOD 10000 )/ 100 )= 2) OR
((((SYS_CALENDAR.CALDATES.cdate MOD 10000 )/ 100 )= 3) OR
((((SYS_CALENDAR.CALDATES.cdate MOD 10000 )/ 100 )= 4) OR
((((SYS_CALENDAR.CALDATES.cdate MOD 10000 )/ 100 )= 5) OR
((((SYS_CALENDAR.CALDATES.cdate MOD 10000 )/ 100 )= 6) OR
((((SYS_CALENDAR.CALDATES.cdate MOD 10000 )/ 100 )= 7) OR
((((SYS_CALENDAR.CALDATES.cdate MOD 10000 )/ 100 )= 8) OR
((((SYS_CALENDAR.CALDATES.cdate MOD 10000 )/ 100 )= 9) OR
((((SYS_CALENDAR.CALDATES.cdate MOD 10000 )/ 100 )= 11) OR
((((SYS_CALENDAR.CALDATES.cdate MOD 10000 )/ 100 )= 12) OR
((((SYS_CALENDAR.CALDATES.cdate MOD 10000 )/ 100 )= 10) AND
(((SYS_CALENDAR.CALDATES.cdate / 10000 )+ 1900 )<> 2006
))))))))))))") into Spool 1 (group_amps), which is built locally
on the AMPs. The size of Spool 1 is estimated with no confidence
to be 52,450 rows. The estimated time for this step is 0.27
seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.27 seconds.

This is because the cdate i.e calendar_date is having unique primary index.

Enthusiast

Re: SQL Question

I should have been more explicit. Let's assume that I do not have the actual date column in my table and that I just have a month column and a year column.

Re: SQL Question

wouldn't there be less comparisons if you just did:

select *
from sys_calendar.calendar
where month_of_year <> 10 or year_of_calendar <> 2006