All Forums Database
c9jad 6 posts Joined 08/04
16 Nov 2006
SQL Question

Is the following the best way to eliminate the days of October 2006 from the result?select *from sys_calendar.calendarwhere (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)

vinod_sugur 22 posts Joined 04/05
17 Nov 2006

The below is best way to execute the query:select *from sys_calendar.calendarwhere 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.

c9jad 6 posts Joined 08/04
17 Nov 2006

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.

cushdan 2 posts Joined 11/06
27 Nov 2006

wouldn't there be less comparisons if you just did:select *from sys_calendar.calendarwhere month_of_year <> 10 or year_of_calendar <> 2006

You must sign in to leave a comment.