17.05 - Example: Using the LOCAL ORDER BY Option - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Advanced SQL Engine
Teradata Database
Release Number
January 2021
English (United States)
Last Update

This example uses the same tables as the previous example except that it uses a new version of orders_staging that has an integer column named o_sequence, which it uses to order the rows it selects locally.

     ALTER TABLE orders_staging
     ADD o_sequence INTEGER;

The following INSERT …  SELECT request locally orders the rows of the columns it selects from orders_staging by o_sequence before inserting them into orders.

     INSERT INTO orders
       SELECT o_orderkey, o_custkey, o_orderstatus, o_totalprice+10,
              o_ordertsz, o_comment
       FROM orders_staging 
       LOCAL ORDER BY o_sequence;

For the LOCAL ORDER BY clause, o_sequence does not resolve to a column in the target table orders, so the database resolves using the standard rules for resolution to o_sequence in the underlying table of the SELECT request, orders_staging. Note that orders_staging.o_sequence is not included in the select expression list and the spool is generated locally and sorted on the value of orders_staging.o_sequence before being locally inserting into orders.