Teradata Partition Elimination – the Stored Procedure Approach
Occasionally, we may want to choose rows in a table’s character column that begin with a specific prefix, yet we lack prior knowledge of all the names contained in that column. This could include newly introduced codes in the future.
If we are lucky, there might be a convention for these codes, such as: “All code values have to start with an ‘A’ character”.
With SQL, this problem can be resolved using the following approach:
SELECT * FROM The_Table WHERE CODE_COLUMN LIKE ‘A%’;
SELECT * FROM The_Table WHERE SUBSTR(CODE_COLUMN,1,1) = ‘A’;
While the SQL statement above resolves our issue, accessing a table with numerous rows could potentially result in performance problems.
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,
) PRIMARY INDEX (PK)
PARTITION BY (Code_Column);
Fortunately, a Teradata Stored Procedure can resolve this issue.
- 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()
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;’;
FOR TheRow AS CODES_CURSOR CURSOR
CODE_COLUMN,RANK(CODE_COLUMN) AS RNK
IF TheRow.RNK = 1
SET CODE_LIST = ”” || TheRow.CODE_COLUMN || ””;
SET CODE_LIST = CODE_LIST || ‘,”’ || TheRow.CODE_COLUMN || ””;
SET MySQL =’
INSERT INTO TARGET_TABLE
CODE_COLUMN IN (”’ || CODE_LIST || ”’)
GROUP BY 1