How TD Analytical Functions Are Implemented?

Database
Enthusiast

How TD Analytical Functions Are Implemented?

Hi everybody,

Could anybody tell me how TD analytical functions are implemented under the hub?

It is obvious, after running some basic tests, that they are not using the standard Teradata aggregation alghoritms (local aggregation, global ARSA or global RSA). I couldn't find any info in Teradata manuals and seeing only STAT FUNCTION step in Expain doesn't help at all.

I'm usually involved in performance tunning tasks and not understanding how they work internally often makes me helpless. I just cannot predict execution time given the data volume and data demographics. Sometimes it is possible to rewrite them a join to a group by subquery, but often it is not.

I would be very grateful for any information that you might have on the topic. 

Best Regards,

Igor

1 REPLY
Senior Apprentice

Re: How TD Analytical Functions Are Implemented?

Hi Igor,

afaik in the manuals there's not much details on the internal implementation of OLAP functions.

- OLAP functions might skew in spool, cause the data is distributed based on the PARTITION/ORDER columns.

- Another downside is the spool usage, when you Explain it you'll see two spools, i.e. you need twice the spool size when you add an OLAP function.

But in most cases OLAP functions other than the GROUP WINDOW stuff can't be rewritten using old-style syntax (of course rewriting is possible, but the SQL is *much* more complicated and performance is horrible), so you can only try to spot if the usage of an OLAP function is not really needed in this specific case and can be replaced by a simpler construct.

Dieter