Database Reference
In-Depth Information
In the following example, based on the table_expansion.sql script, there's a range-partitioned table with one
partition per quarter of 2014. Notice that the local index it creates is unusable. Later, a usable index partition is built
for a single table partition only:
CREATE TABLE t (
id NUMBER PRIMARY KEY,
d DATE NOT NULL,
n NUMBER NOT NULL,
pad VARCHAR2(4000) NOT NULL
)
PARTITION BY RANGE (d) (
PARTITION t_q1_2014 VALUES LESS THAN (to_date('2014-04-01','yyyy-mm-dd')),
PARTITION t_q2_2014 VALUES LESS THAN (to_date('2014-07-01','yyyy-mm-dd')),
PARTITION t_q3_2014 VALUES LESS THAN (to_date('2014-10-01','yyyy-mm-dd')),
PARTITION t_q4_2014 VALUES LESS THAN (to_date('2015-01-01','yyyy-mm-dd'))
);
CREATE INDEX i ON t (n) LOCAL UNUSABLE;
ALTER INDEX i REBUILD PARTITION t_q4_2014;
Prior to version 11.2, an index scan is completely avoided when a query such as the following is optimized. Even
though the restriction is based on an indexed column, not all required index partitions are usable. Thus, even for
partitions that have a usable index, no index scan is performed:
SELECT *
FROM t
WHERE n = 8
As of version 11.2, to enable the use of the usable index partitions, the query is transformed into a compound
query in which one component query accesses the partitions with the usable index, and the other component query
accesses the partitions with the unusable index. Notice how this is achieved by adding to both component queries a
predicate based on the partition key:
SELECT *
FROM (SELECT *
FROM t
WHERE n = 8
AND d < to_date('2014-10-01','yyyy-mm-dd')
UNION ALL
SELECT *
FROM t
WHERE n = 8
AND d >= to_date('2014-10-01','yyyy-mm-dd')
AND d < to_date('2015-01-01','yyyy-mm-dd')) vw_te
Set to Join Conversion
The purpose of set to join conversion is to avoid sort operations in compound queries involving INTERSECT and MINUS .
This query transformation also postpones the elimination of duplicates to the end of processing for such queries.
 
Search WWH ::




Custom Search