Example: Using the LOCAL ORDER BY Option
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 Teradata 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.