Teradata has incorporated several analytical functions from Teradata Aster in its recent releases. Although many of these functions are tailored for web click analysis, Teradata Antiselect has proven valuable for specific applications.
What is the Teradata Antiselect function?
Teradata’s Antiselect function allows for the reversal of column selection logic. Rather than specifying columns to select, Antiselect can be used to specify columns that should not be included in the result set, leaving all other columns present.
What is the syntax of Teradata Antiselect?
Below is our table example:
CREATE TABLE DWHPRO.AntiSelect ( a BIGINT, b BIGINT, c BIGINT, d BIGINT, e BIGINT, f BIGINT, g BIGINT, h BIGINT ) PRIMARY INDEX (a);
All columns are selected from the table “DWHPRO.Antiselect” except for columns c and e.
SELECT * FROM Antiselect ( ON DWHPRO.AntiSelect USING Exclude (‘c’,’e’) ) AS anti ;
A standard SQL query would appear as follows:
SELECT a,b,d,f,g,h FROM DWHPRO.AntiSelect;
What are useful applications for Teradata Antiselect?
Specific technical columns must be omitted from the comparison to verify if the two tables match. For instance, column c is filled with the CURRENT_TIMESTAMP function in our scenario, which will inevitably result in a different value every time the table is populated.
CREATE TABLE DWHPRO.AntiSelect(a BIGINT,b BIGINT,c TIMESTAMP,) PRIMARY INDEX (a);
INSERT INTO DWHPRO.AntiSelect1SELECT colA,colB,CURRENT_TIMESTAMP FROM OldMapping;
INSERT INTO DWHPRO.AntiSelect2SELECT colA,colB,CURRENT_TIMESTAMP FROM NewMapping;
SELECT * FROM Antiselect (ON DWHPRO.AntiSelect1USING Exclude (‘c’) ) AS anti MINUSSELECT * FROM Antiselect (ON DWHPRO.AntiSelect2USING Exclude (‘c’) ) AS anti;
SELECT * FROM Antiselect (ON DWHPRO.AntiSelect2USING Exclude (‘c’) ) AS antiMINUSSELECT * FROM Antiselect (ON DWHPRO.AntiSelect1USING Exclude (‘c’) ) AS anti;
Teradata Antiselect is particularly convenient when dealing with tables with many columns.
What are the possibilities for excluding columns?
This is our sample table:
CREATE TABLE DWHPRO.AntiSelect(a BIGINT,b BIGINT,c BIGINT,d BIGINT,e BIGINT,f BIGINT,g BIGINT,h BIGINT) PRIMARY INDEX (a);
A list of columns can be excluded with the following syntax (columns c and e are excluded):
SELECT * FROM Antiselect (ON DWHPRO.AntiSelectUSING Exclude (‘c’,’e’) ) AS anti ;
A range of columns can be excluded with the following syntax: (columns c,d,e are excluded):
SELECT * FROM Antiselect ( ON DWHPRO.AntiSelect USING Exclude (‘c:e’) ) AS anti ;
A range of columns can be excluded by index with the following syntax: (columns b,c,d are excluded); indexing starts with the position 0:
SELECT * FROM Antiselect (ON DWHPRO.AntiSelectUSING Exclude (‘[1:3]’) ) AS anti ;
Exclude all columns up to index 2 : (columns a,b,c are excluded):
SELECT * FROM Antiselect (ON DWHPRO.AntiSelectUSING Exclude (‘[2:]’) ) AS anti ;
Exclude all columns after index 2 : (columns c,d,e,f,g,h are excluded):
SELECT * FROM Antiselect (ON DWHPRO.AntiSelectUSING Exclude (‘[:2]’) ) AS anti ;
What is the performance impact of using Teradata Antiselect?
Selecting the required columns in a SQL query should result in the same number of I/Os as using the Teradata Antiselect function. However, the latter requires extra CPU, and our tests with millions of rows have shown that it consumes 2-4 times more CPU. Thus, careful consideration should be given to the use of this function.