Sub query vs OLAP approach

Database
Enthusiast

Sub query vs OLAP approach

Hi,

Please see below example-

TABLE: loc3 (col1 int, col2 int, col3 int) with below data-

000015,  0000000058789756, 198520

000015,  0000000058843478, 198520

000015,  0000000058891716, 198520

I am trying to find out the record with heighest col2 value-

sel * from loc3 where col2 = (sel max(col2) from loc3);

sel * from loc3 qualify row_number() over(partition by col1 order by col2 desc) = 1;

Can somebody please explain me the differences in terms of query performance/processing with the above 2 approaches (sub query and olap function). What happens in case of huge data and complex queries if we compare the 2 approaches.

I have gone through some existing posts but could not figure out the difference. So wanted to start with a simple example.

TIA

7 REPLIES
Enthusiast

Re: Sub query vs OLAP approach

I am not sure with the performance effect of using these two approaches, but the result will be affected by using either of these.

As with subquery the inner query will select the max from the whole column values, but in case of OLAP functions, it will partition based on the column1 and then find max for each partition.

So in case of subquery you might get reauslt for only a single max value, but with OLAP you will get max for each partition which is more desireable and informative.

Khurram
Enthusiast

Re: Sub query vs OLAP approach

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.

Enthusiast

Re: Sub query vs OLAP approach

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?

Khurram
Senior Supporter

Re: Sub query vs OLAP approach

Hi Khurram,

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
as
(select * from sys_calendar.calendar) with data unique primary index (calendar_Date) on commit preserve rows;

explain
select *
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)
;

explain
select *
from vt_cal
qualify calendar_Date = max(calendar_date) over (partition by month_of_calendar)
;
Enthusiast

Re: Sub query vs OLAP approach

Ulrich,

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!

Khurram
Junior Contributor

Re: Sub query vs OLAP approach

Hi Khurram,

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.

Dieter

Enthusiast

Re: Sub query vs OLAP approach

Hi Dieter,

Thanks for the correction!

Khurram