I got an issue while selecting a from table with huge data and using the following WHERE clause :
WHERE ( (EXTRACT ( MONTH FROM ( VDB.AGG_NW_ACTIVITY_SUBS_DAILY.Call_Start_Date ))(TITLE 'Month')) = (EXTRACT ( MONTH FROM ( current_date - iNtErVaL '1' MONTH ))(TITLE 'Month'))
Of course this is a full table scan, you do some calculation on the Call_Start_Date.
Additionally it will fail due to the interval calculation when you run this on 2015-03-29.
Call_Start_Date BETWEEN TRUNC(ADD_MONTHS(CURRENT_DATE,-1), 'mon') -- first day of previous month
AND LAST_DAY(TRUNC(ADD_MONTHS(CURRENT_DATE,-1), 'mon')) -- last day of previous month
Thanks Dieter for reply,
Is it possible defining variable to get dates from a table so it wont do an all-row scan ?
Using _Date_ST_TM ,_Date_EN_TM
so the optimizer changes the plan and access required partitions, if yes can you please provide me with an example or link of how to create such a variable in teradata SQL ?
Without the actual SELECTs it's hard to tell if both queries return the same result.
Compare EXPLAINs and chekc if the condition is pushed into the Derived Table in Q2.
Otherwise you might try to change UNION to UNION ALL, which performs better (but might return duplicate rows).