FAQ: Oracle partitioning
1. What is partitioning?
Partitioning is the process of separating data into logical subsets. In the same way as normal tables and indexes, partitions are physical segments for which extents are allocated (see Note 666061).
2. Which objects can be partitioned?
Tables and indexes can be partitioned. All combinations are theoretically possible:
- Table not partitioned, relevant index partitioned
- Table partitioned, relevant index not partitioned
- Table partitioned, relevant index partitioned
Up to 64,000 partitions are possible. Objects with LONG fields and LONG RAW fields cannot be partitioned.
3. What are the advantages of partitioning?
- Improved administration because many operations can be carried out on separate partitions (data loads, index creation and rebuilding, backup/recovery and so on).
- Partitions that are no longer required can simply be dropped (no reorganization to recover disk space and no time consuming DELETEs are necessary).
- You can improve the performance of queries if only specific partitions have to be read.
- Partitioning is transparent for the application and does not require any change to SQL queries and DML statements.
- Avoid redo log data when you delete old data because, unlike a DELETE, a DROP or TRUNCATE does not generate redo log data.
4. What problems can occur in connection with partitioning?
Validation of partitioned objects with VALIDATE STRUCTURE encounters an ORA-14508 error if the utlvalid.sql script was not executed for the executing database user. To avoid the error, see Note 514178 and execute utlvalid.sql for all relevant users.
ORA-14400 occurs if you want to insert an entry in the table that does not match the value range of any partition. In such a case, you must compare the value of the entry with the definitions of the partitions to determine the cause of the error.
Queries that do not contain the partitioning criterion in the WHERE clause, must scan ALL partitions. In these cases, the effort increases in proportion to the number of partitions.
5. How many types of partitioning exist?
Each partition includes data from a specific value range, for example:
Column YEAR: JAN 01 2002 - DEC 31 2002 -> Partition 1
Column YEAR: JAN 01 2003 - DEC 31 2003 -> Partition 2
Column YEAR: JAN 01 2004 - DEC 31 2004 -> Partition 3
For each partition, there is a list specifying the values to be assigned to this partition, for example:
Column COUNTRY: "Germany", "France" -> Partition 1 (Europe)
COLUMN COUNTRY: "USA", "Mexico" -> Partition 2 (America)
If you specify a default partition, all records that do not match any list are included in this partition.
A hash algorithm is used to distribute the records at random to the individual partitions.
Combined partitioning: Several of the above partitioning types can be combined in the form of PARTITION and SUBPARTITION (for example, range hash or range list).
The following different partitioning methods are used in relation to indexes:
Local partitioning: There is a 1:1 relationship between the table and index partitions. Each index partition contains the values of exactly one related table partition.
Global partitioning: The index partitioning is independent of the table partitioning in question. An index partition can contain values from different table partitions.
6. What is the Oracle syntax for creating partitioned objects?
Example of range partitioning:
CREATE TABLE MONTHS (MONTH NUMBER, DAYS NUMBER)
PARTITION BY RANGE (MONTH)
( PARTITION WINTER VALUES LESS THAN (4),
PARTITION SPRING VALUES LESS THAN (7),
PARTITION SUMMER VALUES LESS THAN (10),
PARTITION AUTUMN VALUES LESS THAN (13));
Example of list partitioning:
CREATE TABLE MONTHS (MONTH VARCHAR(20), DAYS NUMBER)
PARTITION BY LIST (MONTH)
( PARTITION WINTER VALUES ('January', 'February', 'March'),
PARTITION SPRING VALUES ('April', 'May', 'June'),
PARTITION SUMMER VALUES ('July', 'August', 'September'),
PARTITION AUTUMN VALUES ('October', 'November', 'December'));
Example of hash partitioning:
CREATE TABLE MONTHS (MONTH VARCHAR(20), DAYS NUMBER)
PARTITION BY HASH(MONTH)
7. How can I find out which objects are partitioned?
The following statement provides all objects with partitions at Oracle level:
SELECT TABLE_OWNER OWNER, 'TABLE' TYPE, TABLE_NAME NAME
GROUP BY TABLE_OWNER, TABLE_NAME
SELECT INDEX_OWNER, 'INDEX', INDEX_NAME
GROUP BY INDEX_OWNER, INDEX_NAME;
8. How do I determine all the partitions of a partitioned object?
You can receive the partitions of a table as follows:
SELECT * FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = '<table_name>';
You can determine the partitions of an index as follows:
SELECT * FROM DBA_IND_PARTITIONS WHERE INDEX_NAME = '<index_name>';
9. Where does Oracle store administrative information for partitions?
Oracle stores administrative information for the partitions in the DBA_PART_TABLES and DBA_PART_INDEXES views, for example:
- PARTITIONING_TYPE: Partitioning type
- PARTITION_COUNT: Number of partitions
- DEF_*: Default values for memory and configuration parameters (see Note 666061).
- The DBA_PART_KEY_COLUMNS view contains the columns to which an object is partitioned:
- NAME: Name of the partitioned object
- COLUMN_NAME: Column name
- COLUMN_POSITION: Position of the column during partitioning
- The DBA_TAB_PARTITIONS and DBA_IND_PARTITIONS views contain the high value of the partition (in other words, during range partitioning, the value that was specified with LESS THAN):
- HIGH_VALUE: high value of the partition
10. Are objects partitioned by default in the R/3 environment?
Partitioned objects are not delivered in normal SAP R/3 Systems. On the other hand, many InfoCube tables in the Business Warehouse area (BW) are partitioned.
11. Can I partition additional R/3 objects depending on demand?
As of 4. 6B, R/3 DDIC supports a simple range partitioning of tables based on exactly one column. This functionality is required when you use BW.
Note 742243 describes the prerequisites and possibilities in Release 4.6C or higher for creating objects as range, list or hash partitioned. However, no options will be available for defining subpartitions. Indexes can only ever be partitioned in the same way as the underlying tables (or not at all). See also Note 105047.
If you want to use partitioning that is not supported by your R/3 Release, you can also partition the objects manually at Oracle level. Note, however, that this partitioning can disappear if you perform certain actions (such as table conversions).
12. Are objects partitioned by default in the Oracle DDIC?
As of Oracle9i, multiple objects belonging to the Logminer (see Note 701562) and whose names start with LOGMNR or LOGSTDBY are partitioned when created under the SYSTEM user, for example:
As of Oracle 10g, STREAMS$_APPLY_SPILL_MSGS_PART and a large number of tables from the workload repository (WRH$_...) are also partitioned.
13. Can I deactivate partitioning in BW?
If you use BW functions, then partitioned objects are an essential prerequisite for good database performance. Therefore deactivation of the Oracle partitioning option is not permitted in this case.
However, if BW is automatically installed when you install another product (such as NetWeaver04), but you do not actually use BW, you can deactivate the partitioning option to reduce the license costs for a database that is directly licensed with Oracle (for example). For more information, see Note 859841.
14. Where can I find more information about partitioning?
Oracle9i Online Documentation:
Oracle9i Database Concepts
-> 11 Partitioned Tables and Indexes