The comparison section supplies both three-column target definitions; provide the OldMapping and NewMapping source tables. Run the separate demonstrations with unused table names. Two directional MINUS checks compare distinct rows; they do not prove that duplicate multiplicities match.
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.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookSELECT * 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?
Sometimes specific technical columns must be omitted from a comparison to verify whether two tables match. For instance, in our scenario column c is populated with the CURRENT_TIMESTAMP function, which will inevitably produce a different value every time the table is loaded.
CREATE TABLE DWHPRO.AntiSelect1
(a BIGINT, b BIGINT, c TIMESTAMP) PRIMARY INDEX(a);
CREATE TABLE DWHPRO.AntiSelect2
(a BIGINT, b BIGINT, c TIMESTAMP) PRIMARY INDEX(a);
INSERT INTO DWHPRO.AntiSelect1 SELECT colA,colB,CURRENT_TIMESTAMP FROM OldMapping;
INSERT INTO DWHPRO.AntiSelect2 SELECT colA,colB,CURRENT_TIMESTAMP FROM NewMapping;
SELECT * FROM Antiselect (ON DWHPRO.AntiSelect1 USING Exclude ('c') ) AS anti MINUS SELECT * FROM Antiselect (ON DWHPRO.AntiSelect2 USING Exclude ('c') ) AS anti;
SELECT * FROM Antiselect (ON DWHPRO.AntiSelect2 USING Exclude ('c') ) AS anti MINUS SELECT * FROM Antiselect (ON DWHPRO.AntiSelect1 USING 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.AntiSelect USING 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.AntiSelect USING Exclude ('[1:3]') ) AS anti ;
Exclude columns from zero-based index 2 through the end: c,d,e,f,g,h are excluded; a,b remain.
SELECT * FROM Antiselect (ON DWHPRO.AntiSelect USING Exclude ('[2:]') ) AS anti ;
Exclude columns from the beginning through zero-based index 2: a,b,c are excluded; d,e,f,g,h remain.
SELECT * FROM Antiselect (ON DWHPRO.AntiSelect USING Exclude ('[:2]') ) AS anti ;
What is the performance impact of using Teradata Antiselect?
Selecting explicit columns and using Antiselect can yield similar data access, but operator overhead and plans depend on the workload and release. The earlier 2–4× CPU observation was specific to a historical test and should not be used as a general multiplier.
Deciding what to change in your data platform?
Compare optimisation, modernisation and migration against your workload and business requirements.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book