 |
|
Tuning SQL with Built-in Functions and
Special Operators
Oracle Tips by Burleson Consulting |
Oracle provides a wealth of non-ANSI built-in
functions to improve the functionality of SQL statements to
transform datatypes. These built-in functions, commonly called BIFs,
are widely used to improve the ability of SQL to transform column
datatypes for easier table access.
As you will learn, the main problem with the
use of BIFs in SQL statements is that they can cause unnecessary
full-table scans. The remedy to this problem is to rewrite the SQL
without BIFs or add a function-based index to remove the large-table
full-table scan.
This chapter has two areas, the use of the
like and case statements, and the section on Oracle BIFs. Both of
these areas are very important for a full knowledge of Oracle SQL
tuning.
Using the like and case Clauses in SQL
While the case and like operators are not
built-in functions, they are closely related and serve many of the
same purposes as BIFs. You already know that BIFs can be made to use
an index by creating a matching function-based index, but the
behaviors of the like and case clauses are very
subtle.
Let’s begin by looking at one of the most
popular string conversion clauses, the like clause.
Using the like Clause in Oracle Queries
The like clause is one of the most
popular SQL clauses because it can be used to easily extract a
string anywhere in a text column. Because the like parameter
is used so frequently, it is a great benefit to understand how
Oracle can use indexes and alternative execution plans to service
these types of queries.
As you know, the percent (%) operator is used
with the like clause as a mask, and the % will match any
character. For example, if we wanted all names containing “smith,”
we could issue a query as follows:
select
ename
from
emp
where
ename like '%smith%'
;
Here is the output. Note that "smith" can
appear anywhere in the output. As you see, the like clause is
a very useful tool for extracting substrings from textual column
output.
ENAME
----------
smith
smithson
havensmith
nesmith
But how can we get good Oracle SQL
performance using the like clause? To answer this question,
we must evaluate how the Oracle SQL optimizer uses indexes with the
like clause.
Let’s begin by looking at a query that uses
the like clause to find all employee names beginning with an
“S”.
select
ename
from
emp
where
ename like 'S%'
;
Here is the execution plan, and you see that our query
was able to utilize the emp_ename index to service the query.
This means that the Oracle SQL optimizer will recognize the like
clause for indexing so long as the leading edge of the query is a
literal value.
OPERATION
----------------------------------------------------------------------
OPTIONS OBJECT_NAME
POSITION
------------------------------ ----------------------------
----------
SELECT STATEMENT
1
INDEX
RANGE SCAN
EMP_ENAME
1
However, the like operator will not work if a %
is placed in the left-hand side of the query. Hence, the query that
follows should not be able to use the index because there is a
wildcard at the leading edge of the like clause format mask.
select
ename
from
emp
where
ename like '%TH%'
;
Here is a surprise. While we might expect a full-table
scan, we see an index full scan. This is because even though the SQL
optimizer was not able to read the leading edge of the index, the
index full scan can be used because the index nodes contain
everything required to answer the query.
OPERATION
----------------------------------------------------------------------
OPTIONS
OBJECT_NAME
POSITION
------------------------------ ----------------------------
----------
SELECT STATEMENT
1
INDEX
FULL SCAN
EMP_ENAME
1
Next, let’s look at the SQL case statement
and see how it can reduce the number of full-table scans.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
