Queries which are using the LIKE operator usually are causing a full table scan. If the LIKE operator matches from the left, the Teradata Optimizer can easily search in the statistic histograms to get data demographic 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 choose a pessimistic selectivity and chose 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. As this is not ensured, we use a trick to enforce a full table scan on a smaller table.  Let's assume that the 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 query which is typically executed against Table1: SELECT * FROM Table1 WHERE colA like '%ABC%'; In order 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 needUsually, queries which 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
Teradata Golden Tuning Tipps 2017 – Take your Skills to the next Level!
The Teradata Access Paths

Our Reader Score
[Total: 2    Average: 5/5]
Tuning the Teradata LIKE Operator written by Roland Wenzlofsky on April 30, 2018 average rating 5/5 - 2 user ratings

LEAVE A REPLY

Please enter your comment!
Please enter your name here