Database Partitioning -- process of logically and/or physically segmenting data (and it’s associated storage, tables and indexes) into more manageable pieces, in order to improve manageability and accessibility.
A good partitioning design lies the choice of a suitable partitioning key, typically be the column that is most frequently used as the predicate to access the data.
Partitioning Methods:
- Range Partitioning -- Example: partitioning the GL_BALANCES table as ranges by "Period Name".
- List Partitioning -- Example: Partitioning OE_ORDER_LINES_ALL table to have categories listed like open & closed Orders using OPEN_FLAG.
- Hash Partitioning -- Hash function to the partitioning key to stripe data into partitions.
- Composite Partitioning -- Data is partitioned by using both the range method and then sub-partitioned by the hash method. Example: WF_ITEM_ACTIVITY_STATUSES Partitioned with Range of Item Type and then sub partitioned using Hash Method.
Index Partitioning & Methods:
Partitioned or non-partioned indexes can be used with partitioned or non-partitioned tables.
- Global Non-partitioned (prefixed)
- Global Partitioned (prefixed)
- Local Partitioned Prefixed
- Local Partitioned Non-Prefix
Advantages:
- It brings significant benefits in performance - Improves access path/scanning the current partition
- Easy to manage the table and index can be rebuild at partition level
- Improves purge performance
- Improves Upgrade performance
- No modifications to application queries are necessary when accessing a partitioned table.
- CBO is partition aware.
Note: If an incorrect partitioning design is chosen, partitioning can actually degrade rather than enhance performance.
Important Database Views:
DBA_TABLES
DBA_PART_TABLES
DBA_TAB_PARTITIONS
DBA_PART_KEY_COLUMNS
DBA_PART_HISTOGRAMS
DBA_PART_COL_STATISTICS
DBA_SUBPART_KEY_COLUMNS
DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
DBA_PART_INDEXES
No comments:
Post a Comment