19 | 03 | 2024
Latest Articles
Popular Articles

Database Design

Partitioned Tables : Overview

User Rating:  / 0
PoorBest 

Partitioned Tables : Overview


See also Puget Sound Oracle User Group : Partition Examples and overview

 

At the physical level any table, any index has its associated segment with at least one entry in dba_segments. Data is stored in blocks. An extent regroups adjacent blocks belonging to the same segment. Any table, index segment has at least 1 extent, possible hundreds or thousands.

From Oracle 8.0 onwards we can partition our tables, our indexes and lob segments.
When a table or index is partitioned it consist of multiple segments. There is no 1 to 1 relationship anymore between the object and the segment, in stead any partitioned index or partitioned table has n entries in dba_segments where n is the number of partitions.


Partitioning can be useful for large tables and large indexes, advantages are

1. Maintenance and DBA tasks happen at the partition level.

Moving, rebuilding segments - when needed - can be done at the partition level.
Any object partition can be put in a dedicated tablespace, which can be put read only whether the other object partitions are in read only tablespaces or not.
Example a table is range partitioned with the insert date as the partition key. Old data is kept in a dedicated tablespace which can be put read only, whereas more recent data is kept in read write tablespaces.


2. Possible improved performance.

Partition wise joins and partition pruning can improve query performance. Hash partitioning can reduce buffer busy waits - read reduce hot spots - when there is concurrent DML. Partitioning can also decrease performance.


The partitioning type Oracle knows are



1. Hash Partitioning

2. Range Partitioning

3. List Partitioning

4. Composite Partitioning

5. Interval Partitioning ( 11G )

6. Reference Partitioning ( 11G )

7. System Partitioning ( 11G )

8. Virtual Column Partitioning ( 11G )

 

Relevant data dictionary views

 

DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
DBA_LOB_PARTITIONS
DBA_LOB_SUBPARTITIONS
DBA_MVIEW_DETAIL_PARTITION
DBA_PARTIAL_DROP_TABS
DBA_PART_COL_STATISTICS
DBA_PART_HISTOGRAMS
DBA_PART_INDEXES
DBA_PART_KEY_COLUMNS
DBA_PART_LOBS
DBA_PART_TABLES


Whether there are partitioned objects in your database can be retrieved with

 

SQL> select owner,table_name,partitioning_type,partition_count from dba_part_tables;

SQL> select owner,table_name,index_name,partitioning_type,partition_count from dba_part_indexes;

SQL> select table_owner,table_name,column_name,lob_name from dba_part_lobs;