SQL/MP Query Guide

Improving Query Performance Through Query
Design
HP NonStop SQL/MP Query Guide—524488-003
3-24
Using LIKE Predicates
Using LIKE Predicates
LIKE constructs can cause full table scans and can therefore result in inefficient
queries. Avoid using the LIKE predicate when another operator might be more efficient.
Instead, use the equal operator (=) whenever possible.
If you must use LIKE, consider these guidelines:
•
Avoid using the LIKE predicate beginning with a pattern match symbol (% or _), as
in this example:
WHERE LAST_NAME LIKE "%SON"
If the wild-card character (% or _) is used as the leftmost character of a column
value, SQL cannot transform the LIKE predicate into a range predicate for a
bounded search.
Consequently, specifying LIKE, beginning with a pattern match symbol, causes a
full table scan.
•
Avoid constructs such as this:
WHERE LAST_NAME LIKE ?P1
If the LIKE predicate compares a column with a parameter in SQLCI, SQL cannot
transform the LIKE predicate into a range predicate for a bounded search, but
must scan the table instead.
•
Avoid LIKE hostvar when hostvar can represent anything.
SQL assumes the worst case, “%...”, and a full table scan is performed.
How the Optimizer Processes Join Operations
Choosing an execution plan for queries involving a join of two or more tables is an
extension of the process of choosing plans for single-table queries. In addition to
determining how to access a table before the join, the optimizer evaluates different
ways to join the tables.
The optimizer evaluates how each of these four join strategies would perform for the
query:
•
Nested join (also called nested-loop join)
•
Sort merge join
•
Key-sequenced merge join
•
Hash join
Except for the nested join, all other join strategies require the existence of an equijoin
predicate, which relates columns using an equal (=) comparison operator.
The following subsections describe the four join methods, followed by a description of
how the optimizer compares and evaluates the different types of join strategies for a