Teradata Partition Elimination with Stored Procedures

0
514
teradata stored procedure

teradata stored procedure

Teradata Partition Elimination – the Stored Procedure Approach

Sometimes we need to select the rows from a table’s character column which are 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 this codes, such as: “all code values have to start with an ‘A’ character”.

In SQL, the problem can be solved like this:

SELECT * FROM The_Table WHERE CODE_COLUMN LIKE ‘A%’;

or

SELECT * FROM The_Table WHERE SUBSTR(CODE_COLUMN,1,1) = ‘A’;

Although above SQL statement solves our problem, we may experience performance issues when accessing a table containing a lot of 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, a Teradata Stored Procedure can be written to solve this problem:

  1. All distinct codes are extracted from the table and written into a variable.
  2. 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;

Questions?
If you have any questions about all this, please ask in the comments! I’ll be paying close attention and answering as many as I can. Thank you for reading. Whatever this blog has become, I owe it all to you.

Our Reader Score
[Total: 4    Average: 4.3/5]
Teradata Partition Elimination with Stored Procedures written by Roland Wenzlofsky on May 22, 2014 average rating 4.3/5 - 4 user ratings

LEAVE A REPLY

Please enter your comment!
Please enter your name here