Database Reference
In-Depth Information
To solve the second problem, several approaches are available. Which one you have to use depends on the type
of the SQL statement (that is,
DELETE
,
INSERT
,
SELECT
,
1
and
UPDATE
) and on the type of the segment (that is, table or
index). However, before starting, you should always ask one question when the frequency of execution is high: is
it really necessary to execute those SQL statements against the same data so often? Actually, it's not unusual to see
applications (that implement some kind of polling, for example) that unnecessarily execute the same SQL statement
too often. If the frequency of execution can't be reduced, there are the following possibilities. Note that in most
situations, the goal is to spread the activities over a greater number of blocks in order to solve the problem. The only
exception is when several sessions wait from the same row.
•
If there's contention for a table's blocks because of
DELETE
,
SELECT
, and
UPDATE
statements,
you should reduce the number of rows per block. Note that this is the opposite of the common
best practice to fit the maximum number of rows per block. To store fewer rows per block,
either a higher
PCTFREE
or a smaller block size can be used.
If there's contention for a table's blocks because of
•
INSERT
statements and freelist segment
space management is in use, the number of freelists can be increased. In fact, the goal of
having several freelists is precisely to spread concurrent
INSERT
statements over several
blocks. Another possibility is to move the segment into a tablespace with automatic segment
space management.
•
If there's contention for an index's blocks, there are two possible solutions. First, the index
can be created with the option
REVERSE
. Note, however, that this method doesn't help if the
contention is on the root block of the index. Second, the index can be hash partitioned
(or subpartitioned), based on the leading column of the index key (this creates multiple root
blocks and so helps with root block contention if a single partition is accessed).
The important thing to note about reverse indexes is that range scans on them can't apply restrictions based
on range conditions (for example,
BETWEEN
,
>
, or
<=
). Of course, equality predicates are supported. The following
example, based on the
reserve_index.sql
script, shows that the query optimizer no longer uses the index after
rebuilding it with the
REVERSE
option:
SQL> SELECT * FROM t WHERE n < 10;
--------------------------------------------
| Id | Operation | Name |
--------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| T |
|* 2 | INDEX RANGE SCAN | T_I |
--------------------------------------------
2 - access("N"<10)
SQL> ALTER INDEX t_i REBUILD REVERSE;
SQL> SELECT * FROM t WHERE n < 10;
