Archives of the TeradataForum

Message Posted: Mon, 11 Apr 2005 @ 20:55:15 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Since V2R5 the "UNION ALL" has been enhanced to do the following:

"................
For UNION ALL we will now recognize how each set is distributed, and if each piece is hashed the same, save this information. This allows us to avoid unnecessary row redistributions later on in the query plan if join terms are encountered that overlap with the hash columns of the set query. Local geography is picked instead of hashed geography. Also, an additional retrieve step has been eliminated. In some cases this will result in a faster running query, but more important is the reduction in usage of system resources for unnecessary operations. No new syntax is required for this feature. The optimizer will simply attempt this optimization if all sets are base table selects involving the primary index and each set operation is union all. Each set must be hashed on the same columns (by position in the select list). Also, join terms involving these columns must exist to observe the optimization.
.........................."

from am R5 presentation:

".........
The new plan should be picked automatically if the query is written according to the following rules..

Each SELECT in the set query must be from a base table.

Each SELECT must include all PRIMARY INDEX columns in the same position of the SELECT list.

Each set operation must be UNION ALL.
................"

I also found this in the doc, but haven't verified if this is still true at R5.1 or R6.

"..........
When creating views involving UNION ALL set queries, try to include all base table primary index columns in the definition to take advantage of this feature. Also, hash joins must be disabled to utilize this feature.
....................."

Additionally if could use the CHECK CONSTRAINT operator on a column you could see benefits shown in the example below:

     2nd quarter
     CREATE TABLE order2 (
           . . .
     CHECK ((EXTRACT(MONTH FROM O_ORDERDATE) >=4) and
            (EXTRACT(MONTH FROM O_ORDERDATE) <=6))
     UNIQUE PRIMARY INDEX ( O_ORDERKEY );
     3rd quarter
     CREATE TABLE order3 (
           . . .
     CHECK ((EXTRACT(MONTH FROM O_ORDERDATE) >=7) and
            (EXTRACT(MONTH FROM O_ORDERDATE) <=9))
     UNIQUE PRIMARY INDEX ( O_ORDERKEY );
     4th quarter
     CREATE TABLE order4  (
           . . .
     CHECK ((EXTRACT(MONTH FROM O_ORDERDATE) >=10) and
            (EXTRACT(MONTH FROM O_ORDERDATE) <=12))
     UNIQUE PRIMARY INDEX ( O_ORDERKEY );

Ordertbl is a union view of all four quarters

     CREATE VIEW ORDERTBL AS
     SELECT * FROM ORDER1 UNION ALL SELECT * FROM ORDER2 UNION ALL
     SELECT * FROM ORDER3 UNION ALL SELECT * FROM ORDER4;

     SELECT * from ORDERTBL WHERE
         EXTRACT (MONTH FROM O_ORDERDATE) >= 5 AND
         EXTRACT (MONTH FROM O_ORDERDATE) <= 8 ;

This query, that selects orders made between May and August, only accesses Order2 and Order3 tables.

Before Teradata Database V2R5.0, this would access all the tables

	<Prev	Next>		<<First	<Prev	Next>	Last>>