Eliminating Redundant Ordering of Data - Aster Execution Engine

Teradata Aster® Developer Guide

Product
Aster Execution Engine
Release Number
7.00.02
Published
July 2017
Language
English (United States)
Last Update
2018-04-13
dita:mapPath
xnl1494366523182.ditamap
dita:ditavalPath
Generic_no_ie_no_tempfilter.ditaval
dita:id
ffu1489104705746
lifecycle
previous
Product Category
Software

With SQL-MapReduce Collaborative Planning, you can add logic to your SQL-MapReduce function that describes the order of the output data to the Planner.

For example, consider this query, which retrieves session information ordered on the columns userid and ts:

SELECT userid, ts, sessionid, pagetype
FROM Sessionize(ON clickstream
  PARTITION BY userid
  ORDER BY ts
  TIMECOLUMN ('ts')
  TIMEOUT (60))
order by userid, ts;

Without SQL-MapReduce Collaborative Planning, the plan consists of the following steps:

  1. Execute the following query and store the output data in the temporary table tmp1:
    sessionize (select * from clickstream order by userid, ts)
  2. Sort the data in tmp1.
    Select * from tmp1 order by userid, ts;
  3. Transfer the sorted data to the Queen.
  4. Return the output data to the application from the Queen.

However, with SQL-MapReduce Collaborative Planning logic added to the Sessionize function, you can tell the Planner that there was no change in data order. This allows the Planner to eliminate the sorting step (see step 2 above), resulting in a significant optimization, as shown in the following figure.

Order example