 |
|
Table Access via Indexes
Oracle Tips by Burleson Consulting |
Within Oracle, all indexes are accessed in a
transparent fashion and there is very little that we can do to alter
the way that Oracle utilizes an index. However, there are numerous
internal techniques that we can use to change index access. I will
cover these in detail in Chapter 20.
In the meantime, let’s turn our attention to
methods that can be used to change the Oracle table structures to
reduce the amount of disk I/O that occurs when Oracle accesses
tables. We will examine how resequencing rows can improve the
performance of index range scans and see how the table storage
parameters can affect the performance of Oracle insert, update,
and delete statements.
It is
important to recognize that there is a hierarchical relationship
between the SQL statement, the execution plan, and the table access
method (see Figure 6-3).
Figure 3: The levels
of SQL execution
The SQL source code is used to generate the
execution plan. The execution plan, in turn, dictates the table
access methods. This is a clear hierarchy because a single SQL
statement may be represented by many execution plans, depending on
the optimizer_mode, hints, and the nature of the CBO
statistics. Each execution plan may generate several table access
methods, including full-table scans, index scans, and ROWID index
access.
As I noted, each execution plan may combine
several table access methods, and you must understand the low-level
table access methods because they determine the elapsed time for the
query. Next, let’s look at the ways that the table access method may
be changed.
Changing Table Access Methods
Because there are many different ways to
write a SQL statement that provides identical results, one of the
goals of tuning table access is to be able to rewrite SQL statements
to get the desired execution plan. We will cover this topic in great
detail in Chapter 19, but I need to introduce this important concept
here because rewriting SQL can dramatically change the table access
method.
There are many things that can be done within
Oracle to change the table access method for a SQL statement. These
include:
-
Changing an index from unique to non-unique
-
Manually rewriting the SQL statement
-
Adding or deleting an index
-
Forcing a change with a hint
-
Changing the optimizer_goal or
optimizer_mode
In order to appreciate how these changes will
affect the execution plan for a SQL statement, let’s examine each of
these scenarios.
Changing Indexes from Unique to Non-unique
Changing index structures to improve
performance is most commonly seen in cases where subqueries are
specified. The savvy Oracle SQL tuner is always on the lookout for
both correlated and noncorrelated subqueries for several reasons.
The foremost is to search for opportunities for replacing the
subquery with a standard join, and the other is to examine the
uniqueness of the indexes in the subquery to see if changing the
index structure can change the table access method.
While we will examine the tuning of
subqueries in detail in Chapter 19, for now, just note that
subqueries can be specified in any of the following formats:
Where
emp_name IN (subuery)
Where EXISTS (Subquery)
Where emp_name = (Subquery)
To see how changing an index can change the
table access method, let’s examine a simple example. The query that
follows is intended to show all employees who received a bonus. In
the Oracle demo database, bonuses are stored in a separate bonus
table. To perform this query, we must specify the rows we want in
the emp table and then look for matching rows in the bonus
table:
select /*+
first_rows */
ename,
hiredate,
comm
from
emp
where
ename IN (select ename from bonus)
;
Here is the execution plan, where nonunique indexes
have been created on the ename columns in the emp and
bonus tables:
OPERATION
----------------------------------------------------------------------
OPTIONS OBJECT_NAME
POSITION
------------------------------ ----------------------------
----------
SELECT STATEMENT
331
NESTED LOOPS
1
VIEW
VW_NSO_1
1
SORT
UNIQUE
1
TABLE ACCESS
FULL BONUS
1
TABLE ACCESS
BY INDEX ROWID EMP
2
INDEX
RANGE SCAN ENAME_IDX
1
Why do we see the view in the
execution plan when we know that both entities are tables? When the
plan table OPERATION column contains the VIEW object with a
object_name like VW_NSO_1, the operation represents a nested
select operation.
Now, watch what happens when we replace the
nonunique indexes with unique indexes on the ename column:
SQL> create
unique index ename_idx on emp (ename);
Index created.
SQL> create unique index ename_bonus_idx on bonus (ename);
Index created.
Now we rerun the explain plan to see the execution
plan for our query:
OPERATION
----------------------------------------------------------------------
OPTIONS OBJECT_NAME
POSITION
------------------------------ ----------------------------
----------
SELECT STATEMENT
1
NESTED LOOPS
1
TABLE ACCESS
FULL EMP
1
INDEX
UNIQUE SCAN ENAME_BONUS_IDX
2
We now have change the execution plan from a nested
select to a standard nested index scan. Note how the presence of a
unique rather than non-unique index radically changed the execution
plan for the SQL statement.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
