Database Reference
In-Depth Information
T_4_OCT_2014 46
T_4_NOV_2014 47
T_4_DEC_2014 48
With such a table, if a restriction is applied to the partition key, the query optimizer recognizes it and whenever
possible excludes partitions containing data that is processing-irrelevant. This is possible because the data dictionary
contains the boundaries of the partitions, and therefore, the query optimizer can compare them to the restriction or
join condition specified in the SQL statement. Because of limitations, however, this isn't always possible. The next
subsections show different examples that point out how and when the query optimizer is able to use partition pruning.
throughout this section, only queries are used in the examples. this doesn't mean that partition pruning works
only with queries. actually, it works in the same way for SQL statements such as UPDATE and DELETE . I'm using queries
here for convenience only.
Note
PARTITION RANGE SINGLE
In the following SQL statement, the WHERE clause contains two restrictions: one for each column of the partition key.
In this type of situation, the query optimizer recognizes that only a single partition contains relevant data. As a result,
the PARTITION RANGE SINGLE operation appears in the execution plan. It's essential to understand that its child
operation ( TABLE ACCESS FULL ) isn't a full table scan over the whole table. Instead, only a single partition is accessed.
This is confirmed by the value of the Starts column as well. Which partition is accessed is specified by the Pstart
and Pstop columns:
SELECT * FROM t WHERE n1 = 3 AND d1 = to_date('2014-07-19','yyyy-mm-dd')
----------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 31 | 31 |
|* 2 | TABLE ACCESS FULL | T | 1 | 31 | 31 |
----------------------------------------------------------------
2 - filter("D1"=TO_DATE(' 2014-07-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "N1"=3)
Figure 13-6 is a graphical representation of this behavior.
 
 
Search WWH ::




Custom Search