Database Reference
In-Depth Information
PARTITION t_aug_2014 VALUES LESS THAN (to_date('2014-09-01','yyyy-mm-dd')),
PARTITION t_sep_2014 VALUES LESS THAN (to_date('2014-10-01','yyyy-mm-dd')),
PARTITION t_oct_2014 VALUES LESS THAN (to_date('2014-11-01','yyyy-mm-dd')),
PARTITION t_nov_2014 VALUES LESS THAN (to_date('2014-12-01','yyyy-mm-dd')),
PARTITION t_dec_2014 VALUES LESS THAN (to_date('2015-01-01','yyyy-mm-dd'))
INDEXING ON
)
When an index is created, it's possible to specify whether the indexing property has to be observed (
INDEXING
PARTIAL
) or not (
INDEXING FULL
, this is the default value). The following SQL statement shows how to create a
partial index:
CREATE INDEX i ON t (d)
INDEXING PARTIAL
The key requirement for the use of partial indexes is that the data has to be stored in a partitioned table. Whether
an index is nonpartitioned, local or global isn't relevant. Independently of that, only rows stored in a partition
with
INDEXING ON
are indexed. With a table and an index like the ones created in the previous example, the query
optimizer isn't restricted to either access all data through a table scan or an index scan. Instead, it can take advantage
of the table expansion query transformation (refer to Chapter 6) and, as a result, generate different access paths
depending on whether the data is indexed or not. The following example illustrates such a case:
SQL> SELECT *
2 FROM t
3 WHERE d BETWEEN to_date('2014-11-30 23:00:00','yyyy-mm-dd hh24:mi:ss')
4 AND to_date('2014-12-01 01:00:00','yyyy-mm-dd hh24:mi:ss');
--------------------------------------------------------------------------------
| Id | Operation | Name | Pstart| Pstop |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | VIEW | VW_TE_2 | | |
| 2 | UNION-ALL | | | |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T | 12 | 12 |
|* 4 | INDEX RANGE SCAN | I | | |
| 5 | PARTITION RANGE SINGLE | | 11 | 11 |
|* 6 | TABLE ACCESS FULL | T | 11 | 11 |
--------------------------------------------------------------------------------
4 - access("T"."D">=TO_DATE(' 2014-12-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "D"<=TO_DATE(' 2014-12-01 01:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
6 - filter("D">=TO_DATE(' 2014-11-30 23:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Single-table Hash Cluster Access
In practice, too few databases take advantage of single-table hash clusters. As a matter of fact, when they're correctly
sized and accessed through an equality condition on the cluster key, they provide excellent performance. There are
two reasons for this. First, they need no separate access structure (for example, an index) to locate data—in fact, the
cluster key is enough to locate it. Second, all data related to a cluster key is clustered together. These two advantages
were also demonstrated by the tests summarized in Figures
13-3
and
13-4
earlier in this chapter.
