Example 2: Using a LIMIT clause in the SQL query - Aster Client

Teradata Aster® Client Guide

Product
Aster Client
Release Number
7.00
Published
May 2017
Language
English (United States)
Last Update
2018-04-13
dita:mapPath
hki1475000360386.ditamap
dita:ditavalPath
Generic_no_ie_no_tempfilter.ditaval
dita:id
B700-2005
lifecycle
previous
Product Category
Software

To issue the same query in ACT using the SQL LIMIT clause to limit results to 1000 rows:

SELECT * FROM FOO, BAR where FOO.A = BAR.A LIMIT 1000;

Here is how the query will execute:

  1. ACT issues the SQL statement to the queen.
  2. The queen planner identifies LIMIT 1000 and pushes down the following query to each of the workers:
    SELECT * FROM FOO, BAR where FOO.A = BAR.A LIMIT 1000;
  3. Each worker computes the equi-join as dictated by the FOO.A = BAR.A LIMIT 1000 constraint. This allows for optimizations where the entire equi-join computation need not be done at each worker.
  4. The queen fetches results from the workers.
  5. ACT fetches results from the queen.

The difference between the two approaches occurs in step 3 of both examples. Using the SQL LIMIT clause (Example 2), the workers are allowed to compute with the constraint of LIMIT 1000, whereas in Example 1 they have to compute the entire equi-join.

So, as you can see, fetch-limit via server-side cursors does not translate into the workers doing a LIMIT 1000 on their individual slice of data. Therefore, if the use case calls for it, an Aster Database power-user should be aware that using the SQL LIMIT clause can speed up query execution dramatically in Aster Database.