02 | 12 | 2023
Latest Articles
Popular Articles

Database Design

Partitioned Tables : Overview

User Rating:  / 0

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



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;