Optimization When IMDC Exists - 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

When the specified IMDC exists, the imdc_adapter function need not create the IMDC and load it with data. Therefore, the function need not scan the inputs. To avoid the cost of scanning the inputs (which can be large), the inputs are replaced at planning time with the empty table NC_SYSTEM.NC_DUMMY. This optimization is especially helpful when the query that calls the function is issued repeatedly, as in the following kmeans analytic function.

Without imdc_adapter, the query is:
SELECT * FROM kmeansReduce (
  ON kmeansmap ( 
    ON km_input PARTITION BY ANY
    ON km_centroid DIMENSION ORDER BY clusterid
    centroidstable ('km_centroid')
  ) PARTITION BY clusterid
) ORDER BY clusterid;
With imdc_adapter, the query is:
SELECT * FROM kmeansReduce (
  ON kmeansmap (
    ON imdc_adapter (
      ON km_input PARTITION BY ANY
      imdc (kmeans_data)
    ) PARTITION BY ANY
    ON km_centroid DIMENSION ORDER BY clusterid
    centroidstable ('km_centroid')
  ) PARTITION BY clusterid
) ORDER BY clusterid;
If the IMDC km_centroid does not exist, the function executes the preceding query. If the IMDC km_centroid exists, the function executes the following query.
SELECT * FROM kmeansReduce (
  ON kmeansmap (
    ON imdc_adapter (
      ON NC_SYSTEM.NC_DUMMY PARTITION BY ANY
      imdc (kmeans_data)
    ) PARTITION BY ANY
    ON km_centroid DIMENSION ORDER BY clusterid
    centroidstable ('km_centroid')
  ) PARTITION BY clusterid
) ORDER BY clusterid;