Oracle Performance Optimization Operation 11: Like clause as far as possible front-end matching

Because the like parameter is used very frequently, it is highly efficient to use the index for the LIKE clause.

Cases:

SELECT * from the city where name like '%s% ';

The execution plan for the above query uses a full table scan (table ACCESS complete), if it can be modified to:

SELECT * from the city where name like ' s% ';

The execution plan for the query will then become (index RANGE SCAN), successfully using the index of the name field.

This means that the Oracle SQL Optimizer recognizes the LIKE clause for the index as long as the matching end of the query is a specific value.

So when we do like queries, we should try to make the matching end of the query is a specific value, that is, using like ' s% '.

Oracle Performance Optimization Operation 11: Like clause as far as possible front-end matching