Thanks Khurram, yeah i understand that OLAP will get max for each partition and to get the same in subquery i need to modify it(make it correlated). I can say-
sel * from loc3 a1 where col2 = (sel max(col2) from loc3 a2 where a1.col1 = a2.col1);
But i am still looking for an answer in terms of performance.
Well, the corelated subquery processes data a row at a time. While the OLAP function is a set operation, So the performance will be better in case of OLAP (SET Operation) as compared to Corelated(Row By Row Operation).
How much data you have to process with this query?
what do you mean with row at a time for the corelated subquery.
The correlated subquery example below seems to be set based operations to me - not so effcient as the OLAP query in this case but it does classical joins and redistribution, or do I miss something?
create volatile table vt_cal
(select * from sys_calendar.calendar) with data unique primary index (calendar_Date) on commit preserve rows;
from vt_cal c1
where calendar_date in (select max(c2.calendar_date) from vt_cal c2 where c1.month_of_calendar = c2.month_of_calendar group by c2.month_of_calendar)
qualify calendar_Date = max(calendar_date) over (partition by month_of_calendar)
If you can see the manual 25965_TeradataSQL-V14.0.4-studentmanual, Page # 522 explains the difference between a subquery and a corelated sub query. It explains that the main difference between a subquery and Corelated subquery is that subquery is a set operation, while Corelated subquery is a row at a time operation.
Further, The normal subquery start evaluation from inner query and then the outer query is solved. While in case of a corelated subquery a row is picked from outer query and then the inner query is evaluated gainst it.
Please correct me if I have understood anyting incorrect!
i don't have this exact version, but when you read on you will find a remark that this is a only description how a result set is created logically:
Outer references behave as described in the following process.
This process does not mirror the query plan generated by the Optimizer.
It is meant only to describe how a correlated subquery works at a conceptual level.
As Ulrich stated: Teradata rewrites any [correlated] subquery to a join.