The complete and the most recent version of this article can be viewed
from My Oracle Support Knowledge Section.
Master Note for Partitioning [ID 1312352.1]
In this Document
Scope and Application
Master Note for Partitioning
|This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.|
Applies to:Oracle Database Products > Oracle Database > Data Warehousing
Information in this document applies to any platform.
PurposeThis article is intended to give you a single reference point and to assist in quickly finding various information about partitioning.
Scope and ApplicationThe document will cover the following topics:
Master Note for Partitioning
Concepts/Definitions SectionOverview of Partitioning
Partitioning enables you to decompose very large tables and indexes into smaller and more manageable pieces called partitions. Each partition is an independent object with its own name and optionally its own storage characteristics.
Partitioning offers these advantages:
- Increased availability of mission-critical databases if critical tables and indexes are divided into partitions to reduce the maintenance windows, recovery times, and impact of failures.
- Easier administration of schema objects reducing the impact of scheduled downtime for maintenance operations.
- Reduced contention for shared resources in OLTP systems
- Enhanced query performance: Often the results of a query can be achieved by accessing a subset of partitions, rather than the entire table. For some queries, this technique (called partition pruning) can provide order-of-magnitude gains in performance.
Overview of Partitioning:
OracleÂ® Database Concepts 11g Release 2 (11.2) Part Number E16508-05 Chapter 4
OracleÂ® Database VLDB and Partitioning Guide 11g Release 2 (11.2) Part Number E16541-08
Relevant articles regarding partitions creation and maintenance:
Document 69715.1 Creating & Adding Table and Index Partitions
Document 165303.1 Examples about Insert into Range Partitioned Tables
Document 164874.1 Example of Script to Create a Range Partition Table
Document 166652.1 Example of Script to Maintain Range Partitioned Table
Document 149116.1 Oracle9i Partitioning Enhancements, LIST Partitioning
Document 209368.1 Range List Partitioning - Oracle 9.2 Enhancement
Document 276158.1 Partitioning Enhancements in Oracle 10g
Relevant articles regarding latest features:
Document 452447.1 11g Partitioning Enhancements
Document 785462.1 11g New Features:System Partitioning
Document 805976.1 11g New Features:INTERVAL PARTITIONING
Document 943567.1 11g new feature: Extended Composite Partitioning (Overview, Example and Use)
Document 761251.1 Oracle 11G Reference Partitioning examples
Document 466352.1 11g Feature: Interval Partitioning Example
Document 757754.1 Interval Partitioning By Week
Relevant articles regarding partition pruning:
Document 179518.1 Partition Pruning and Joins
Document 166118.1 Partition Pruning/Elimination (This article provide a very detailed example of how to identify which partitions/subpartitions were accessed during an execution of a statement using event 10128)
How-To SectionHow to Partition a Non-partitioned Table
Document 1070693.6 How to Partition a Non-partitioned Table
Document 472449.1 How To Partition Existing Table Using DBMS_Redefinition
'How to' relevant articles for partitioning types
Document 854332.1 How To Introduce Interval Parititioning into a Range Partitioned Table
Document 165701.1 How to Implement Hash Partitioning on IOT Tables in 9i & Above
Document 1266993.1 How To Use Multicolumn Partitioning Keys
Document 74181.1 Partitioning Tables with User-Defined Types and LOBs
Document 1304370.1 How to use Partition Names for ref partitioning tables when Split is used
Document 854332.1 How To Introduce Interval Partitioning into a Range Partitioned Table
Document 846405.1 How To Change the Partition Column Of A Partitioned Table Using DBMS_Redefinition
'How to' relevant articles for partitioned indexes:
Document 69374.1 Partitioned Indexes: Global, Local, Prefixed and Non-Prefixed
Document 74224.1 How to Create Primary Key Partitioned Indexes
Document 795854.1 How To Update Both Global and Local Indexes when Moving Table Partition?
'How to' relevant articles for statistics collection when partitions are used:
Document 175258.1 How to Compute Statistics on Partitioned Tables and Indexes
Document 237538.1 How to Move from ANALYZE to DBMS_STATS on Partitioned Tables - Some Examples
Document 111990.1 ORA-14508: ANALYZE PARTITION TABLE VALIDATE STRUCTURE CASCADE
Document 1050294.1 STALE_STATS OF SUB PARTITION INDEX REPORTED INCORRECTLY IN DBA_TAB_STATISTICS
Document 1302628.1 Collect statistics for a large partitioned table takes a lot when incremental is used
Document 1319225.1 Collect incremental statistics for a large partitioned table in versions 10.2 and 11
Troubleshooting SectionRelevant bulletin articles for partitioning related issues:
Document 209070.1 Partition Pruning based on Joins to Partitioning Criteria Stored in Dimension Tables
Document 378138.1 What to check when the fast split partitioning does not appear as working?
Document 232628.1 Fast Split partitioning in 9iR2
Relevant articles for troubleshooting import slow issues with partitioning
Document 1073195.1 Data Pump Import (Impdp) slow when importing partitioned table
Document 752904.1 DataPump Export of Partitioned Table is Very Slow and Apparently Hangs
Document 1224663.1 IMPDP Raises ORA-39001, ORA-39203 When Importing A Partition Of A Partitioned Table Over A Network Link
Document 1281826.1 What Types of Partitioning Are Eligible for Partition Change Tracking (PCT) Fast Refresh?
Document 165599.1 Top Partitioned Tables Issues
Document 199623.1 Top Issues Encountered Regarding Split Partition
Document 166215.1 Top Partition Performance Issues
Document 372357.1 Fast Split Partitioning Not Ocurring When It Was Expected
Document 272312.1 How to Recreate a Table Partition After Having Dropped the Datafile?
Document 959116.1 Interval Partitioning Does Not Inherit Logging
Document 198120.1 Exchange Partitions - Common Problems
Document 1077819.1 PARTITIONED TABLE: CHANGE IN BUFFER_POOL FOR A PARTITION NEEDS A REBOUNCE
Relevant article to handle various errors:
Document 887659.1 Getting ORA-01410 for Partitioned Tables Without Any Apparent DDL On The Partitions.
Document 1081230.1 INTERVAL RANGE Partition Giving ORA-14400
Document 389804.1 Range Partition Splitting Fails with ORA-01882
Document 405922.1 ORA-07445 [evaopn2] Query With Bit Mapped Indexes And Partitioning
Document 1084542.1 Ora-600 Internal Error Code, Arguments: [Kkedsamp: Bad Partitioning Info.], , ,
Document 1050966.1 ORA-14652 When Using Reference Partitioning
Document 784989.1 ORA-600 [kdblGetRDBA] During Create Table with Compression and Partitioning
Document 790630.1 ORA-600 [Kkpamdgspam1] When Joining Partitioned Tables at Different Partitioning Levels
Document 727306.1 Ora-14074 When Trying To Add Partition
Dictionary issues introduced with partitioning
Document 1289275.1 OBJ$-PARTOBJ$-TABPART$ mismatch - Dictionary Inconsistency reported for Interval Range Partition Tables
Relevant article to handle size for partitioning
Document 729149.1 Table/Index (partition) Growth Is Far More Than Expected
Install partitioning option
Document 434743.1 Can The Partitioning Option Be Deinstalled When System Partitioned Objects Are Used
Document 430239.1 How To De-install Partitioning Option From Enterprise Edition?
This community has been set up for My Oracle Support (MOS) users, and is being moderated by Oracle. The goal of this community is to exchange database related Data Warehousing knowledge and concepts including Partitioning.
White PapersPartitioning with Oracle Database 11g Release 2 (September 2009)
Partitioning in Oracle Database 11g (June 2007)
Partitioning White papers are also accessible via Note 1329441.1
ReferencesNOTE:1329441.1 - White Papers for Data Warehousing Components in the Oracle Database