fbpx

Teradata has adopted a variety of analytical functions from Teradata Aster in the latest releases. While most functions are, in our opinion, very specific to web click analysis, Teradata Antiselect is quite useful for specific purposes.

What is the Teradata Antiselect function?

With the analytical function called Teradata Antiselect, Teradata offers a possibility to reverse the logic of selecting the columns. Instead of defining the columns to be selected, the Teradata Antiselect function can be used to define any columns that should not be selected; all other columns are then present in the results set.

What is the syntax of Teradata Antiselect?

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);

In the example below, all columns of the table “DWHPRO.Antiselect” are selected except for the c and e columns:

SELECT * FROM Antiselect (
ON DWHPRO.AntiSelect
USING Exclude (‘c’,’e’) ) AS anti ;

The typical SQL query would look like this:

SELECT a,b,d,f,g,h FROM DWHPRO.AntiSelect;

What are useful applications for Teradata Antiselect?

One possibility is to check whether the two tables are identical, but certain (technical) columns must be excluded from the comparison. In our example, column c is populated with the CURRENT_TIMESTAMP function and is by definition different each time the table is populated:

CREATE TABLE DWHPRO.AntiSelect
(
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 a large number of columns.

What are the possibilities to exclude 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 all columns up to index 2 : (columns a,b,c are excluded):

SELECT * FROM Antiselect (
ON DWHPRO.AntiSelect
USING Exclude (‘[2:]’) ) AS anti ;

Exclude all columns after index 2 : (columns c,d,e,f,g,h are excluded):

SELECT * FROM Antiselect (
ON DWHPRO.AntiSelect
USING Exclude (‘[:2]’) ) AS anti ;

What is the performance impact of using Teradata Antiselect?

While the number of I/Os should be unchanged from a SQL in which the required columns are selected, calling the Teradata Antiselect function requires additional CPU. In our tests with several million rows, we have seen 2-4 times the CPU consumption. The use of this function should, therefore, be carefully considered.

Buy now at Amazon
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>