Partitioning ==>Increased performance and Ease of data management
Partitioning allows a database object(Table/View/IOT-index-organized table) to be subdivided into smaller pieces,called a partition.
It has its own name, and may optionally have its own storage characteristics can be managed either collectively or individually. From application point of View , no modifications are required.
Moreover, partitioning can greatly reduce the total cost of data ownership, using a “tiered archiving” approach of keeping older info still online on low cost storage devices.IT administrators can implement Information Lifecycle Management (ILM) protocols by partitioning data and moving historical data to low-cost storage.Partitioning can be used to obtain better concurrency as well as to decrease the number of rows to be processed through partition pruning and partition-wise joins
Partitioning for Performance
Partitioning pruning==> leveraging the partitioning metadata to only touch the data of relevance for a SQL operation
Partitioning for Manageability
Partitioning for Availability
Lifecycle Management(ILM) with Partitioning
Which Partitioning Method Should Be Used?
Step 1 – Is Partitioning Necessary? Step 2 - Should This Object Be Partitioned? Step 3 - Which Partitioning Method Should Be Used? Step 4 - Identify the Partition Key Step 5 – Performance Check & Access Path Analysis Step 6 – Partitioned Table Creation and Data Migration Method: 1 – Straight Insert Oracle Data Pump Data Pump Access Methods Method 2 – Import/Export using Data Pump Step 7 – Maintenance Step PARTITION MAINTENANCE OPERATIONS Adding partitions or sub partitions Dropping a partition Moving a partition Splitting and merging partitions Exchanging a partition with a table Renaming a partition
Practical Partitioning Case Study Oracle General Ledger -->340 GB in size with General Ledger representing 90% of this data. Background – Current Table Volumes , (35 % of database) , (6% of database) ,(5% of database) Strategy ? Partition Maintenance ?
The Benefits of This Partitioning Strategy
Maintenance Purging historic data Performance- Performance improvements of SQL were achieved through partition pruning. Read-only partitions-in read only table spaces as this reduced the cost of storage
DBA_PART_TABLES |DBA_PART_INDEXES DBA_TAB_PARTITIONS | DBA_TAB_SUBPARTITIONS DBA_PART_KEY_COLUMNS | DBA_SUBPART_KEY_COLUMNS DBA_PART_COL_STATISTICS | DBA_SUBPART_COL_STATISTICS DBA_PART_HISTOGRAMS | DBA_SUBPART_HISTOGRAMS DBA_IND_PARTITIONS |DBA_IND_SUBPARTITIONS DBA_SUBPARTITION_TEMPLATES
White-paper:Optimizing Storage for Oracle E-Business Suite Applications
Meta link:Using Database Partitioning with Oracle E-Business Suite
11g Partitioning Features
Partitioning in Oracle