fbpx

Tuning the Teradata LIKE Operator

By Roland Wenzlofsky

April 30, 2018


Usually, queries that are using the LIKE operator are causing a full table scan.

If the LIKE operator matches from the left, the Teradata Optimizer can quickly search in the statistic histograms to get demographic data metrics such as the number of rows, etc.

Unfortunately, the Teradata Optimizer has no way to use the statistics for a LIKE match, such as LIKE ‘%ABC%’. In this case, it will expect a pessimistic selectivity and always choose the full table scan as the least expensive access path. The full table scan can be done on the base table, a join index, or a NUSI.

Still, in the absence of additional qualifiers against other columns, no indexed access is possible.

As mentioned above, the Teradata Optimizer may choose to do the full table scan on a smaller NUSI (if the number of distinct values is high) or a Join Index.

But as this is not ensured, we can use a trick to enforce the full table scan on a smaller table.

Let’s assume that the big table we are accessing with the LIKE operator looks like this:

CREATE SET TABLE Table1
(
PK INTEGER NOT NULL
colA VARCHAR(100),
colB VARCHAR(100),
colC VARCHAR(100),

colZ VARCHAR(100)
) UNIQUE PRIMARY INDEX (PK)

Below is a typical query against Table1:

SELECT * FROM Table1 WHERE colA like ‘%ABC%’;

To avoid the full table scan of the big table, we create a second table, only containing the minimum set of columns required for the LIKE match (in our example, we only need “colA”) plus the unique primary key column “PK”:

CREATE SET TABLE Helper
(
PK INTEGER NOT NULL,
colA  VARCHAR(100)
) UNIQUE PRIMARY INDEX (PK);

INSERT INTO Helper SELECT PK, colA FROM Table1;
COLLECT STATISTICS ON Helper COLUMN(PK);

Finally, we transform the original SQL statement:

SELECT Table1.*
FROM
Table1 t01
INNER JOIN
Helper t02
ON
t01.PK = t02.PK
WHERE Helper.colA like ‘%ABC%’;

Teradata will now do a full table scan on the smaller table “Helper”, and an AMP-local rowkey based merge join with the big table “Table1”.

Of course, the benefit is only given if scanning the big table is much more expensive than scanning the small table and doing a consecutive AMP-local merge join.

The Teradata Access Paths
The Teradata Access Paths

__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
Buy the Book Teradata Query Performance Tuning

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

>