3 Partitioning for Availability, Manageability, and Performance
Partitioning enables availability, manageability, and performance.
This chapter provides high-level insight into how partitioning enables availability, manageability, and performance. Guidelines are provided on when to use a given partitioning strategy. The main focus is the use of table partitioning, although most of the recommendations and considerations apply to index partitioning as well.
This chapter contains the following sections:
3.1 Partition Pruning
Partition pruning is an essential performance feature for data warehouses.
In partition pruning, the optimizer analyzes FROM
and WHERE
clauses in SQL statements to eliminate unneeded partitions when building the partition access list. This functionality enables Oracle Database to perform operations only on those partitions that are relevant to the SQL statement.
The following topics are discussed:
3.1.1 Benefits of Partition Pruning
Partition pruning dramatically reduces the amount of data retrieved from disk and shortens processing time, thus improving query performance and optimizing resource utilization.
If you partition the index and table on different columns (with a global partitioned index), then partition pruning also eliminates index partitions even when the partitions of the underlying table cannot be eliminated.
Depending upon the actual SQL statement, Oracle Database may use static or dynamic pruning. Static pruning occurs at compile-time, with the information about the partitions accessed beforehand. Dynamic pruning occurs at run-time, meaning that the exact partitions to be accessed by a statement are not known beforehand. A sample scenario for static pruning is a SQL statement containing a WHERE
condition with a constant literal on the partition key column. An example of dynamic pruning is the use of operators or functions in the WHERE
condition.
Partition pruning affects the statistics of the objects where pruning occurs and also affects the execution plan of a statement.
3.1.2 Information That Can Be Used for Partition Pruning
Partition pruning can be performed on partitioning columns.
Oracle Database prunes partitions when you use range, LIKE
, equality, and IN
-list predicates on the range or list partitioning columns, and when you use equality and IN
-list predicates on the hash partitioning columns.
On composite partitioned objects, Oracle Database can prune at both levels using the relevant predicates. For example, see the table sales_range_hash
, which is partitioned by range on the column s_saledate
and subpartitioned by hash on the column s_productid
in Example 3-1.
Oracle uses the predicate on the partitioning columns to perform partition pruning as follows:
-
When using range partitioning, Oracle accesses only partitions
sal99q2
andsal99q3
, representing the partitions for the third and fourth quarters of 1999. -
When using hash subpartitioning, Oracle accesses only the one subpartition in each partition that stores the rows with
s_productid=1200
. The mapping between the subpartition and the predicate is calculated based on Oracle's internal hash distribution function.
A reference-partitioned table can take advantage of partition pruning through the join with the referenced table. Virtual column-based partitioned tables benefit from partition pruning for statements that use the virtual column-defining expression in the SQL statement.
Example 3-1 Creating a table with partition pruning
CREATE TABLE sales_range_hash( s_productid NUMBER, s_saledate DATE, s_custid NUMBER, s_totalprice NUMBER) PARTITION BY RANGE (s_saledate) SUBPARTITION BY HASH (s_productid) SUBPARTITIONS 8 (PARTITION sal99q1 VALUES LESS THAN (TO_DATE('01-APR-1999', 'DD-MON-YYYY')), PARTITION sal99q2 VALUES LESS THAN (TO_DATE('01-JUL-1999', 'DD-MON-YYYY')), PARTITION sal99q3 VALUES LESS THAN (TO_DATE('01-OCT-1999', 'DD-MON-YYYY')), PARTITION sal99q4 VALUES LESS THAN (TO_DATE('01-JAN-2000', 'DD-MON-YYYY'))); SELECT * FROM sales_range_hash WHERE s_saledate BETWEEN (TO_DATE('01-JUL-1999', 'DD-MON-YYYY')) AND (TO_DATE('01-OCT-1999', 'DD-MON-YYYY')) AND s_productid = 1200;
3.1.3 How to Identify Whether Partition Pruning Has Been Used
Whether Oracle uses partition pruning is reflected in the execution plan of a statement, either in the plan table for the EXPLAIN
PLAN
statement or in the shared SQL area.
The partition pruning information is reflected in the plan columns PSTART
(PARTITION_START
) and PSTOP
(PARTITION_STOP
). For serial statements, the pruning information is also reflected in the OPERATION
and OPTIONS
columns.
See Also:
Oracle Database SQL Tuning
Guide for more information about EXPLAIN
PLAN
and how to interpret it
3.1.4 Static Partition Pruning
Oracle determines when to use static pruning primarily based on static predicates.
For many cases, Oracle determines the partitions to be accessed at compile time. Static partition pruning occurs if you use static predicates, except for the following cases:
-
Partition pruning occurs using the result of a subquery.
-
The optimizer rewrites the query with a star transformation and pruning occurs after the star transformation.
-
The most efficient execution plan is a nested loop.
These three cases result in the use of dynamic pruning.
If at parse time Oracle can identify which contiguous set of partitions is accessed, then the PSTART
and PSTOP
columns in the execution plan show the begin and the end values of the partitions being accessed. Any other cases of partition pruning, including dynamic pruning, show the KEY value in PSTART
and PSTOP
, optionally with an additional attribute.
The following is an example:
SQL> explain plan for select * from sales where time_id = to_date('01-jan-2001', 'dd-mon-yyyy'); Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------- Plan hash value: 3971874201 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 673 | 19517 | 27 (8)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE| | 673 | 19517 | 27 (8)| 00:00:01 | 17 | 17 | |* 2 | TABLE ACCESS FULL | SALES | 673 | 19517 | 27 (8)| 00:00:01 | 17 | 17 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("TIME_ID"=TO_DATE('2001-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
This plan shows that Oracle accesses partition number 17, as shown in the PSTART
and PSTOP
columns. The OPERATION
column shows PARTITION RANGE SINGLE
, indicating that only a single partition is being accessed. If OPERATION
shows PARTITION RANGE ALL
, then all partitions are being accessed and effectively no pruning takes place. PSTART
then shows the very first partition of the table and PSTOP
shows the very last partition.
An execution plan with a full table scan on an interval-partitioned table shows 1 for PSTART
, and 1048575 for PSTOP
, regardless of how many interval partitions were created.
3.1.5 Dynamic Partition Pruning
Oracle dynamic partition pruning is introduced in this topic.
Dynamic pruning occurs if pruning is possible and static pruning is not possible. The following examples show multiple dynamic pruning cases:
3.1.5.1 Dynamic Pruning with Bind Variables
Statements that use bind variables against partition columns result in dynamic pruning.
The following SQL statement is an example.
SQL> explain plan for select * from sales s where time_id in ( :a, :b, :c, :d); Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------- Plan hash value: 513834092 --------------------------------------------------------------------------------------------------- | Id | Operation | Name |Rows|Bytes|Cost (%CPU)| Time | Pstart| Pstop| --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | |2517|72993| 292 (0)|00:00:04| | | | 1 | INLIST ITERATOR | | | | | | | | | 2 | PARTITION RANGE ITERATOR | |2517|72993| 292 (0)|00:00:04|KEY(I) |KEY(I)| | 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES |2517|72993| 292 (0)|00:00:04|KEY(I) |KEY(I)| | 4 | BITMAP CONVERSION TO ROWIDS | | | | | | | | |* 5 | BITMAP INDEX SINGLE VALUE |SALES_TIME_BIX| | | | |KEY(I) |KEY(I)| --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("TIME_ID"=:A OR "TIME_ID"=:B OR "TIME_ID"=:C OR "TIME_ID"=:D)
For parallel execution plans, only the partition start and stop columns contain the partition pruning information; the operation column contains information for the parallel operation, as shown in the following example:
SQL> explain plan for select * from sales where time_id in (:a, :b, :c, :d); Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------- Plan hash value: 4058105390 ------------------------------------------------------------------------------------------------- | Id| Operation | Name |Rows|Bytes|Cost(%CP| Time |Pstart| Pstop| TQ |INOUT| PQ Dis| ------------------------------------------------------------------------------------------------- | 0| SELECT STATEMENT | |2517|72993| 75(36)|00:00:01| | | | | | | 1| PX COORDINATOR | | | | | | | | | | | | 2| PX SEND QC(RANDOM)|:TQ10000|2517|72993| 75(36)|00:00:01| | |Q1,00| P->S|QC(RAND| | 3| PX BLOCK ITERATOR| |2517|72993| 75(36)|00:00:01|KEY(I)|KEY(I)|Q1,00| PCWC| | |* 4| TABLE ACCESS FULL| SALES |2517|72993| 75(36)|00:00:01|KEY(I)|KEY(I)|Q1,00| PCWP| | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("TIME_ID"=:A OR "TIME_ID"=:B OR "TIME_ID"=:C OR "TIME_ID"=:D)
See Also:
Oracle Database SQL Tuning
Guide for more information about EXPLAIN PLAN
and how to interpret it
3.1.5.2 Dynamic Pruning with Subqueries
Statements that explicitly use subqueries against partition columns result in dynamic pruning.
The following SQL statement is an example.
SQL> explain plan for select sum(amount_sold) from sales where time_id in (select time_id from times where fiscal_year = 2000); Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Plan hash value: 3827742054 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 25 | 523 (5)| 00:00:07 | | | | 1 | SORT AGGREGATE | | 1 | 25 | | | | | |* 2 | HASH JOIN | | 191K| 4676K| 523 (5)| 00:00:07 | | | |* 3 | TABLE ACCESS FULL | TIMES | 304 | 3648 | 18 (0)| 00:00:01 | | | | 4 | PARTITION RANGE SUBQUERY| | 918K| 11M| 498 (4)| 00:00:06 |KEY(SQ)|KEY(SQ)| | 5 | TABLE ACCESS FULL | SALES | 918K| 11M| 498 (4)| 00:00:06 |KEY(SQ)|KEY(SQ)| ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("TIME_ID"="TIME_ID") 3 - filter("FISCAL_YEAR"=2000)
See Also:
Oracle Database SQL Tuning
Guide for more information about EXPLAIN PLAN
and how to interpret it
3.1.5.3 Dynamic Pruning with Star Transformation
Statements that get transformed by the database using the star transformation result in dynamic pruning.
The following SQL statement is an example.
SQL> explain plan for select p.prod_name, t.time_id, sum(s.amount_sold) from sales s, times t, products p where s.time_id = t.time_id and s.prod_id = p.prod_id and t.fiscal_year = 2000 and t.fiscal_week_number = 3 and p.prod_category = 'Hardware' group by t.time_id, p.prod_name; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------ Plan hash value: 4020965003 ------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Pstart| Pstop | ------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 79 | | | | 1 | HASH GROUP BY | | 1 | 79 | | | |* 2 | HASH JOIN | | 1 | 79 | | | |* 3 | HASH JOIN | | 2 | 64 | | | |* 4 | TABLE ACCESS FULL | TIMES | 6 | 90 | | | | 5 | PARTITION RANGE SUBQUERY | | 587 | 9979 |KEY(SQ)|KEY(SQ)| | 6 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 587 | 9979 |KEY(SQ)|KEY(SQ)| | 7 | BITMAP CONVERSION TO ROWIDS | | | | | | | 8 | BITMAP AND | | | | | | | 9 | BITMAP MERGE | | | | | | | 10 | BITMAP KEY ITERATION | | | | | | | 11 | BUFFER SORT | | | | | | |* 12 | TABLE ACCESS FULL | TIMES | 6 | 90 | | | |* 13 | BITMAP INDEX RANGE SCAN | SALES_TIME_BIX | | |KEY(SQ)|KEY(SQ)| | 14 | BITMAP MERGE | | | | | | | 15 | BITMAP KEY ITERATION | | | | | | | 16 | BUFFER SORT | | | | | | | 17 | TABLE ACCESS BY INDEX ROWID| PRODUCTS | 14 | 658 | | | |* 18 | INDEX RANGE SCAN | PRODUCTS_PROD_CAT_IX | 14 | | | | |* 19 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX | | |KEY(SQ)|KEY(SQ)| | 20 | TABLE ACCESS BY INDEX ROWID | PRODUCTS | 14 | 658 | | | |* 21 | INDEX RANGE SCAN | PRODUCTS_PROD_CAT_IX | 14 | | | | ------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("S"."PROD_ID"="P"."PROD_ID") 3 - access("S"."TIME_ID"="T"."TIME_ID") 4 - filter("T"."FISCAL_WEEK_NUMBER"=3 AND "T"."FISCAL_YEAR"=2000) 12 - filter("T"."FISCAL_WEEK_NUMBER"=3 AND "T"."FISCAL_YEAR"=2000) 13 - access("S"."TIME_ID"="T"."TIME_ID") 18 - access("P"."PROD_CATEGORY"='Hardware') 19 - access("S"."PROD_ID"="P"."PROD_ID") 21 - access("P"."PROD_CATEGORY"='Hardware') Note ----- - star transformation used for this statement
Note:
The Cost (%CPU)
and Time
columns were removed from the plan table output in this example.
See Also:
Oracle Database SQL Tuning
Guide for more information about EXPLAIN PLAN
and how to interpret it
3.1.5.4 Dynamic Pruning with Nested Loop Joins
Statements that are most efficiently executed using a nested loop join use dynamic pruning.
The following SQL statement is an example.
SQL> explain plan for select t.time_id, sum(s.amount_sold) from sales s, times t where s.time_id = t.time_id and t.fiscal_year = 2000 and t.fiscal_week_number = 3 group by t.time_id; Explained. SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Plan hash value: 50737729 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6 | 168 | 126 (4)| 00:00:02 | | | | 1 | HASH GROUP BY | | 6 | 168 | 126 (4)| 00:00:02 | | | | 2 | NESTED LOOPS | | 3683 | 100K| 125 (4)| 00:00:02 | | | |* 3 | TABLE ACCESS FULL | TIMES | 6 | 90 | 18 (0)| 00:00:01 | | | | 4 | PARTITION RANGE ITERATOR| | 629 | 8177 | 18 (6)| 00:00:01 | KEY | KEY | |* 5 | TABLE ACCESS FULL | SALES | 629 | 8177 | 18 (6)| 00:00:01 | KEY | KEY | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("T"."FISCAL_WEEK_NUMBER"=3 AND "T"."FISCAL_YEAR"=2000) 5 - filter("S"."TIME_ID"="T"."TIME_ID")
See Also:
Oracle Database SQL Tuning
Guide for more information about EXPLAIN PLAN
and how to interpret it
3.1.6 Partition Pruning with Zone Maps
Partition pruning is enhanced to take advantage of zone maps for pruning of complete partitions. Providing enhanced pruning capabilities provides better performance with less resource consumption and shorter time-to-information.
A zone map is a independent access structure that can be built for a table. During table scans, zone maps enable you to prune disk blocks of a table and partitions of a partitioned table based on predicates on the table columns. Zone maps have no correlation to the partition key columns of a partitioned table, so statements on partitioned tables with zone maps can prune partitions based on non-partition key columns.
See Also:
Oracle Database Data Warehousing Guide for information about zone maps and attribute clustering
Partition pruning with zone maps is especially effective when the zone map column values correlate with partition key column values. For example, the correlation can be between columns of the partitioned table itself, such as a shipping date that has a correlation to the partition key column order date in the same partitioned table, or within the join zone map columns and the partitioned table, such as a join zone map column month description from a dimension table times that correlates with the partition key column day of the partitioned table.
Example 3-2 illustrates partition pruning with zone maps for correlated columns of a partitioned table. Column s_shipdate
in the partitioned table sales_range
correlates with the partition key column order_date
because orders are normally shipped within a couple of days after an order was received.
Due to the correlation of s_shipdate
and the partition key column any selective predicate on this column has a high likelihood to enable partition pruning for the partitioned table sales_range
, without having the column as part of the partitioning key.
The following SELECT
statement looks for all orders that were shipped in the first quarter of 1999:
SELECT * FROM sales_range WHERE s_shipdate BETWEEN to_date('01/01/1999','dd/mm/yyyy') AND to_date('03/01/1999','mm/dd/yyyy');
In the following execution plan for the previous SELECT
statement, zone maps are used for partition pruning and also to prune blocks from the partitions that have to be accessed.
Partition pruning with zone maps is identified by having KEY(ZM)
in the PSTART
and PSTOP
columns of the execution plan. The block level pruning of all accessed partitions is identified by the filter predicate at table access time (id 2
).
Example 3-2 Partitioned table sales_range with attribute clustering and a zone map on a correlated column
CREATE TABLE sales_range( s_productid NUMBER, s_saledate DATE, s_shipdate DATE, s_custid NUMBER, s_totalprice NUMBER) CLUSTERING BY (s_shipdate) WITH MATERIALIZED ZONEMAP PARTITION BY RANGE (s_saledate) (PARTITION sal99q1 VALUES LESS THAN (TO_DATE('01-APR-1999', 'DD-MON-YYYY')), PARTITION sal99q2 VALUES LESS THAN (TO_DATE('01-JUL-1999', 'DD-MON-YYYY')), PARTITION sal99q3 VALUES LESS THAN (TO_DATE('01-OCT-1999', 'DD-MON-YYYY')), PARTITION sal99q4 VALUES LESS THAN (TO_DATE('01-JAN-2000', 'DD-MON-YYYY')));
Example 3-3 Execution plan for partition pruning with zone maps
--------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | | | 1 | PARTITION RANGE ITERATOR | | 58 | 3306 | 3 (0)| 00:00:01 |KEY(ZM)|KEY(ZM)| |* 2 | TABLE ACCESS FULL WITH ZONEMAP| SALES_RANGE | 58 | 3306 | 3 (0)| 00:00:01 |KEY(ZM)|KEY(ZM)| ---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter((SYS_ZMAP_FILTER('/* ZM_PRUNING */ SELECT "ZONE_ID$", CASE WHEN BITAND(zm."ZONE_STATE$",1)=1 THEN 1 ELSE CASE WHEN (zm."MAX_1_S_SHIPDATE" < :1 OR zm."MIN_1_S_SHIPDATE" > :2) THEN 3 ELSE 2 END END FROM "SH"."ZMAP$_SALES_RANGE" zm WHERE zm."ZONE_LEVEL$"=0 ORDER BY zm."ZONE_ID$"',SYS_OP_ZONE_ID(ROWID),TO_DATE(' 1999-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'),TO_DATE(' 1999-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))<3 AND "S_SHIPDATE">=TO_DATE(' 1999-01-01 00:00:00','syyyy-mm-dd hh24:mi:ss') AND "S_SHIPDATE"<=TO_DATE(' 1999-03-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
3.1.7 Partition Pruning Tips
Tips for partition pruning are introduced in this topic.
When using partition pruning, you should consider the following:
Note:
When you manipulate a partition column with any function or transformation, such as CAST
or TRUNC
, partition pruning is not taking place.
3.1.7.1 Data Type Conversions
To get the maximum performance benefit from partition pruning, you should avoid constructs that require the database to convert the data type you specify.
Data type conversions typically result in dynamic pruning when static pruning would have otherwise been possible. SQL statements that benefit from static pruning perform better than statements that benefit from dynamic pruning.
A common case of data type conversions occurs when using the Oracle DATE
data type. An Oracle DATE
data type is not a character string but is only represented as such when querying the database; the format of the representation is defined by the NLS setting of the instance or the session. Consequently, the same reverse conversion has to happen when inserting data into a DATE
field or when specifying a predicate on such a field.
A conversion can either happen implicitly or explicitly by specifying a TO_DATE
conversion. Only a properly applied TO_DATE
function guarantees that the database can uniquely determine the date value and using it potentially for static pruning, which is especially beneficial for single partition access.
Consider the following example that runs against the sales
table. You would like to know the total revenue number for the year 2000. There are multiple ways you can retrieve the answer to the query, but not every method is equally efficient.
explain plan for SELECT SUM(amount_sold) total_revenue FROM sales, WHERE time_id between '01-JAN-00' and '31-DEC-00';
The plan should now be similar to the following:
---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 525 (8)| 00:00:07 | | | | 1 | SORT AGGREGATE | | 1 | 13 | | | | | |* 2 | FILTER | | | | | | | | | 3 | PARTITION RANGE ITERATOR| | 230K| 2932K| 525 (8)| 00:00:07 | KEY | KEY | |* 4 | TABLE ACCESS FULL | SALES | 230K| 2932K| 525 (8)| 00:00:07 | KEY | KEY | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(TO_DATE('01-JAN-00')<=TO_DATE('31-DEC-00')) 4 - filter("TIME_ID">='01-JAN-00' AND "TIME_ID"<='31-DEC-00')
In this case, the keyword KEY
for both PSTART
and PSTOP
means that dynamic partition pruning occurs at run-time. Consider the following case.
explain plan for select sum(amount_sold) from sales where time_id between '01-JAN-2000' and '31-DEC-2000' ;
The execution plan now shows the following:
---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 127 (4)| | | | 1 | SORT AGGREGATE | | 1 | 13 | | | | | 2 | PARTITION RANGE ITERATOR| | 230K| 2932K| 127 (4)| 13 | 16 | |* 3 | TABLE ACCESS FULL | SALES | 230K| 2932K| 127 (4)| 13 | 16 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', "syyyy-mm-dd hh24:mi:ss'))
Note:
The Time
column was removed from the execution plan.
The execution plan shows static partition pruning. The query accesses a contiguous list of partitions 13 to 16. In this particular case, the way the date format was specified matches the NLS date format setting. Though this example shows the most efficient execution plan, you cannot rely on the NLS date format setting to define a certain format.
alter session set nls_date_format='fmdd Month yyyy'; explain plan for select sum(amount_sold) from sales where time_id between '01-JAN-2000' and '31-DEC-2000' ;
The execution plan now shows the following:
----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 525 (8)| | | | 1 | SORT AGGREGATE | | 1 | 13 | | | | |* 2 | FILTER | | | | | | | | 3 | PARTITION RANGE ITERATOR| | 230K| 2932K| 525 (8)| KEY | KEY | |* 4 | TABLE ACCESS FULL | SALES | 230K| 2932K| 525 (8)| KEY | KEY | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(TO_DATE('01-JAN-2000')<=TO_DATE('31-DEC-2000')) 4 - filter("TIME_ID">='01-JAN-2000' AND "TIME_ID"<='31-DEC-2000')
Note:
The Time
column was removed from the execution plan.
This plan, which uses dynamic pruning, again is less efficient than the static pruning execution plan. To guarantee a static partition pruning plan, you should explicitly convert data types to match the partition column data type. For example:
explain plan for select sum(amount_sold) from sales where time_id between to_date('01-JAN-2000','dd-MON-yyyy') and to_date('31-DEC-2000','dd-MON-yyyy') ; ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Pstart| Pstop | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 127 (4)| | | | 1 | SORT AGGREGATE | | 1 | 13 | | | | | 2 | PARTITION RANGE ITERATOR| | 230K| 2932K| 127 (4)| 13 | 16 | |* 3 | TABLE ACCESS FULL | SALES | 230K| 2932K| 127 (4)| 13 | 16 | ---------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Note:
The Time
column was removed from the execution plan.
See Also:
-
Oracle Database SQL Language Reference for details about the
DATE
data type -
Oracle Database Globalization Support Guide for details about NLS settings and globalization issues
3.1.7.2 Function Calls
Functions can limit the ability of the optimizer to perform pruning.
There are several cases when the optimizer cannot perform pruning. One common reasons is when an operator is used on top of a partitioning column. This could be an explicit operator (for example, a function) or even an implicit operator introduced by Oracle as part of the necessary data type conversion for executing the statement. For example, consider the following query:
EXPLAIN PLAN FOR SELECT SUM(quantity_sold) FROM sales WHERE time_id = TO_TIMESTAMP('1-jan-2000', 'dd-mon-yyyy');
Because time_id
is of type DATE
and Oracle must promote it to the TIMESTAMP
type to get the same data type, this predicate is internally rewritten as:
TO_TIMESTAMP(time_id) = TO_TIMESTAMP('1-jan-2000', 'dd-mon-yyyy')
The execution plan for this statement is as follows:
-------------------------------------------------------------------------------------------- |Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 11 | 6 (17)| 00:00:01 | | | | 1 | SORT AGGREGATE | | 1 | 11 | | | | | | 2 | PARTITION RANGE ALL| | 10 | 110 | 6 (17)| 00:00:01 | 1 | 16 | |*3 | TABLE ACCESS FULL | SALES | 10 | 110 | 6 (17)| 00:00:01 | 1 | 16 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(INTERNAL_FUNCTION("TIME_ID")=TO_TIMESTAMP('1-jan-2000',:B1)) 15 rows selected
The SELECT
statement accesses all partitions even though pruning down to a single partition could have taken place. Consider the example to find the total sales revenue number for 2000. Another way to construct the query would be:
EXPLAIN PLAN FOR SELECT SUM(amount_sold) FROM sales WHERE TO_CHAR(time_id,'yyyy') = '2000';
This query applies a function call to the partition key column, which generally disables partition pruning. The execution plan shows a full table scan with no partition pruning:
---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 527 (9)| 00:00:07 | | | | 1 | SORT AGGREGATE | | 1 | 13 | | | | | | 2 | PARTITION RANGE ALL| | 9188 | 116K| 527 (9)| 00:00:07 | 1 | 28 | |* 3 | TABLE ACCESS FULL | SALES | 9188 | 116K| 527 (9)| 00:00:07 | 1 | 28 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(TO_CHAR(INTERNAL_FUNCTION("TIME_ID"),'yyyy')='2000')
Avoid using implicit or explicit functions on the partition columns. If your queries commonly use function calls, then consider using a virtual column and virtual column partitioning to benefit from partition pruning in these cases.
Note:
If any function is applied to the partition key column as part of the filter predicate - explicit or implicit as part of any necessary processing, then partition pruning is not taking place. This is true irrespective of the nature of the function and is true for example for any kind of type conversion, or truncation of values.3.1.7.3 Collection Tables
Collection tables can limit the ability of the optimizer to perform pruning.
The following example illustrates what an EXPLAIN
PLAN
statement might look like when it contains Collection Tables, which, for the purposes of this discussion, are ordered collection tables or nested tables. A full table access is not performed because it is constrained to just the partition in question.
EXPLAIN PLAN FOR SELECT p.ad_textdocs_ntab FROM print_media_part p; Explained. PLAN_TABLE_OUTPUT ----------------------------------------------------------------------- Plan hash value: 2207588228 ----------------------------------------------------------------------- | Id | Operation | Name | Pstart| Pstop | ----------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 1 | PARTITION REFERENCE SINGLE| | KEY | KEY | | 2 | TABLE ACCESS FULL | TEXTDOC_NT | KEY | KEY | | 3 | PARTITION RANGE ALL | | 1 | 2 | | 4 | TABLE ACCESS FULL | PRINT_MEDIA_PART | 1 | 2 | ----------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement
See Also:
Partitioning of Collections in XMLType and Objects for an example of the CREATE
TABLE
statement on which the EXPLAIN
PLAN
is based
3.2 Partition-Wise Operations
Partition-wise operations significantly reduce response time and improve the use of both CPU and memory resources.
Partition-wise joins can reduce query response time by minimizing the amount of data exchanged among parallel execution servers when joins execute in parallel. In Oracle Real Application Clusters (Oracle RAC) environments, partition-wise joins also avoid or at least limit the data traffic over the interconnect, which is the key to achieving good scalability for massive join operations. Parallel partition-wise joins are used commonly for processing large joins efficiently and fast. Partition-wise joins can be full or partial. Oracle Database decides which type of join to use.
In addition to parallel partition-wise joins, queries using the SELECT
DISTINCT
clause and SQL window functions can perform parallel partition-wise operations.
The following topics are discussed:
See Also:
-
Partition-Wise Joins in a Data Warehouse for information about parallel partition-wise operations in a data warehouse environment
-
Oracle Database Data Warehousing Guide for information about data warehousing and optimization techniques
3.2.1 Full Partition-Wise Joins
A full partition-wise join divides a large join into smaller joins between a pair of partitions from the two joined tables.
To use full partition-wise joins, you must equipartition both tables on their join keys, or use reference partitioning.
You can use various partitioning methods to equipartition both tables. These methods are described at a high level in the following topics:
3.2.1.1 Querying a Full Partition-Wise Join
You can query using a full partition-wise join.
Consider a large join between a sales table and a customer table on the column cust_id
, as shown in Example 3-4. The query "find the records of all customers who bought more than 100 articles in Quarter 3 of 1999" is a typical example of a SQL statement performing such a join.
Such a large join is typical in data warehousing environments. In this case, the entire customer table is joined with one quarter of the sales data. In large data warehouse applications, this might mean joining millions of rows. The join method to use in that case is obviously a hash join. You can reduce the processing time for this hash join even more if both tables are equipartitioned on the cust_id
column. This functionality enables a full partition-wise join.
When you execute a full partition-wise join in parallel, the granule of parallelism is a partition. Consequently, the degree of parallelism is limited to the number of partitions. For example, you require at least 16 partitions to set the degree of parallelism of the query to 16.
Example 3-4 Querying with a full partition-wise join
SELECT c.cust_last_name, COUNT(*) FROM sales s, customers c WHERE s.cust_id = c.cust_id AND s.time_id BETWEEN TO_DATE('01-JUL-1999', 'DD-MON-YYYY') AND (TO_DATE('01-OCT-1999', 'DD-MON-YYYY')) GROUP BY c.cust_last_name HAVING COUNT(*) > 100;
3.2.1.2 Full Partition-Wise Joins: Single-Level - Single-Level
A single-level to single-level full partition-wise join is the simplest method: two tables are both partitioned by the join column.
In the example, the customers
and sales
tables are both partitioned on the cust_id
columns. This partitioning method enables full partition-wise joins when the tables are joined on cust_id
, both representing the same customer identification number. This scenario is available for range-range, list-list, and hash-hash partitioning. Interval-range and interval-interval full partition-wise joins are also supported and can be compared to range-range.
In serial, this join is performed between pairs of matching hash partitions, one at a time. When one partition pair has been joined, the join of another partition pair begins. The join completes when all partition pairs have been processed. To ensure a good workload distribution, you should either have many more partitions than the requested degree of parallelism or use equisize partitions with as many partitions as the requested degree of parallelism. Using hash partitioning on a unique or almost-unique column, with the number of partitions equal to a power of 2, is a good way to create equisized partitions.
Note:
-
A pair of matching hash partitions is defined as one partition with the same partition number from each table. For example, with full partition-wise joins based on hash partitioning, the database joins partition 0 of
sales
with partition 0 ofcustomers
, partition 1 ofsales
with partition 1 ofcustomers
, and so on. -
Reference partitioning is an easy way to co-partition two tables so that the optimizer can always consider a full partition-wise join if the tables are joined in a statement.
Parallel execution of a full partition-wise join is a straightforward parallelization of the serial execution. Instead of joining one partition pair at a time, partition pairs are joined in parallel by the query servers. Figure 3-1 illustrates the parallel execution of a full partition-wise join.
Figure 3-1 Parallel Execution of a Full Partition-wise Join
Description of "Figure 3-1 Parallel Execution of a Full Partition-wise Join"
The following example shows the execution plan for sales
and customers
co-partitioned by hash with the same number of partitions. The plan shows a full partition-wise join.
explain plan for SELECT c.cust_last_name, COUNT(*) FROM sales s, customers c WHERE s.cust_id = c.cust_id AND s.time_id BETWEEN TO_DATE('01-JUL-1999', 'DD-MON-YYYY') AND (TO_DATE('01-OCT-1999', 'DD-MON-YYYY')) GROUP BY c.cust_last_name HAVING COUNT(*) > 100; ------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Pstart| Pstop| TQ |IN-OUT| PQ Distrib| ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 46 | 1196 | | | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 46 | 1196 | | | Q1,01 | P->S | QC (RAND) | |* 3 | FILTER | | | | | | Q1,01 | PCWC | | | 4 | HASH GROUP BY | | 46 | 1196 | | | Q1,01 | PCWP | | | 5 | PX RECEIVE | | 46 | 1196 | | | Q1,01 | PCWP | | | 6 | PX SEND HASH | :TQ10000 | 46 | 1196 | | | Q1,00 | P->P | HASH | | 7 | HASH GROUP BY | | 46 | 1196 | | | Q1,00 | PCWP | | | 8 | PX PARTITION HASH ALL| | 59158 | 1502K| 1 | 16 | Q1,00 | PCWC | | |* 9 | HASH JOIN | | 59158 | 1502K| | | Q1,00 | PCWP | | | 10 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 704K| 1 | 16 | Q1,00 | PCWP | | |* 11 | TABLE ACCESS FULL | SALES | 59158 | 751K| 1 | 16 | Q1,00 | PCWP | | ------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(COUNT(SYS_OP_CSR(SYS_OP_MSR(COUNT(*)),0))>100) 9 - access("S"."CUST_ID"="C"."CUST_ID") 11 - filter("S"."TIME_ID"<=TO_DATE(' 1999-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "S"."TIME_ID">=TO_DATE(' 1999-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Note:
The Cost (%CPU)
and Time
columns were removed from the plan table output in this example.
In Oracle RAC environments running on massive parallel processing (MPP) platforms, placing partitions on nodes is critical to achieving good scalability. To avoid remote I/O, both matching partitions should have affinity to the same node. Partition pairs should be spread over all of the nodes to avoid bottlenecks and to use all CPU resources available on the system.
Nodes can host multiple pairs when there are more pairs than nodes. For example, with an 8-node system and 16 partition pairs, each node receives two pairs.
See Also:
Oracle Real Application Clusters Administration and Deployment Guide for more information about data affinity
3.2.1.3 Full Partition-Wise Joins: Composite - Single-Level
A composite to single-level full partition-wise join is a variation of the single-level - single-level method.
In this scenario, one table (typically the larger table) is composite partitioned on two dimensions, using the join columns as the subpartition key. In the example, the sales
table is a typical example of a table storing historical data. Using range partitioning is a logical initial partitioning method for a table storing historical information.
For example, assume you want to partition the sales
table into eight partitions by range on the column time_id
. Also assume you have two years and that each partition represents a quarter. Instead of using range partitioning, you can use composite partitioning to enable a full partition-wise join while preserving the partitioning on time_id
. For example, partition the sales
table by range on time_id
and then subpartition each partition by hash on cust_id
using 16 subpartitions for each partition, for a total of 128 subpartitions. The customers
table can use hash partitioning with 16 partitions.
When you use the method just described, a full partition-wise join works similarly to the one created by a single-level - single-level hash-hash method. The join is still divided into 16 smaller joins between hash partition pairs from both tables. The difference is that now each hash partition in the sales
table is composed of a set of 8 subpartitions, one from each range partition.
Figure 3-2 illustrates how the hash partitions are formed in the sales
table. Each cell represents a subpartition. Each row corresponds to one range partition, for a total of 8 range partitions. Each range partition has 16 subpartitions. Each column corresponds to one hash partition for a total of 16 hash partitions; each hash partition has 8 subpartitions. Hash partitions can be defined only if all partitions have the same number of subpartitions, in this case, 16.
Hash partitions are implicit in a composite table. However, Oracle does not record them in the data dictionary, and you cannot manipulate them with DDL commands as you can range or list partitions.
Figure 3-2 Range and Hash Partitions of a Composite Table
Description of "Figure 3-2 Range and Hash Partitions of a Composite Table"
The following example shows the execution plan for the full partition-wise join with the sales
table range partitioned by time_id
, and subpartitioned by hash on cust_id
.
---------------------------------------------------------------------------------------------- | Id | Operation | Name | Pstart| Pstop |IN-OUT| PQ Distrib | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | | 1 | PX COORDINATOR | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | | | P->S | QC (RAND) | |* 3 | FILTER | | | | PCWC | | | 4 | HASH GROUP BY | | | | PCWP | | | 5 | PX RECEIVE | | | | PCWP | | | 6 | PX SEND HASH | :TQ10000 | | | P->P | HASH | | 7 | HASH GROUP BY | | | | PCWP | | | 8 | PX PARTITION HASH ALL | | 1 | 16 | PCWC | | |* 9 | HASH JOIN | | | | PCWP | | | 10 | TABLE ACCESS FULL | CUSTOMERS | 1 | 16 | PCWP | | | 11 | PX PARTITION RANGE ITERATOR| | 8 | 9 | PCWC | | |* 12 | TABLE ACCESS FULL | SALES | 113 | 144 | PCWP | | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(COUNT(SYS_OP_CSR(SYS_OP_MSR(COUNT(*)),0))>100) 9 - access("S"."CUST_ID"="C"."CUST_ID") 12 - filter("S"."TIME_ID"<=TO_DATE(' 1999-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "S"."TIME_ID">=TO_DATE(' 1999-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Note:
The Rows
, Cost (%CPU)
, Time
, and TQ
columns were removed from the plan table output in this example.
Composite - single-level partitioning is effective because it enables you to combine pruning on one dimension with a full partition-wise join on another dimension. In the previous example query, pruning is achieved by scanning only the subpartitions corresponding to Q3 of 1999, in other words, row number 3 in Figure 3-2. Oracle then joins these subpartitions with the customer table, using a full partition-wise join.
All characteristics of the single-level - single-level partition-wise join apply to the composite - single-level partition-wise join. In particular, for this example, these two points are common to both methods:
-
The degree of parallelism for this full partition-wise join cannot exceed 16. Even though the
sales
table has 128 subpartitions, it has only 16 hash partitions. -
A partition is now a collection of subpartitions. For example, in Figure 3-2, store hash partition 9 of the
sales
table shown by the eight circled subpartitions, on the same node as hash partition 9 of thecustomers
table.
3.2.1.4 Full Partition-Wise Joins: Composite - Composite
You can use composite to composite full partition-wise joins for additional flexibility.
If needed, you can also partition the customers
table by the composite method. For example, you partition it by range on a postal code column to enable pruning based on postal codes. You then subpartition it by hash on cust_id
using the same number of partitions (16) to enable a partition-wise join on the hash dimension.
You can get full partition-wise joins on all combinations of partition and subpartition partitions: partition - partition, partition - subpartition, subpartition - partition, and subpartition - subpartition.
3.2.2 Partial Partition-Wise Joins
With partial partition-wise joins, only one table must be partitioned on the join key.
Oracle Database can perform partial partition-wise joins only in parallel. Unlike full partition-wise joins, partial partition-wise joins require you to partition only one table on the join key, not both tables. The partitioned table is referred to as the reference table. The other table may or may not be partitioned. Partial partition-wise joins are more common than full partition-wise joins.
To execute a partial partition-wise join, the database dynamically repartitions the other table based on the partitioning of the reference table. After the other table is repartitioned, the execution is similar to a full partition-wise join.
The performance advantage that partial partition-wise joins have over joins in nonpartitioned tables is that the reference table is not moved during the join operation. Parallel joins between nonpartitioned tables require both input tables to be redistributed on the join key. This redistribution operation involves exchanging rows between parallel execution servers. This is a CPU-intensive operation that can lead to excessive interconnect traffic in Oracle RAC environments. Partitioning large tables on a join key, either a foreign or primary key, prevents this redistribution every time the table is joined on that key. Of course, if you choose a foreign key to partition the table, which is the most common scenario, then select a foreign key that is involved in many queries.
To illustrate partial partition-wise joins, consider the previous sales/customers
example. Assume that customers
is not partitioned or is partitioned on a column other than cust_id
. Because sales
is often joined with customers
on cust_id
, and because this join dominates our application workload, partition sales
on cust_id
to enable partial partition-wise joins every time customers
and sales
are joined. As with full partition-wise joins, you have several alternatives:
3.2.2.1 Partial Partition-Wise Joins: Single-Level Partitioning
A single-level partial partition-wise join is the simplest method to enable a partial partition-wise join.
For example, you can enable a single-level partial partition-wise join to partition sales
by hash on cust_id
. The number of partitions determines the maximum degree of parallelism, because the partition is the smallest granule of parallelism for partial partition-wise join operations.
The parallel execution of a partial partition-wise join is illustrated in Figure 3-3, which assumes that both the degree of parallelism and the number of partitions of sales
are 16. The execution involves two sets of query servers: one set, labeled set 1 in Figure 3-3, scans the customers
table in parallel. The granule of parallelism for the scan operation is a range of blocks.
Rows from customers
that are selected by the first set, in this case all rows, are redistributed to the second set of query servers by hashing cust_id
. For example, all rows in customers
that could have matching rows in partition P1
of sales
are sent to query server 1 in the second set. Rows received by the second set of query servers are joined with the rows from the corresponding partitions in sales
. Query server number 1 in the second set joins all customers
rows that it receives with partition P1
of sales
.
The example below shows the execution plan for the partial partition-wise join between sales
and customers
.
----------------------------------------------------------------------------------------------- | Id | Operation | Name | Pstart| Pstop |IN-OUT| PQ Distrib | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | | 1 | PX COORDINATOR | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10002 | | | P->S | QC (RAND) | |* 3 | FILTER | | | | PCWC | | | 4 | HASH GROUP BY | | | | PCWP | | | 5 | PX RECEIVE | | | | PCWP | | | 6 | PX SEND HASH | :TQ10001 | | | P->P | HASH | | 7 | HASH GROUP BY | | | | PCWP | | |* 8 | HASH JOIN | | | | PCWP | | | 9 | PART JOIN FILTER CREATE | :BF0000 | | | PCWP | | | 10 | PX RECEIVE | | | | PCWP | | | 11 | PX SEND PARTITION (KEY) | :TQ10000 | | | P->P | PART (KEY) | | 12 | PX BLOCK ITERATOR | | | | PCWC | | | 13 | TABLE ACCESS FULL | CUSTOMERS | | | PCWP | | | 14 | PX PARTITION HASH JOIN-FILTER| |:BF0000|:BF0000| PCWC | | |* 15 | TABLE ACCESS FULL | SALES |:BF0000|:BF0000| PCWP | | ----------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(COUNT(SYS_OP_CSR(SYS_OP_MSR(COUNT(*)),0))>100) 8 - access("S"."CUST_ID"="C"."CUST_ID") 15 - filter("S"."TIME_ID"<=TO_DATE(' 1999-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "S"."TIME_ID">=TO_DATE(' 1999-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
This query runs in parallel, as displayed in the plan, because there are
PX
row sources. One table is partitioned, which is the
SALES
table. You can determine this because the PX PARTITION
HASH
row source contains a nonpartitioned table CUSTOMERS
that
is distributed through PX SEND PARTITION
to a different worker set that
performs the join.
Note:
The Rows
, Cost (%CPU)
, Time
, and TQ
columns were removed from the plan table output in this example.
Note:
This discussion is based on hash partitioning, but it also applies for range, list, and interval partial partition-wise joins.
Considerations for full partition-wise joins also apply to partial partition-wise joins:
-
The degree of parallelism does not need to equal the number of partitions. In Figure 3-3, the query executes with two sets of 16 query servers. In this case, Oracle assigns 1 partition to each query server of the second set. Again, the number of partitions should always be a multiple of the degree of parallelism.
-
In Oracle RAC environments on MPPs, each hash partition of
sales
should preferably have affinity to only one node to avoid remote I/Os. Also, spread partitions over all nodes to avoid bottlenecks and use all CPU resources available on the system. A node can host multiple partitions when there are more partitions than nodes.See Also:
Oracle Real Application Clusters Administration and Deployment Guide for more information about data affinity
3.2.2.2 Partial Partition-Wise Joins: Composite
You can use composite partial partition-wise joins.
As with full partition-wise joins, the prime partitioning method for the sales
table is to use the range method on column time_id
. This is because sales
is a typical example of a table that stores historical data. To enable a partial partition-wise join while preserving this range partitioning, subpartition sales
by hash on column cust_id
using 16 subpartitions for each partition. Both pruning and partial partition-wise joins can be used if a query joins customers
and sales
and if the query has a selection predicate on time_id
.
When the sales
table is composite partitioned, the granule of parallelism for a partial partition-wise join is a hash partition and not a subpartition. Refer to Figure 3-2 for an illustration of a hash partition in a composite table. Again, the number of hash partitions should be a multiple of the degree of parallelism. Also, on an MPP system, ensure that each hash partition has affinity to a single node. In the previous example, the eight subpartitions composing a hash partition should have affinity to the same node.
Note:
This discussion is based on range-hash, but it also applies for all other combinations of composite partial partition-wise joins.
The following example shows the execution plan for the query between sales
and customers
with sales range partitioned by time_id
and subpartitioned by hash on cust_id
.
--------------------------------------------------------------------------------------------- | Id | Operation | Name | Pstart| Pstop |IN-OUT| PQ Distrib | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | | 1 | PX COORDINATOR | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10002 | | | P->S | QC (RAND) | |* 3 | FILTER | | | | PCWC | | | 4 | HASH GROUP BY | | | | PCWP | | | 5 | PX RECEIVE | | | | PCWP | | | 6 | PX SEND HASH | :TQ10001 | | | P->P | HASH | | 7 | HASH GROUP BY | | | | PCWP | | |* 8 | HASH JOIN | | | | PCWP | | | 9 | PART JOIN FILTER CREATE | :BF0000 | | | PCWP | | | 10 | PX RECEIVE | | | | PCWP | | | 11 | PX SEND PARTITION (KEY) | :TQ10000 | | | P->P | PART (KEY) | | 12 | PX BLOCK ITERATOR | | | | PCWC | | | 13 | TABLE ACCESS FULL | CUSTOMERS | | | PCWP | | | 14 | PX PARTITION RANGE ITERATOR| | 8 | 9 | PCWC | | | 15 | PX PARTITION HASH ALL | | 1 | 16 | PCWC | | |* 16 | TABLE ACCESS FULL | SALES | 113 | 144 | PCWP | | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(COUNT(SYS_OP_CSR(SYS_OP_MSR(COUNT(*)),0))>100) 8 - access("S"."CUST_ID"="C"."CUST_ID") 16 - filter("S"."TIME_ID"<=TO_DATE(' 1999-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "S"."TIME_ID">=TO_DATE(' 1999-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Note:
The Rows
, Cost (%CPU)
, Time
, and TQ
columns were removed from the plan table output in this example.
3.3 Index Partitioning
Partitioning indexes has recommendations and considerations in common with partitioning tables.
The rules for partitioning indexes are similar to those for tables:
-
An index can be partitioned unless:
-
The index is a cluster index.
-
The index is defined on a clustered table.
-
-
You can mix partitioned and nonpartitioned indexes with partitioned and nonpartitioned tables:
-
A partitioned table can have partitioned or nonpartitioned indexes.
-
A nonpartitioned table can have partitioned or nonpartitioned indexes.
-
-
Bitmap indexes on nonpartitioned tables cannot be partitioned.
-
A bitmap index on a partitioned table must be a local index.
However, partitioned indexes are more complicated than partitioned tables because there are three types of partitioned indexes:
-
Local prefixed
-
Local nonprefixed
-
Global prefixed
Oracle Database supports all three types. However, there are some restrictions. For example, a key cannot be an expression when creating a local unique index on a partitioned table.
The following topics are discussed:
See Also:
Oracle Database
Reference for information about the DBA_INDEXES
, DBA_IND_PARTITIONS
, DBA_IND_SUBPARTITIONS
, and DBA_PART_INDEXES
views.
3.3.1 Local Partitioned Indexes
In a local index, all keys in a particular index partition refer only to rows stored in a single underlying table partition.
A local index is created by specifying the LOCAL
attribute. Oracle constructs the local index so that it is equipartitioned with the underlying table. Oracle partitions the index on the same columns as the underlying table, creates the same number of partitions or subpartitions, and gives them the same partition bounds as corresponding partitions of the underlying table.
Oracle also maintains the index partitioning automatically when partitions in the underlying table are added, dropped, merged, or split, or when hash partitions or subpartitions are added or coalesced. This ensures that the index remains equipartitioned with the table.
A local index can be created UNIQUE
if the partitioning columns form a subset of the index columns. This restriction guarantees that rows with identical index keys always map into the same partition, where uniqueness violations can be detected.
Local indexes have the following advantages:
-
Only one index partition must be rebuilt when a maintenance operation other than
SPLIT
PARTITION
orADD
PARTITION
is performed on an underlying table partition. -
The duration of a partition maintenance operation remains proportional to partition size if the partitioned table has only local indexes.
-
Local indexes support partition independence.
-
Local indexes support smooth roll-out of old data and roll-in of new data in historical tables.
-
Oracle can take advantage of the fact that a local index is equipartitioned with the underlying table to generate better query access plans.
-
Local indexes simplify the task of tablespace incomplete recovery. To recover a partition or subpartition of a table to a point in time, you must also recover the corresponding index entries to the same point in time. The only way to accomplish this is with a local index. Then you can recover the corresponding table and index partitions or subpartitions.
The following topics are discussed:
See Also:
Oracle Database PL/SQL
Packages and Types Reference for a description of the DBMS_PCLXUTIL
package
3.3.1.1 Local Prefixed Indexes
A local index is prefixed if it is partitioned on a left prefix of the index columns and the subpartioning key is included in the index key. Local prefixed indexes can be unique or nonunique.
For example, if the sales
table and its local index sales_ix
are partitioned on the week_num
column, then index sales_ix
is local prefixed if it is defined on the columns (week_num
, xaction_num
). On the other hand, if index sales_ix
is defined on column product_num
then it is not prefixed.
**INTERNAL XREF ERROR** illustrates another example of a local prefixed index.
3.3.1.2 Local Nonprefixed Indexes
A local index is nonprefixed if it is not partitioned on a left prefix of the index columns or if the index key does not include the subpartitioning key.
You cannot have a unique local nonprefixed index unless the partitioning key is a subset of the index key.
Figure 3-5 illustrates an example of a local nonprefixed index.
3.3.1.2.1 Inverted File Flat Vector Indexes Partitioning Schemes
Inverted File Flat vector indexes support both global and local indexes on partitioned tables. By default, IVF indexes are globally partitioned by centroid.
A global IVF index is composed of two tables:
-
One called
VECTOR$<base table name>_IVF_IDX$<object info>$IVF_FLAT_CENTROIDS
, containing the list of identified centroid vectors and associated ids. -
The second called
VECTOR$<base table name>_IVF_IDX$<object info>$IVF_FLAT_CENTROID_PARTITIONS
, which is list-partitioned on the centroid ids. Each partition contains the base table vectors closely related (cluster) to the corresponding centroid id for that partition.
This is illustrated by the following diagram:
This structure is used to accelerate searches in the index by identifying first the centroid that is the closest to your query vector, and then use the corresponding centroid id to prune unnecessary partitions.
However, if the base table is partitioned on some relational data and your query is filtering on the base table partition key, then global IVF indexes are not optimal because they are completely independent of the base table partition key. For example, if you search the top-10 houses in California similar to a vectorized picture, the picture itself has most probably no relationship with the state of California. While your query benefits from the fact that your base table is partitioned by state, so you can search only the partition corresponding to California, the query still must look at pictures that may not be in California.
To further accelerate such type of queries, you have the possibility to create a local IVF index. The term local for an index refers to a one-to-one relationship between the base table partitions or subpartitions and the index partitions.
This is illustrated by the following graphic, where the base table has three partitions. The created local IVF index is still constituted by two internal tables:
-
One called
VECTOR$<base table name>_IVF_IDX$<object info>$IVF_FLAT_CENTROIDS
, which is list-partitioned by base table partition ids, and is thus equi-partioned with the base table. Each partition containing the list of corresponding identified centroid vectors and associated ids. -
The second called
VECTOR$<base table name>_IVF_IDX$<object info>$IVF_FLAT_CENTROID_PARTITIONS
, which is list-partitioned by base table partition id and list-subpartitioned by centroid id. This table is also equi-partitioned with the base table, and each subpartition contains the base table vectors closely related (cluster) to the corresponding centroid id for that subpartition.
Coming back to our initial example where you search the top-10 houses in California similar to a vectorized picture; your query benefits from partition pruning on the base table and Centroids table (California) as they are both partitioned by state. In addition, and once the closest centroid is identified in that partition, the query simply needs to scan the corresponding centroid cluster subpartition in the Centroid Partitions table without having to scan other centroid subpartitions.
Another possibility is for the base table to be composite partitioned. Here is a graphical representation corresponding to that case. The Centroids table is list-partitioned according to base table subpartitions. Each partition in the Centroids table containing all centroid vectors found in the corresponding base table subpartition. The Centroid Partitions table is list-partitioned by base table subpartition id, and is further subpartitioned by centroid id:
Note:
You can create local IVF indexes only on a partitioned base table.
Local IVF indexes inherit all system catalog tables and views used by regular local indexes. A flag (idx_spare2
) in the vecsys.vector$index
table indicates if an index is a local or global vector index.
Using local IVF indexes brings additional advantages:
-
Simplified partition management operations (PMOP):
For example, dropping a table partition just involves dropping the corresponding index partition.
-
Flexible Indexing schemes:
For example, marking certain index partitions
UNUSABLE
to avoid indexing certain table partitions through partial indexing.
Note:
If you want your user queries to use full potential of local IVF indexes, by taking the benefit of partition pruning, then user queries must satisfy the following conditions:
-
The base table is [sub]partitioned by a single column.
-
Conditions are a form of
[sub]partitition_key CMP
constant, whereCMP
can be:=, >, >=, <, <=, IN
-
The
partition_key
condition isAND
ed with other non-partition conditions.
Partition Management Operations (PMOP) and IVF Indexes
These are the PMOP possibilities and restrictions for local IVF indexes:
-
ALTER TABLE TRUNCATE [sub]partition <partition_name>
ALTER TABLE DROP [sub]partition <partition_name>
These operations are supported with all partition schemes:
RANGE
,HASH
,LIST
. However, all corresponding IVF index partitions are marked asUNUSABLE
after the operation. -
ALTER TABLE ADD [sub]partition <partition_name>
If the base table is partitioned by
RANGE
orLIST
, then the operation is supported. However, all corresponding IVF indexes are marked asUNUSABLE
after the operation. If the table is partitioned byHASH
, then the operation fails if there are any local IVF indexes on the base table. -
All other PMOP operations on the base table are not supported, such as
ALTER TABLE SPLIT/MERGE/MOVE/EXCHANGE/COALESCE
.These
ALTER TABLE
statements will fail if there are any local IVF indexes on the table being altered. -
ALTER INDEX
on local IVF indexes is not supported.
Experiment with LOCAL IVF Vector Indexes
You can start experimenting with LOCAL
IVF indexes using the
following code. This is not a scenario but merely a series of SQL commands to help
you get started on your own testing scenarios.
- Create the base table using the partition scheme of your
choice:
RANGE
partitioning:DROP TABLE sales_data PURGE; CREATE TABLE sales_data ( product_id NUMBER, customer_id NUMBER, sale_date DATE, amount_sold NUMBER, vec vector(8), region VARCHAR2(20) ) PARTITION BY RANGE (product_id) ( PARTITION sales_1 VALUES LESS THAN (100), PARTITION sales_2 VALUES LESS THAN (200), PARTITION sales_3 VALUES LESS THAN (300), PARTITION sales_4 VALUES LESS THAN (400), PARTITION sales_5 VALUES LESS THAN (500), PARTITION sales_6 VALUES LESS THAN (600), PARTITION sales_7 VALUES LESS THAN (700), PARTITION sales_8 VALUES LESS THAN (800), PARTITION sales_9 VALUES LESS THAN (900), PARTITION sales_10 VALUES LESS THAN (1000), PARTITION sales_default VALUES LESS THAN (1000000) );
LIST
partitioning:DROP TABLE sales_data PURGE; CREATE TABLE sales_data ( product_id NUMBER, customer_id NUMBER, sale_date DATE, amount_sold NUMBER, vec VECTOR(8), region VARCHAR2(20) ) PARTITION BY LIST (region) ( PARTITION RegionA_Partition VALUES ('RegionA1','RegionA2','RegionA3','RegionA4','RegionA5'), PARTITION RegionB_Partition VALUES ('RegionB1','RegionB2','RegionB3','RegionB4','RegionB5'), PARTITION RegionC_Partition VALUES ('RegionC1','RegionC2','RegionC3','RegionC4','RegionC5'), PARTITION RegionD_Partition VALUES ('RegionD1','RegionD2','RegionD3','RegionD4','RegionD5'), PARTITION RegionE_Partition VALUES ('RegionE1','RegionE2','RegionE3','RegionE4','RegionE5'), PARTITION RegionF_Partition VALUES ('RegionF1','RegionF2','RegionF3','RegionF4','RegionF5'), PARTITION RegionG_Partition VALUES ('RegionG1','RegionG2','RegionG3','RegionG4','RegionG5'), PARTITION RegionH_Partition VALUES ('RegionH1','RegionH2','RegionH3','RegionH4','RegionH5'), PARTITION RegionI_Partition VALUES ('RegionI1','RegionI2','RegionI3','RegionI4','RegionI5'), PARTITION RegionJ_Partition VALUES ('RegionJ1','RegionJ2','RegionJ3','RegionJ4','RegionJ5'), PARTITION Other_Region_Partition VALUES (DEFAULT) );
HASH
partitioning:DROP TABLE sales_data PURGE; CREATE TABLE sales_data ( product_id NUMBER, customer_id NUMBER, sale_date DATE, amount_sold NUMBER, vec VECTOR(8), region VARCHAR2(20) ) PARTITION BY HASH (product_id) PARTITIONS 10;
- Use this procedure to randomly insert data into the
SALES_DATA
table:CREATE OR REPLACE PROCEDURE insert_sales_data(numRows IN INTEGER, maxProductId IN INTEGER) AS TYPE vec_array IS VARRAY(8) OF NUMBER; BEGIN DBMS_RANDOM.INITIALIZE(100); FOR i IN 1..numRows LOOP INSERT INTO sales_data (product_id, customer_id, sale_date, amount_sold, vec, region) VALUES (round(DBMS_RANDOM.VALUE(1, MaxProductId)), -- Random product_id between 1 and 1000 round(DBMS_RANDOM.VALUE(1, 10000)), -- Random customer_id between 1 and 10000 (DATE '2024-05-10' - DBMS_RANDOM.VALUE(1, 1460)), -- Random sale_date within the last 4 years DBMS_RANDOM.VALUE(10, 10000), -- Random amount_sold between 10 and 1000 '[' || to_char(DBMS_RANDOM.VALUE(0, 1000)) || ',' || to_char(DBMS_RANDOM.VALUE(0, 1000)) || ',' || to_char(DBMS_RANDOM.VALUE(0, 1000)) || ',' || to_char(DBMS_RANDOM.VALUE(0, 1000)) || ',' || to_char(DBMS_RANDOM.VALUE(0, 1000)) || ',' || to_char(DBMS_RANDOM.VALUE(0, 1000)) || ',' || to_char(DBMS_RANDOM.VALUE(0, 1000)) || ',' || to_char(DBMS_RANDOM.VALUE(0, 1000)) || ']', CASE MOD(i, 10) WHEN 0 THEN 'RegionA' || MOD(i,5) WHEN 1 THEN 'RegionB' || MOD(i,5) WHEN 2 THEN 'RegionC' || MOD(i,5) WHEN 3 THEN 'RegionD' || MOD(i,5) WHEN 4 THEN 'RegionE' || MOD(i,5) WHEN 5 THEN 'RegionF' || MOD(i,5) WHEN 6 THEN 'RegionG' || MOD(i,5) WHEN 7 THEN 'RegionH' || MOD(i,5) WHEN 8 THEN 'RegionI' || MOD(i,5) ELSE 'RegionJ' || MOD(i,5) END); IF MOD(i, 10000) = 0 THEN COMMIT; END IF; END LOOP; COMMIT; END insert_sales_data; / EXEC insert_sales_data(20000, 501);
- Create a
LOCAL
IVF index on the VEC column of theSALES_DATA
table:CREATE VECTOR INDEX vidxivf ON sales_data(vec) ORGANIZATION NEIGHBOR PARTITIONS WITH TARGET ACCURACY 95 DISTANCE EUCLIDEAN PARAMETERS(TYPE IVF, NEIGHBOR PARTITION 20) LOCAL;
- Check all index partitions state before and after running PMOP commands:
SELECT INDEX_NAME, PARTITION_NAME, STATUS FROM USER_IND_PARTITIONS WHERE index_name LIKE upper('vidxivf') ORDER BY 1, 2;
- Test the following
ALTER TABLE
commands to see what is supported and what is not:If the table is
RANGE
partitioned:ALTER TABLE sales_data ADD PARTITION sales_1000000 VALUES LESS THAN (2000000); ALTER TABLE sales_data SPLIT PARTITION sales_2 AT (150) INTO ( PARTITION sales_21, PARTITION sales_22 ); ALTER TABLE sales_data DROP PARTITION sales_3;
If the table isLIST
partitioned:ALTER TABLE sales_data SPLIT PARTITION RegionE_Partition VALUES ('RegionE1', 'RegionE2', 'RegionE3') INTO ( PARTITION RegionE1_Partition, PARTITION RegionE2_Partition ); ALTER TABLE sales_data DROP PARTITION RegionB_Partition;
If the table is
HASH
partitioned:ALTER TABLE sales_data ADD PARTITION; SELECT partition_name FROM user_tab_partitions WHERE table_name='SALES_DATA'; ALTER TABLE sales_data COALESCE PARTITION;
3.3.2 Global Partitioned Indexes
In a global partitioned index, the keys in a particular index partition may refer to rows stored in multiple underlying table partitions or subpartitions.
A global index can be range or hash partitioned, though it can be defined on any type of partitioned table. A global index is created by specifying the GLOBAL
attribute. The database administrator is responsible for defining the initial partitioning of a global index at creation and for maintaining the partitioning over time. Index partitions can be merged or split as necessary.
Normally, a global index is not equipartitioned with the underlying table. There is nothing to prevent an index from being equipartitioned with the underlying table, but Oracle does not take advantage of the equipartitioning when generating query plans or executing partition maintenance operations. So an index that is equipartitioned with the underlying table should be created as LOCAL
.
A global partitioned index contains a single B-tree with entries for all rows in all partitions. Each index partition may contain keys that refer to many different partitions or subpartitions in the table.
The highest partition of a global index must have a partition bound that includes all values that are MAXVALUE
. This insures that all rows in the underlying table can be represented in the index.
The following topics are discussed:
3.3.2.1 Prefixed and Nonprefixed Global Partitioned Indexes
A global partitioned index is prefixed if it is partitioned on a left prefix of the index columns.
A global partitioned index is nonprefixed if it is not partitioned on a left prefix of the index columns. Oracle does not support global nonprefixed partitioned indexes. See Figure 3-6 for an example.
Global prefixed partitioned indexes can be unique or nonunique. Nonpartitioned indexes are treated as global prefixed nonpartitioned indexes.
3.3.2.2 Management of Global Partitioned Indexes
Management of global partitioned indexes presents several challenges.
Global partitioned indexes are harder to manage than local indexes because of the following:
-
When the data in an underlying table partition is moved or removed (
SPLIT
,MOVE
,DROP
, orTRUNCATE
), all partitions of a global index are affected. Consequently global indexes do not support partition independence. -
When an underlying table partition or subpartition is recovered to a point in time, all corresponding entries in a global index must be recovered to the same point in time. Because these entries may be scattered across all partitions or subpartitions of the index, mixed with entries for other partitions or subpartitions that are not being recovered, there is no way to accomplish this except by re-creating the entire global index.
Figure 3-6 Global Prefixed Partitioned Index
Description of "Figure 3-6 Global Prefixed Partitioned Index"
3.3.3 Summary of Partitioned Index Types
A summary of partitioned index types is provided in this topic.
Table 3-1 summarizes the types of partitioned indexes that Oracle supports. The key points are:
-
If an index is local, then it is equipartitioned with the underlying table. Otherwise, it is global.
-
A prefixed index is partitioned on a left prefix of the index columns. Otherwise, it is nonprefixed.
Table 3-1 Types of Partitioned Indexes
Type of Index | Index Equipartitioned with Table | Index Partitioned on Left Prefix of Index Columns | UNIQUE Attribute Allowed | Example: Table Partitioning Key | Example: Index Columns | Example: Index Partitioning Key |
---|---|---|---|---|---|---|
Local Prefixed (any partitioning method) |
Yes |
Yes |
Yes |
A |
A, B |
A |
Local Nonprefixed (any partitioning method) |
Yes |
No |
YesFoot 1 |
A |
B, A |
A |
Global Prefixed (range partitioning only) |
NoFoot 2 |
Yes |
Yes |
A |
B |
B |
Footnote 1
For a unique local nonprefixed index, the partitioning key must be a subset of the index key and cannot be a partial index.
Footnote 2
Although a global partitioned index may be equipartitioned with the underlying table, Oracle does not take advantage of the partitioning or maintain equipartitioning after partition maintenance operations such as DROP or SPLIT PARTITION.
3.3.4 The Importance of Nonprefixed Indexes
Nonprefixed indexes are important because they are particularly useful in historical databases.
In a table containing historical data, it is common for an index to be defined on one column to support the requirements of fast access by that column. However, the index can also be partitioned on another column (the same column as the underlying table) to support the time interval for rolling out old data and rolling in new data.
Consider a sales
table partitioned by week. It contains a year's worth of data, divided into 13 partitions. It is range partitioned on week_no
, four weeks to a partition. You might create a nonprefixed local index sales_ix
on sales
. The sales_ix
index is defined on acct_no
because there are queries that need fast access to the data by account number. However, it is partitioned on week_no
to match the sales
table. Every four weeks, the oldest partitions of sales
and sales_ix
are dropped and new ones are added.
3.3.5 Performance Implications of Prefixed and Nonprefixed Indexes
There are performance implications of prefixed and nonprefixed indexes.
With a prefixed index, the likelihood to get partition pruning is much higher than with a non-prefixed index. If a column is part of an index, then you can assume that the column is used as a filter predicate, which automatically means some level of pruning when a filtered column is a prefixed column. This result suggests that it is usually less expensive to probe into a prefixed index than to probe into a nonprefixed index. If an index is prefixed (either local or global) and Oracle is presented with a predicate involving the index columns, then partition pruning can restrict application of the predicate to a subset of the index partitions.
For example, in **INTERNAL XREF ERROR**, if the predicate is deptno=15
, the optimizer knows to apply the predicate only to the second partition of the index. (If the predicate involves a bind variable, the optimizer does not know exactly which partition but it may still know there is only one partition involved, in which case at run time, only one index partition is accessed.)
When an index is nonprefixed, Oracle often has to apply a predicate involving the index columns to all N
index partitions. This is required to look up a single key, or to do an index range scan. For a range scan, Oracle must also combine information from N
index partitions. For example, in Figure 3-5, a local index is partitioned on chkdate
with an index key on acctno
. If the predicate is acctno=31
, Oracle probes all 12 index partitions.
Of course, if there is also a predicate on the partitioning columns, then multiple index probes might not be necessary. Oracle takes advantage of the fact that a local index is equipartitioned with the underlying table to prune partitions based on the partition key. For example, if the predicate in Figure 3-5 is chkdate<3/97
, Oracle only has to probe two partitions.
So for a nonprefixed index, if the partition key is a part of the WHERE
clause but not of the index key, then the optimizer determines which index partitions to probe based on the underlying table partition.
When many queries and DML statements using keys of local, nonprefixed, indexes have to probe all index partitions, this effectively reduces the degree of partition independence provided by such indexes.
Table 3-2 Comparing Prefixed Local, Nonprefixed Local, and Global Indexes
Index Characteristics | Prefixed Local | Nonprefixed Local | Global |
---|---|---|---|
Unique possible? |
Yes |
Yes |
Yes. Must be global if using indexes on columns other than the partitioning columns |
Manageability |
Easy to manage |
Easy to manage |
Harder to manage |
OLTP |
Good |
Bad |
Good |
Long Running (DSS) |
Good |
Good |
Not Good |
3.3.6 Advanced Index Compression With Partitioned Indexes
Advanced index compression with partitioned indexes can reduce the storage requirements for indexes.
Creating an index using advanced index compression reduces the size of all supported unique and non-unique indexes. Advanced index compression improves the compression ratios significantly while still providing efficient access to the indexes. Advanced compression works well on all supported indexes, including those indexes that are not good candidates for prefix compression.
For a partitioned index, you can specify the compression type on a partition by partition basis. You can also specify advanced index compression on index partitions even when the parent index is not compressed.
The following example shows a mixture of compression attributes on the partition indexes.
CREATE INDEX my_test_idx ON test(a, b) COMPRESS ADVANCED HIGH LOCAL (PARTITION p1 COMPRESS ADVANCED LOW, PARTITION p2 COMPRESS, PARTITION p3, PARTITION p4 NOCOMPRESS);
The following example shows advanced index compression support on partitions where the parent index is not compressed.
CREATE INDEX my_test_idx ON test(a, b) NOCOMPRESS LOCAL (PARTITION p1 COMPRESS ADVANCED LOW, PARTITION p2 COMPRESS ADVANCED HIGH, PARTITION p3);
See Also:
Oracle Database Administrator’s Guide for information about advanced index compression
3.3.7 Guidelines for Partitioning Indexes
There are several guidelines for partitioning indexes.
When deciding how to partition indexes on a table, consider the mix of applications that must access the table. There is a trade-off between performance and availability and manageability. Here are some guidelines you should consider:
-
For OLTP applications:
-
Global indexes and local prefixed indexes provide better performance than local nonprefixed indexes because they minimize the number of index partition probes.
-
Local indexes support more availability when there are partition or subpartition maintenance operations on the table. Local nonprefixed indexes are very useful for historical databases.
-
-
For DSS applications, local nonprefixed indexes can improve performance because many index partitions can be scanned in parallel by range queries on the index key.
For example, a query using the predicate "
acctno
between 40 and 45" on the tablechecks
of Figure 3-5 causes parallel scans of all the partitions of the nonprefixed indexix3
. On the other hand, a query using the predicatedeptno BETWEEN 40 AND 45
on the tabledeptno
of **INTERNAL XREF ERROR** cannot be parallelized because it accesses a single partition of the prefixed indexix1
. -
For historical tables, indexes should be local if possible. This limits the effect of regularly scheduled drop partition operations.
-
Unique indexes on columns other than the partitioning columns must be global because unique local nonprefixed indexes whose keys do not contain the partitioning key are not supported.
-
Unusable indexes do not consume space.
See Also:
Oracle Database Administrator’s Guide for information about guidelines for managing tables
3.3.8 Physical Attributes of Index Partitions
The physical attributes of index partitions are described in this topic.
Default physical attributes are initially specified when a CREATE
INDEX
statement creates a partitioned index. Because there is no segment corresponding to the partitioned index itself, these attributes are only used in derivation of physical attributes of member partitions. Default physical attributes can later be modified using ALTER
INDEX
MODIFY
DEFAULT
ATTRIBUTES
.
Physical attributes of partitions created by CREATE
INDEX
are determined as follows:
-
Values of physical attributes specified (explicitly or by default) for the index are used whenever the value of a corresponding partition attribute is not specified. Handling of the
TABLESPACE
attribute of partitions of aLOCAL
index constitutes an important exception to this rule in that without a user-specifiedTABLESPACE
value (at both partition and index levels), the value of the physical attribute of the corresponding partition of the underlying table is used. -
Physical attributes (other than
TABLESPACE
, as explained in the preceding) of partitions of local indexes created during processingALTER
TABLE
ADD
PARTITION
are set to the default physical attributes of each index.
Physical attributes (other than TABLESPACE
) of index partitions created by ALTER
TABLE
SPLIT
PARTITION
are determined as follows:
-
Values of physical attributes of the index partition being split are used.
Physical attributes of an existing index partition can be modified by ALTER
INDEX MODIFY
PARTITION
and ALTER
INDEX
REBUILD
PARTITION
. Resulting attributes are determined as follows:
-
Values of physical attributes of the partition before the statement was issued are used whenever a new value is not specified. The
ALTER
INDEX
REBUILD PARTITION
SQL statement can change the tablespace in which a partition resides.
Physical attributes of global index partitions created by ALTER
INDEX
SPLIT PARTITION
are determined as follows:
-
Values of physical attributes of the partition being split are used whenever a new value is not specified.
-
Physical attributes of all partitions of an index (along with default values) may be modified by
ALTER
INDEX
, for example,ALTER
INDEX
indexname
NOLOGGING
changes the logging mode of all partitions ofindexname
toNOLOGGING
.
See Also:
Partition Administration for more detailed examples of adding partitions and examples of rebuilding indexes
3.4 Partitioning and Table Compression
Compression can be performed on several partitions or a complete partitioned heap-organized table.
You can do this compression by either defining a complete partitioned table as being compressed, or by defining it on a per-partition level. Partitions without a specific declaration inherit the attribute from the table definition or, if nothing is specified on table level, from the tablespace definition.
The decision whether a partition should be compressed or uncompressed adheres to the same rules as a nonpartitioned table. However, due to partitioning to separate data logically into distinct partitions, such a partitioned table is an ideal candidate for compressing parts of the data (partitions). For example, it is beneficial in all rolling window operations as a intermediate stage before aging out old data. With data segment compression, you can keep more old data online, minimizing the burden of additional storage consumption.
You can also change any existing uncompressed table partition later on, add new compressed and uncompressed partitions, or change the compression attribute as part of any partition maintenance operation that requires data movement, such as MERGE
PARTITION
, SPLIT
PARTITION
, or MOVE
PARTITION
. The partitions can contain data or can be empty.
The access and maintenance of a partially or fully compressed partitioned table are the same as for a fully uncompressed partitioned table. Everything that applies to fully uncompressed partitioned tables is also valid for partially or fully compressed partitioned tables.
The following topics are discussed:
See Also:
-
Oracle Database Data Warehousing Guide for a generic discussion of data warehousing optimizations and techniques
-
Oracle Database Administrator’s Guide for information about guidelines for managing tables
-
Oracle Database Performance Tuning Guide for estimating the compression factor
3.4.1 Table Compression and Bitmap Indexes
There are several necessary steps before using compression on partitioned tables with bitmap indexes.
To use table compression on partitioned tables with bitmap indexes, you must do the following before you introduce the compression attribute for the first time:
-
Mark bitmap indexes unusable.
-
Set the compression attribute.
-
Rebuild the indexes.
The first time you make a compressed partition part of an existing, fully uncompressed partitioned table, you must either drop all existing bitmap indexes or mark them UNUSABLE
before adding a compressed partition. This must be done irrespective of whether any partition contains any data. It is also independent of the operation that causes one or more compressed partitions to become part of the table. This does not apply to a partitioned table having B-tree indexes only.
This rebuilding of the bitmap index structures is necessary to accommodate the potentially higher number of rows stored for each data block with table compression enabled. Enabling table compression must be done only for the first time. All subsequent operations, whether they affect compressed or uncompressed partitions, or change the compression attribute, behave identically for uncompressed, partially compressed, or fully compressed partitioned tables.
To avoid the recreation of any bitmap index structure, Oracle recommends creating every partitioned table with at least one compressed partition whenever you plan to partially or fully compress the partitioned table in the future. This compressed partition can stay empty or even can be dropped after the partition table creation.
Having a partitioned table with compressed partitions can lead to slightly larger bitmap index structures for the uncompressed partitions. The bitmap index structures for the compressed partitions, however, are usually smaller than the appropriate bitmap index structure before table compression. This highly depends on the achieved compression rates.
Note:
Oracle Database raises an error if compression is introduced to an object for the first time and there are usable bitmap index segments.
3.4.2 Example of Table Compression and Partitioning
Examples of table compression with partitioned tables are described in this topic.
The following statement moves and compresses an existing partition sales_q1_1998
of table sales
:
ALTER TABLE sales MOVE PARTITION sales_q1_1998 TABLESPACE ts_arch_q1_1998 COMPRESS;
Alternatively, you could choose Hybrid Columnar Compression (HCC), as in the following:
ALTER TABLE sales MOVE PARTITION sales_q1_1998 TABLESPACE ts_arch_q1_1998 COMPRESS FOR ARCHIVE LOW;
If you use the MOVE
statement, then the local indexes for partition sales_q1_1998
become unusable. You must rebuild them afterward, as follows:
ALTER TABLE sales MODIFY PARTITION sales_q1_1998 REBUILD UNUSABLE LOCAL INDEXES;
You can also include the UPDATE INDEXES
clause in the MOVE
statement in order for the entire operation to be completed automatically without any negative effect on users accessing the table.
The following statement merges two existing partitions into a new, compressed partition, residing in a separate tablespace. The local bitmap indexes have to be rebuilt afterward, as in the following:
ALTER TABLE sales MERGE PARTITIONS sales_q1_1998, sales_q2_1998 INTO PARTITION sales_1_1998 TABLESPACE ts_arch_1_1998 COMPRESS FOR OLTP UPDATE INDEXES;
See Also:
-
Partition Administration for more details and examples about partition management operations
-
Oracle Database Performance Tuning Guide for details regarding how to estimate the compression ratio when using table compression
-
Oracle Database SQL Language Reference for the SQL syntax
-
Oracle Database Concepts for more information about Hybrid Columnar Compression. Hybrid Columnar Compression is a feature of certain Oracle storage systems.
3.5 Recommendations for Choosing a Partitioning Strategy
Review these recommendations based on performance considerations when choosing a partitioning strategy.
The following topics provide recommendations for choosing a partitioning strategy:
3.5.1 When to Use Range or Interval Partitioning
Range and interval partitioning are useful when organizing similar data, especially date and time data.
Range partitioning is a convenient method for partitioning historical data. The boundaries of range partitions define the ordering of the partitions in the tables or indexes.
Interval partitioning is an extension to range partitioning in which, beyond a point in time, partitions are defined by an interval. Interval partitions are automatically created by the database when data is inserted into the partition.
Range or interval partitioning is often used to organize data by time intervals on a column of type DATE
. Thus, most SQL statements accessing range partitions focus on time frames. An example of this is a SQL statement similar to "select data from a particular period in time". In such a scenario, if each partition represents data for one month, the query "find data of month 06-DEC" must access only the December partition of year 2006. This reduces the amount of data scanned to a fraction of the total data available, an optimization method called partition pruning.
Range partitioning is also ideal when you periodically load new data and purge old data, because it is easy to add or drop partitions. For example, it is common to keep a rolling window of data, keeping the past 36 months' worth of data online. Range partitioning simplifies this process. To add data from a new month, you load it into a separate table, clean it, index it, and then add it to the range-partitioned table using the EXCHANGE
PARTITION
statement, all while the original table remains online. After you add the new partition, you can drop the trailing month with the DROP
PARTITION
statement. The alternative to using the DROP
PARTITION
statement can be to archive the partition and make it read only, but this works only when your partitions are in separate tablespaces. You can also implement a rolling window of data using inserts into the partitioned table.
Interval partitioning provides an easy way for interval partitions to be automatically created as data arrives. Interval partitions can also be used for all other partition maintenance operations.
In conclusion, consider using range or interval partitioning when:
-
Very large tables are frequently scanned by a range predicate on a good partitioning column, such as
ORDER_DATE
orPURCHASE_DATE
. Partitioning the table on that column enables partition pruning. -
You want to maintain a rolling window of data.
-
You cannot complete administrative operations, such as backup and restore, on large tables in an allotted time frame, but you can divide them into smaller logical pieces based on the partition range column.
Example 3-5 creates the table salestable
for a period of two years, 2005 and 2006, and partitions it by range according to the column s_salesdate
to separate the data into eight quarters, each corresponding to a partition. Future partitions are created automatically through the monthly interval definition. Interval partitions are created in the provided list of tablespaces in a round-robin manner. Analysis of sales figures by a short interval can take advantage of partition pruning. The sales table also supports a rolling window approach.
Example 3-5 Creating a table with range and interval partitioning
CREATE TABLE salestable (s_productid NUMBER, s_saledate DATE, s_custid NUMBER, s_totalprice NUMBER) PARTITION BY RANGE(s_saledate) INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) STORE IN (tbs1,tbs2,tbs3,tbs4) (PARTITION sal05q1 VALUES LESS THAN (TO_DATE('01-APR-2005', 'DD-MON-YYYY')) TABLESPACE tbs1, PARTITION sal05q2 VALUES LESS THAN (TO_DATE('01-JUL-2005', 'DD-MON-YYYY')) TABLESPACE tbs2, PARTITION sal05q3 VALUES LESS THAN (TO_DATE('01-OCT-2005', 'DD-MON-YYYY')) TABLESPACE tbs3, PARTITION sal05q4 VALUES LESS THAN (TO_DATE('01-JAN-2006', 'DD-MON-YYYY')) TABLESPACE tbs4, PARTITION sal06q1 VALUES LESS THAN (TO_DATE('01-APR-2006', 'DD-MON-YYYY')) TABLESPACE tbs1, PARTITION sal06q2 VALUES LESS THAN (TO_DATE('01-JUL-2006', 'DD-MON-YYYY')) TABLESPACE tbs2, PARTITION sal06q3 VALUES LESS THAN (TO_DATE('01-OCT-2006', 'DD-MON-YYYY')) TABLESPACE tbs3, PARTITION sal06q4 VALUES LESS THAN (TO_DATE('01-JAN-2007', 'DD-MON-YYYY')) TABLESPACE tbs4);
See Also:
Partition Administration for more information about the partition maintenance operations on interval partitions
3.5.2 When to Use Hash Partitioning
Hash partitioning is useful for randomly distributing data across partitions based on a hashing algorithm, rather than grouping similar data.
There are times when it is not obvious in which partition data should reside, although the partitioning key can be identified. Rather than group similar data, there are times when it is desirable to distribute data such that it does not correspond to a business or a logical view of the data, as it does in range partitioning. With hash partitioning, a row is placed into a partition based on the result of passing the partitioning key into a hashing algorithm.
Using this approach, data is randomly distributed across the partitions rather than grouped. This is a good approach for some data, but may not be an effective way to manage historical data. However, hash partitions share some performance characteristics with range partitions. For example, partition pruning is limited to equality predicates. You can also use partition-wise joins, parallel index access, and parallel DML.
As a general rule, use hash partitioning for the following purposes:
-
To enable partial or full parallel partition-wise joins with likely equisized partitions.
-
To distribute data evenly among the nodes of an MPP platform that uses Oracle Real Application Clusters. Consequently, you can minimize interconnect traffic when processing internode parallel statements.
-
To use partition pruning and partition-wise joins according to a partitioning key that is mostly constrained by a distinct value or value list.
-
To randomly distribute data to avoid I/O bottlenecks if you do not use a storage management technique that stripes and mirrors across all available devices.
Note:
With hash partitioning, only equality or IN
-list predicates are supported for partition pruning.
For optimal data distribution, the following requirements should be satisfied:
-
Choose a column or combination of columns that is unique or almost unique.
-
Create multiple partitions and subpartitions for each partition that is a power of two. For example, 2, 4, 8, 16, 32, 64, 128, and so on.
Example 3-6 creates four hash partitions for the table sales_hash
using the column s_productid
as the partitioning key. Parallel joins with the products table can take advantage of partial or full partition-wise joins. Queries accessing sales figures for only a single product or a list of products benefit from partition pruning.
If you do not explicitly specify partition names, but instead you specify the number of hash partitions, then Oracle automatically generates internal names for the partitions. Also, you can use the STORE
IN
clause to assign hash partitions to tablespaces in a round-robin manner.
Example 3-6 Creating a table with hash partitioning
CREATE TABLE sales_hash (s_productid NUMBER, s_saledate DATE, s_custid NUMBER, s_totalprice NUMBER) PARTITION BY HASH(s_productid) ( PARTITION p1 TABLESPACE tbs1 , PARTITION p2 TABLESPACE tbs2 , PARTITION p3 TABLESPACE tbs3 , PARTITION p4 TABLESPACE tbs4 );
See Also:
-
Partition-Wise Operations for information about part-wise joins
-
Storage Management for VLDBs for more information about managing storage for VLDBs
-
Partition Administration for more examples on creating hash-partitioned tables
-
Oracle Database SQL Language Reference for partitioning syntax
3.5.3 When to Use List Partitioning
List partitioning is useful to explicitly map rows to partitions based on discrete values.
In Example 3-7, all the customers for states Oregon and Washington are stored in one partition and customers in other states are stored in different partitions. Account managers who analyze their accounts by region can take advantage of partition pruning.
Example 3-7 Creating a table with list partitioning
CREATE TABLE accounts ( id NUMBER , account_number NUMBER , customer_id NUMBER , branch_id NUMBER , region VARCHAR(2) , status VARCHAR2(1) ) PARTITION BY LIST (region) ( PARTITION p_northwest VALUES ('OR', 'WA') , PARTITION p_southwest VALUES ('AZ', 'UT', 'NM') , PARTITION p_northeast VALUES ('NY', 'VM', 'NJ') , PARTITION p_southeast VALUES ('FL', 'GA') , PARTITION p_northcentral VALUES ('SD', 'WI') , PARTITION p_southcentral VALUES ('OK', 'TX') );
3.5.4 When to Use Composite Partitioning
Composite partitioning offers the benefits of partitioning on multiple dimensions.
From a performance perspective you can take advantage of partition pruning on one or two dimensions depending on the SQL statement, and you can take advantage of the use of full or partial partition-wise joins on either dimension.
You can take advantage of parallel backup and recovery of a single table. Composite partitioning also increases the number of partitions significantly, which may be beneficial for efficient parallel execution. From a manageability perspective, you can implement a rolling window to support historical data and still partition on another dimension if many statements can benefit from partition pruning or partition-wise joins.
You can split backups of your tables and you can decide to store data differently based on identification by a partitioning key. For example, you may decide to store data for a specific product type in a read-only, compressed format, and keep other product type data uncompressed.
The database stores every subpartition in a composite partitioned table as a separate segment. Thus, the subpartitions may have properties that differ from the properties of the table or from the partition to which the subpartitions belong.
The following topics are discussed:
See Also:
Oracle Database SQL Language Reference for details regarding syntax and restrictions
3.5.4.1 When to Use Composite Range-Hash Partitioning
Composite range-hash partitioning is particularly common for tables that store history, are very large consequently, and are frequently joined with other large tables.
For these types of tables (typical of data warehouse systems), composite range-hash partitioning provides the benefit of partition pruning at the range level with the opportunity to perform parallel full or partial partition-wise joins at the hash level. Specific cases can benefit from partition pruning on both dimensions for specific SQL statements.
Composite range-hash partitioning can also be used for tables that traditionally use hash partitioning, but also use a rolling window approach. Over time, data can be moved from one storage tier to another storage tier, compressed, stored in a read-only tablespace, and eventually purged. Information Lifecycle Management (ILM) scenarios often use range partitions to implement a tiered storage approach.
Example 3-8 is an example of a range hash partitioned page_history
table of an Internet service provider. The table definition is optimized for historical analysis for either specific client_ip
values (in which case queries benefit from partition pruning) or for analysis across many IP addresses, in which case queries can take advantage of full or partial partition-wise joins.
This example shows the use of interval partitioning. You can use interval partitioning in addition to range partitioning so that interval partitions are created automatically as data is inserted into the table.
Example 3-8 Creating a table with composite range-hash partitioning
CREATE TABLE page_history ( id NUMBER NOT NULL , url VARCHAR2(300) NOT NULL , view_date DATE NOT NULL , client_ip VARCHAR2(23) NOT NULL , from_url VARCHAR2(300) , to_url VARCHAR2(300) , timing_in_seconds NUMBER ) PARTITION BY RANGE(view_date) INTERVAL (NUMTODSINTERVAL(1,'DAY')) SUBPARTITION BY HASH(client_ip) SUBPARTITIONS 32 (PARTITION p0 VALUES LESS THAN (TO_DATE('01-JAN-2006','dd-MON-yyyy'))) PARALLEL 32 COMPRESS;
See Also:
Managing and Maintaining Time-Based Information for more detail on Information Lifecycle Management (ILM) and implementing tiered storage using partitioning
3.5.4.2 When to Use Composite Range-List Partitioning
Composite range-list partitioning is commonly used for large tables that store historical data and are commonly accessed on multiple dimensions.
Often the historical view of the data is one access path, but certain business cases add another categorization to the access path. For example, regional account managers are very interested in how many new customers they signed up in their region in a specific time period. ILM and its tiered storage approach is a common reason to create range-list partitioned tables so that older data can be moved and compressed, but partition pruning on the list dimension is still available.
Example 3-9 creates a range-list partitioned call_detail_records
table. A telecommunication company can use this table to analyze specific types of calls over time. The table uses local indexes on from_number
and to_number
.
This example shows the use of interval partitioning. You can use interval partitioning in addition to range partitioning so that interval partitions are created automatically as data is inserted into the table.
Example 3-9 Creating a table with composite range-list partitioning
CREATE TABLE call_detail_records ( id NUMBER , from_number VARCHAR2(20) , to_number VARCHAR2(20) , date_of_call DATE , distance VARCHAR2(1) , call_duration_in_s NUMBER(4) ) PARTITION BY RANGE(date_of_call) INTERVAL (NUMTODSINTERVAL(1,'DAY')) SUBPARTITION BY LIST(distance) SUBPARTITION TEMPLATE ( SUBPARTITION local VALUES('L') TABLESPACE tbs1 , SUBPARTITION medium_long VALUES ('M') TABLESPACE tbs2 , SUBPARTITION long_distance VALUES ('D') TABLESPACE tbs3 , SUBPARTITION international VALUES ('I') TABLESPACE tbs4 ) (PARTITION p0 VALUES LESS THAN (TO_DATE('01-JAN-2005','dd-MON-yyyy'))) PARALLEL; CREATE INDEX from_number_ix ON call_detail_records(from_number) LOCAL PARALLEL NOLOGGING; CREATE INDEX to_number_ix ON call_detail_records(to_number) LOCAL PARALLEL NOLOGGING;
3.5.4.3 When to Use Composite Range-Range Partitioning
Composite range-range partitioning is useful for applications that store time-dependent data on multiple time dimensions.
Often these applications do not use one particular time dimension to access the data, but rather another time dimension, or sometimes both at the same time. For example, a web retailer wants to analyze its sales data based on when orders were placed, and when orders were shipped (handed over to the shipping company).
Other business cases for composite range-range partitioning include ILM scenarios, and applications that store historical data and want to categorize its data by range on another dimension.
Example 3-10 shows a range-range partitioned table account_balance_history
. A bank may use access to individual subpartitions to contact its customers for low-balance reminders or specific promotions relevant to a certain category of customers.
This example shows the use of interval partitioning. You can use interval partitioning in addition to range partitioning so that interval partitions are created automatically as data is inserted into the table. In this case 7-day (weekly) intervals are created, starting Monday, January 1, 2007.
Example 3-10 Creating a table with composite range-range partitioning
CREATE TABLE account_balance_history ( id NUMBER NOT NULL , account_number NUMBER NOT NULL , customer_id NUMBER NOT NULL , transaction_date DATE NOT NULL , amount_credited NUMBER , amount_debited NUMBER , end_of_day_balance NUMBER NOT NULL ) PARTITION BY RANGE(transaction_date) INTERVAL (NUMTODSINTERVAL(7,'DAY')) SUBPARTITION BY RANGE(end_of_day_balance) SUBPARTITION TEMPLATE ( SUBPARTITION unacceptable VALUES LESS THAN (-1000) , SUBPARTITION credit VALUES LESS THAN (0) , SUBPARTITION low VALUES LESS THAN (500) , SUBPARTITION normal VALUES LESS THAN (5000) , SUBPARTITION high VALUES LESS THAN (20000) , SUBPARTITION extraordinary VALUES LESS THAN (MAXVALUE) ) (PARTITION p0 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy')));
3.5.4.4 When to Use Composite List-Hash Partitioning
Composite list-hash partitioning is useful for large tables that are usually accessed on one dimension, but (due to their size) still must take advantage of parallel full or partial partition-wise joins on another dimension in joins with other large tables.
Example 3-11 shows a credit_card_accounts
table. The table is list-partitioned on region in order for account managers to quickly access accounts in their region. The subpartitioning strategy is hash on customer_id
so that queries against the transactions table, which is subpartitioned on customer_id
, can take advantage of full partition-wise joins. Joins with the hash partitioned customers table can also benefit from full partition-wise joins. The table has a local bitmap index on the is_active
column.
Example 3-11 Creating a table with composite list-hash partitioning
CREATE TABLE credit_card_accounts ( account_number NUMBER(16) NOT NULL , customer_id NUMBER NOT NULL , customer_region VARCHAR2(2) NOT NULL , is_active VARCHAR2(1) NOT NULL , date_opened DATE NOT NULL ) PARTITION BY LIST (customer_region) SUBPARTITION BY HASH (customer_id) SUBPARTITIONS 16 ( PARTITION emea VALUES ('EU','ME','AF') , PARTITION amer VALUES ('NA','LA') , PARTITION apac VALUES ('SA','AU','NZ','IN','CH') ) PARALLEL; CREATE BITMAP INDEX is_active_bix ON credit_card_accounts(is_active) LOCAL PARALLEL NOLOGGING;
3.5.4.5 When to Use Composite List-List Partitioning
Composite list-list partitioning is useful for large tables that are often accessed on different dimensions.
You can specifically map rows to partitions on those dimensions based on discrete values.
Example 3-12 shows an example of a very frequently accessed current_inventory
table. The table is constantly updated with the current inventory in the supermarket supplier's local warehouses. Potentially perishable foods are supplied from those warehouses to supermarkets, and it is important to optimize supplies and deliveries. The table has local indexes on warehouse_id
and product_id
.
Example 3-12 Creating a table with composite list-list partitioning
CREATE TABLE current_inventory ( warehouse_id NUMBER , warehouse_region VARCHAR2(2) , product_id NUMBER , product_category VARCHAR2(12) , amount_in_stock NUMBER , unit_of_shipping VARCHAR2(20) , products_per_unit NUMBER , last_updated DATE ) PARTITION BY LIST (warehouse_region) SUBPARTITION BY LIST (product_category) SUBPARTITION TEMPLATE ( SUBPARTITION perishable VALUES ('DAIRY','PRODUCE','MEAT','BREAD') , SUBPARTITION non_perishable VALUES ('CANNED','PACKAGED') , SUBPARTITION durable VALUES ('TOYS','KITCHENWARE') ) ( PARTITION p_northwest VALUES ('OR', 'WA') , PARTITION p_southwest VALUES ('AZ', 'UT', 'NM') , PARTITION p_northeast VALUES ('NY', 'VM', 'NJ') , PARTITION p_southeast VALUES ('FL', 'GA') , PARTITION p_northcentral VALUES ('SD', 'WI') , PARTITION p_southcentral VALUES ('OK', 'TX') ); CREATE INDEX warehouse_id_ix ON current_inventory(warehouse_id) LOCAL PARALLEL NOLOGGING; CREATE INDEX product_id_ix ON current_inventory(product_id) LOCAL PARALLEL NOLOGGING;
3.5.4.6 When to Use Composite List-Range Partitioning
Composite list-range partitioning is useful for large tables that are accessed on different dimensions.
For the most commonly used dimension, you can specifically map rows to partitions on discrete values. List-range partitioning is commonly used for tables that use range values within a list partition, whereas range-list partitioning is commonly used for a discrete list values within a range partition. List-range partitioning is less commonly used to store historical data, even though equivalent scenarios are all suitable. Range-list partitioning can be implemented using interval-list partitioning, whereas list-range partitioning does not support interval partitioning.
Example 3-13 shows a donations
table that stores donations in different currencies. The donations are categorized into small, medium, and high, depending on the amount. Due to currency differences, the ranges are different.
Example 3-13 Creating a table with composite list-range partitioning
CREATE TABLE donations ( id NUMBER , name VARCHAR2(60) , beneficiary VARCHAR2(80) , payment_method VARCHAR2(30) , currency VARCHAR2(3) , amount NUMBER ) PARTITION BY LIST (currency) SUBPARTITION BY RANGE (amount) ( PARTITION p_eur VALUES ('EUR') ( SUBPARTITION p_eur_small VALUES LESS THAN (8) , SUBPARTITION p_eur_medium VALUES LESS THAN (80) , SUBPARTITION p_eur_high VALUES LESS THAN (MAXVALUE) ) , PARTITION p_gbp VALUES ('GBP') ( SUBPARTITION p_gbp_small VALUES LESS THAN (5) , SUBPARTITION p_gbp_medium VALUES LESS THAN (50) , SUBPARTITION p_gbp_high VALUES LESS THAN (MAXVALUE) ) , PARTITION p_aud_nzd_chf VALUES ('AUD','NZD','CHF') ( SUBPARTITION p_aud_nzd_chf_small VALUES LESS THAN (12) , SUBPARTITION p_aud_nzd_chf_medium VALUES LESS THAN (120) , SUBPARTITION p_aud_nzd_chf_high VALUES LESS THAN (MAXVALUE) ) , PARTITION p_jpy VALUES ('JPY') ( SUBPARTITION p_jpy_small VALUES LESS THAN (1200) , SUBPARTITION p_jpy_medium VALUES LESS THAN (12000) , SUBPARTITION p_jpy_high VALUES LESS THAN (MAXVALUE) ) , PARTITION p_inr VALUES ('INR') ( SUBPARTITION p_inr_small VALUES LESS THAN (400) , SUBPARTITION p_inr_medium VALUES LESS THAN (4000) , SUBPARTITION p_inr_high VALUES LESS THAN (MAXVALUE) ) , PARTITION p_zar VALUES ('ZAR') ( SUBPARTITION p_zar_small VALUES LESS THAN (70) , SUBPARTITION p_zar_medium VALUES LESS THAN (700) , SUBPARTITION p_zar_high VALUES LESS THAN (MAXVALUE) ) , PARTITION p_default VALUES (DEFAULT) ( SUBPARTITION p_default_small VALUES LESS THAN (10) , SUBPARTITION p_default_medium VALUES LESS THAN (100) , SUBPARTITION p_default_high VALUES LESS THAN (MAXVALUE) ) ) ENABLE ROW MOVEMENT;
3.5.5 When to Use Interval Partitioning
Interval partitioning can be used for almost every table that is range partitioned and uses fixed intervals for new partitions.
The database automatically creates interval partitions as data for that partition is inserted. Until this happens, the interval partition exists but no segment is created for the partition.
The benefit of interval partitioning is that you do not need to create your range partitions explicitly. You should consider using interval partitioning unless you create range partitions with different intervals, or if you always set specific partition attributes when you create range partitions. You can specify a list of tablespaces in the interval definition. The database creates interval partitions in the provided list of tablespaces in a round-robin manner.
If you upgrade your application and you use range partitioning or composite range-* partitioning, then you can easily change your existing table definition to use interval partitioning. You cannot manually add partitions to an interval-partitioned table. If you have automated the creation of new partitions, then in the future you must change your application code to prevent the explicit creation of range partitions.
The following SQL statement initiates a change from range partitioning to using monthly interval partitioning on the sales
table.
ALTER TABLE sales SET INTERVAL (NUMTOYMINTERVAL(1,'MONTH'));
You cannot use interval partitioning with reference partitioned tables.
Serializable transactions do not work with interval partitioning. Inserting data into a partition of an interval partitioned table that does not have a segment yet causes an error.
3.5.6 When to Use Reference Partitioning
Reference partitioning is useful in certain situations.
Reference partitioning is useful in the following scenarios:
-
If you have denormalized, or would denormalize, a column from a parent table into a child table to get partition pruning benefits on both tables.
For example, your
orders
table stores theorder_date
, but theorder_items
table, which stores one or more items for each order, does not. To get good performance for historical analysis of orders data, you would traditionally duplicate theorder_date
column in theorder_items
table to use partition pruning on theorder_items
table.You should consider reference partitioning in such a scenario and avoid having to duplicate the
order_date
column. Queries that join both tables and use a predicate onorder_date
automatically benefit from partition pruning on both tables. -
If two large tables are joined frequently, then the tables are not partitioned on the join key, but you want to take advantage of partition-wise joins.
Reference partitioning implicitly enables full partition-wise joins.
-
If data in multiple tables has a related life cycle, then reference partitioning can provide significant manageability benefits.
Partition management operations against the parent table are automatically cascaded to its descendents. For example, when you add a partition to the parent table, that addition is automatically propagated to all its descendents.
To use reference partitioning, you must enable and enforce the foreign key relationship between the parent table and the reference table in place. You can cascade reference-partitioned tables.
The primary key-foreign key relationship must be enabled all the time and cannot be disabled. Also the relationship cannot be declared as deferred. These are mandatory requirements because the enabled primary key-foreign relationship is required to determine the data placement for the child tables.
3.5.7 When to Partition on Virtual Columns
Partitioning on virtual columns provides more flexibility to partition on a derived column.
Virtual column partitioning enables you to partition on an expression, which may use data from other columns, and perform calculations with these columns. PL/SQL function calls are not supported in virtual column definitions that are to be used as a partitioning key.
Virtual column partitioning supports all partitioning methods, plus performance and manageability features. To get partition pruning benefits, consider using virtual columns if tables are frequently accessed using a predicate that is not directly captured in a column, but can be derived. Traditionally, to get partition pruning benefits, you would have to add a separate column to capture and calculate the correct value and ensure the column is always populated correctly to ensure correct query retrieval.
Example 3-14 shows a car_rentals
table. The customer's confirmation number contains a two-character country name as the location where the rental car is picked up. Rental car analyses usually evaluate regional patterns, so it makes sense to partition by country.
In this example, the column country
is defined as a virtual column derived from the confirmation number. The virtual column does not require any storage. As the example illustrates, row movement is supported with virtual columns. The database migrates a row to a different partition if the virtual column evaluates to a different value in another partition.
Example 3-14 Creating a table with virtual columns for partitioning
CREATE TABLE car_rentals ( id NUMBER NOT NULL , customer_id NUMBER NOT NULL , confirmation_number VARCHAR2(12) NOT NULL , car_id NUMBER , car_type VARCHAR2(10) , requested_car_type VARCHAR2(10) NOT NULL , reservation_date DATE NOT NULL , start_date DATE NOT NULL , end_date DATE , country as (substr(confirmation_number,9,2)) ) PARTITION BY LIST (country) SUBPARTITION BY HASH (customer_id) SUBPARTITIONS 16 ( PARTITION north_america VALUES ('US','CA','MX') , PARTITION south_america VALUES ('BR','AR','PE') , PARTITION europe VALUES ('GB','DE','NL','BE','FR','ES','IT','CH') , PARTITION apac VALUES ('NZ','AU','IN','CN') ) ENABLE ROW MOVEMENT;
3.5.8 Considerations When Using Read-Only Tablespaces
Review these considerations when using read-only tables.
When a referential integrity constraint is defined between parent and child tables, an index is defined on the foreign key, and the tablespace in which that index resides is made read-only, then the integrity check for the constraint is implemented in SQL and not through consistent read buffer access.
The implication of this is if the child is partitioned and if only some child partitions have their indexes in read-only tablespaces and if an insert is made into one nonread-only child segment, then a TM enqueue is acquired on the child table in SX mode.
SX mode is incompatible with S requests, so that if you try to insert into the parent, it is blocked because that insert attempts to acquire an S TM enqueue against the child.