ALTER TO CURRENT

Database
Enthusiast

ALTER TO CURRENT

I need to understand what the issue is with time_zones and ALTER TO CURRENT and why the query plan changes if we dont add intervals. IE a translation into English would be good :-)

This is from the Teradata Documentation.

Source: http://www.info.teradata.com/HTMLPubs/DB_TTU_13_10/index.html#page/SQL_Reference/B035_1184_109A/Alte...

When you build your ALTER TABLE TO CURRENT requests, you should specify modified functions in your partitioning expressions such as DATE - INTERVAL '2' DAY or  CURRENT_DATE - INTERVAL '2' DAY, or some other appropriate adjustment rather than specifying an unmodified DATE or CURRENT_DATE function because requests might be submitted in different time zones than the session time zone in which the ALTER TABLE TO CURRENT request is submitted.Such adjustments ensure that the Optimizer query plans remain the same for the same request regardless of the session time zone.

Thanks Folks !

4 REPLIES
Enthusiast

Re: ALTER TO CURRENT

Hi,

Instead of getting the entire result set, interval is used to extract only particular 'n' days of data because of which your query plan changes. Reg' the time zones, i am not clear about your question?

Enthusiast

Re: ALTER TO CURRENT

Sorry for the question not being clear. I would essentially like to know why adding intervals to our current_date allows the query plan to stay the same. Not sure why it would change if we didnt have intervals.

Thanks

Senior Apprentice

Re: ALTER TO CURRENT

I don't know exactly, but this is probably related to CURRENT_DATE being dependent on your session time zone:

 BTEQ -- Enter your SQL request or BTEQ command:

SET TIME ZONE -11;
SELECT CURRENT_TIMESTAMP(0), CURRENT_DATE;

Current TimeStamp(0) Date
------------------------- --------
2015-01-12 22:53:36-11:00 15/01/12

SET TIME ZONE 11;
SELECT CURRENT_TIMESTAMP(0), CURRENT_DATE;

Current TimeStamp(0) Date
------------------------- --------
2015-01-13 20:53:41+11:00 15/01/13
Enthusiast

Re: ALTER TO CURRENT

Thanks Dieter !