Database Reference
In-Depth Information
Now that you've seen what the main available options are that can access data efficiently in different situations,
it's time to describe in detail the access paths used to process SQL statements with weak and strong selectivity.
SQL Statements with Weak Selectivity
To process data efficiently, SQL statements with weak selectivity have to use either a full table scan or a full partition
scan. But in plenty of situations, only full table scans come into play. There are three main reasons for this. First,
partitioning is an Enterprise Edition option. So, you can't take advantage of it if you're using Standard Edition or, of
course, if you don't have the Partitioning option license to use it. Second, even if you're allowed to use the Partitioning
option, not all tables will be partitioned in practice. Third, a table might be partitioned by only a limited number
of columns. As a result, even if a table is partitioned, not all SQL statements that reference it will be able to take
advantage of partitioning, unless all of them reference the partitioning key(s), which is usually not the case in practice.
In particular situations, both full table scans and full partition scans might be avoided by replacing them with
full index scans. In such cases, the idea is to take advantage of indexes not for the purpose of searching for particular
values, but simply because they're smaller than tables.
Full Table Scans
It's possible to perform a full table scan on all heap tables. Because there are no particular requirements for doing this
type of scan, on occasion it may be the only access path possible. The following query is an example. Note that in the
execution plan, the
TABLE ACCESS FULL
operation corresponds to the full table scan. The example also shows how to
force a full table scan with the
full
hint:
SELECT /*+ full(t) */ * FROM t WHERE n2 = 19
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 |
TABLE ACCESS FULL
| T |
----------------------------------
1 - filter("N2"=19)
During full table scans, server processes read all of the table's blocks below the high watermark sequentially.
Server processes up to and including version 10.2 perform buffer cache reads. From version 11.1 onward, the type of
disk I/O operation depends on the number of blocks to be read, the fraction of the target table's blocks that are already
in the buffer cache, and whether the
BUFFER_POOL
storage parameter is set to
KEEP
. Simply put, when the number of
blocks to be read from the disk is low or the
KEEP
buffer pool is used, server processes perform buffer cache reads.
Otherwise, they perform direct reads. This option to perform direct reads is a major enhancement to make sure that
large amounts of data aren't unnecessarly loaded through the buffer cache (from where they will be immediately
discarded).
The minimum number of logical reads performed by a full table scan depends on the number of
blocks
, not on
the number of
rows
. This can lead to suboptimal performance if the table contains a lot of empty or almost-empty
blocks. Clearly, a block has to be read to know whether it contains data. One of the most common scenarios that can
