Improve Performance with Teradata Partition Elimination: The Stored Procedure Approach
Learn how to solve the problem of accessing a table containing many rows using Teradata Partition Elimination with a Stored Procedure.
Teradata Partition Elimination - the Stored Procedure Approach
Occasionally, we may want to select rows in a table's character column that begin with a specific prefix, yet we lack prior knowledge of all the values 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:
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. DWHPro Letters is free. Subscribe to get new issues by email.
SELECT * FROM The_Table WHERE CODE_COLUMN LIKE 'A%';
or
Get the next issue by email.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 were to partition the table, we might end up with a full table scan (FTS), as partition elimination cannot 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);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 inserted into a SQL statement and executed:
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 CURSORFOR
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_TABLEWHERECODE_COLUMN IN (''' || CODE_LIST || ''')GROUP BY 1;'
CALL DBC.SysExecSQL(MySQL);
END;
Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe for free and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.