DWHPro Insights

Optimizing Queries with LIKE Operator in Teradata

By Roland Wenzlofsky ·

Optimizing Queries with LIKE Operator in Teradata

Maintain Helper whenever Table1 changes, and keep one helper row per PK. A stale helper can omit qualifying rows; duplicate helper keys can multiply them. The abbreviated base-table DDL below is a schema sketch, not a standalone setup script.

Queries using the LIKE operator typically result in a full table scan.

When the LIKE operator matches from the left, the Teradata Optimizer can swiftly search the statistic histograms for demographic data metrics, including row count.

A leading-wildcard predicate such as LIKE ‘%ABC%’ generally cannot use a prefix range lookup. Estimates and access paths still depend on the release, statistics, other predicates and available indexes; inspect EXPLAIN instead of assuming one mandatory plan.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

However, accessing indexes necessitates further qualifying against other columns.

The Teradata Optimizer may perform a full table scan on a smaller NUSI or a Join Index if there are many distinct values.

A narrower helper table makes another access alternative available to the optimizer. It does not force a particular scan or join plan.

Assuming the table we are querying with the LIKE operator appears as follows:

CREATE SET TABLE Table1
(
PK INTEGER NOT NULL,
colA VARCHAR(100),
colB VARCHAR(100),
colC VARCHAR(100),
...
colZ VARCHAR(100)
) UNIQUE PRIMARY INDEX (PK);

Here is a typical query:

SELECT * FROM Table1 WHERE colA like '%ABC%';

To prevent a complete table scan on the large table, we generate a secondary table that solely includes the essential columns necessary for the LIKE comparison (in this instance, “colA” is sufficient) along with the distinct 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);

We have now converted the initial SQL query:

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

The intended plan scans the narrower Helper table and joins matching keys to Table1. Actual geography and join method depend on compatible data types, maps, statistics and optimizer costing; confirm them with EXPLAIN.

The benefit is only applicable if scanning the large table is more costly than scanning the small table and executing a consecutive AMP-local merge join.

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam