Hi All Teradata 12 DB is remote - there is NO possibility to admin READ-ONLY access to views (made on tables 1 to 1) but there is possibility create temp tasbles in TEMP space
SQL assistant DB is big (telecom)
There is rather complex SQL - say SQL1
Optimizer estimate performance like 3.5 hours - really it works for 5-8 minutes - so OK.
SQL1 returns like 1.500 000 entries (not much really for this system)
Problem when new join added to SQL1 ON history table like:
JOIN SSHistotry SSH ON SBS.Subscription_Id = SSH.Subscription_Id AND CAST (CAST('2009-12-01' AS DATE) AS TIMESTAMP(0)) - INTERVAL '1' SECOND BETWEEN SSH.Subs_Segment_Start_Dttm AND SSH.Subs_Segment_End_Dttm
Optimizer estimate 36 hours !!!! when SQL works - (more than 1 hour - then aborted) skew about 99% What could be problems with that ? joins like this are standard in this system and works ok - many times without problems
I try change SSHistotry to other history table - result same What could be ways of optimizing sql in this case ? Creating temp table with index say on Subscription_Id I ll try this way - but may be there are some hints ?