fbpx

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.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"rgb(255, 0, 0)"}},"gradients":[]}}]}__CONFIG_colors_palette__
GET OUR TERADATA BOOK

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>