Database Reference
In-Depth Information
SQL> SELECT last_output_rows, last_cr_buffer_gets, last_cu_buffer_gets
2 FROM v$session s, v$sql_plan_statistics p
3 WHERE s.prev_sql_id = p.sql_id
4 AND s.prev_child_number = p.child_number
5 AND s.sid = sys_context('userenv','sid')
6 AND p.operation_id = 1;
LAST_OUTPUT_ROWS LAST_CR_BUFFER_GETS LAST_CU_BUFFER_GETS
---------------- ------------------- -------------------
40 23 0
Remember that the number of logical reads performed by a full table scan strongly depends on the setting for row
prefetching. Refer to the “Row Prefetching” section earlier in this chapter for an example of this.
Full Partition Scans
When the selectivity is very weak (that is, close to 1), full table scans are the most efficient way to access data. As soon
as the selectivity decreases, many blocks are unnecessarily accessed by full table scans. Because the use of indexes
isn't beneficial with weak selectivity, partitioning is the most common option used to reduce the number of logical
reads. The reason for using partitioning is to take advantage of the query optimizer's ability to exclude the processing
of partitions that contain processing-irrelevant data
a priori
. This feature is called
partition pruning
.
There are two basic prerequisite conditions to capitalize on partition pruning for a given SQL statement. First,
and obviously, a table must be partitioned. Second, a restriction or a join condition on the partition key must be
specified in the SQL statement. If these two requirements are met, the query optimizer can replace a full table scan by
one or several full partition scans. In practice, though, things aren't that easy. In fact, the query optimizer has to deal
with several particular situations that might, or might not, lead to partition pruning. To understand these situations
better, the following sections detail partition pruning basics, as well as more advanced pruning techniques such as
OR
,
multicolumn, subquery, and join-filter pruning. These are followed by some practical advice on how to implement
partitioning. Note that partitioned indexes are discussed in the “SQL Statements with Strong Selectivity” section later
in this chapter.
Range Partitioning
To illustrate how partition pruning works, let's examine several examples based on the
pruning_range.sql
script. The
test table is range partitioned and created with the following SQL statement. To be able to show all types of partition
pruning, the partition key is composed of two columns:
n1
and
d1
. The table is partitioned by four different values of
the
n1
column and by month based on
d1
column. This means there are 48 partitions per year:
CREATE TABLE t (
id NUMBER,
d1 DATE,
n1 NUMBER,
n2 NUMBER,
n3 NUMBER,
pad VARCHAR2(4000),
CONSTRAINT t_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (n1, d1) (
PARTITION t_1_jan_2014 VALUES LESS THAN (1, to_date('2014-02-01','yyyy-mm-dd')),
