Teradata Partition Elimination – the Stored Procedure Approach
Sometimes we need to select the rows from a table’s character column starting with a particular prefix, but we don’t know all existing names of this column in advance. For example, in the future, there could be new codes introduced.
If we are lucky, there might be a convention for these codes, such as: “all code values have to start with an ‘A’ character”.
In SQL, we can solve the problem like this:
SELECT * FROM The_Table WHERE CODE_COLUMN LIKE ‘A%’;
or
SELECT * FROM The_Table WHERE SUBSTR(CODE_COLUMN,1,1) = ‘A’;
Although the above SQL statement solves our problem, we may experience performance issues when accessing a table containing many rows.
Even if the column CODE_COLUMN would partition the table, we might end up with a full table scan (FTS), as partition elimination can’t be applied if a LIKE or SUBSTR function is defined on the partition column(s):
CREATE TABLE The_Table
(
PK INTEGER NOT NULL,
Code_Column CHAR(100)
) PRIMARY INDEX (PK)
PARTITION BY (Code_Column);
Luckily, we can write a Teradata Stored Procedure to solve this problem:
- All distinct codes are extracted from the table and written into a variable.
- The list of code values is dynamically pasted into a SQL statement and executed:
REPLACE PROCEDURE MY_SP()
BEGIN
DECLARE CODE_LIST VARCHAR(3200);
SET MySQL = ‘CREATE VOLATILE MULTISET TABLE MY_CODES AS
(
SELECT CODE_COLUMN FROM THE_TABLE WHERE CODE_COLUMN LIKE ”A%” GROUP BY 1
) WITH DATA PRIMARY INDEX (CODE_COLUMN) ON COMMIT PRESERVE ROWS;’;
CALL DBC.SysExecSQL(MySQL);
SET CODE_LIST=”’Ax”’;
FOR TheRow AS CODES_CURSOR CURSOR
FOR
SELECT
CODE_COLUMN,RANK(CODE_COLUMN) AS RNK
FROM MY_CODES
DO
IF TheRow.RNK = 1
THEN
SET CODE_LIST = ”” || TheRow.CODE_COLUMN || ””;
ELSE
SET CODE_LIST = CODE_LIST || ‘,”’ || TheRow.CODE_COLUMN || ””;
END IF;
END FOR;
SET MySQL =’
INSERT INTO TARGET_TABLE
SELECT PK,COUNT(*)
FROM THE_TABLE
WHERE
CODE_COLUMN IN (”’ || CODE_LIST || ”’)
GROUP BY 1
;’
CALL DBC.SysExecSQL(MySQL);
END;