What is System partition In Oracle

What is System partition In Oracle, when we can use it?

System partitioning

System partitioning enables application-controlled partitioning without having the database controlling the data placement.

The database simply provides the ability to break down a table into partitions without knowing what the individual partitions are going to be used for. All aspects of partitioning have to be controlled by the application. For example, an attempt to insert into a system partitioned table without the explicit specification of a partition fails.

System partitioning provides the well-known benefits of partitioning (scalability, availability, and manageability), but the partitioning and actual data placement are controlled by the application.

Using System Partitioning

System Partitioning enables you to create a single table consisting of multiple physical partitions. System partitioning does not use partitioning keys. Instead, it creates the number of partitions specified. Therefore, the resulting partitions have no bounds (range), values (list), or a partitioning method.

Because there are no partitioning keys, you must explicitly map the distributed table rows to the destination partition. When inserting a row, for example, you must use the partition extended syntax to specify the partition to which a row must be mapped.

Advantages of System Partitioned Tables

The main advantages of system-partitioned tables are that it can be used to create and maintain tables that are equipartitioned with respect to another table. For example, this means that a dependent table could be created as a system-partitioned table, with the same number of partitions as the base table. It follows that such a system-partitioned table can be used to store index data for a domain index, with the following implications:

• Pruning follows the base table pruning rules: when a partition is accessed in the base table, the corresponding partition can be accessed in the system-partitioned table.
• DDLs of the base table can be duplicated on the system-partitioned table. Therefore, if a partition is dropped on the base table, the corresponding partition on the system-partitioned table is dropped automatically.

Implementing System Partitioning

Consider how to implement system partitioning.

Creating a System-Partitioned Table

Example A describes how to create a system-partitioned table with four partitions. Each partition can have different physical attributes.

 

Example A: Creating System-Partitioned Tables

CREATE TABLE SystemPartitionedTable (c1 integer, c2 integer)

PARTITION BY SYSTEM

(

  PARTITION p1 TABLESPACE tbs_1,

  PARTITION p2 TABLESPACE tbs_2,

  PARTITION p3 TABLESPACE tbs_3,

  PARTITION p4 TABLESPACE tbs_4

);

Inserting Data into a System-Partitioned Table

Example B demonstrates how to insert data into a system-partitioned table. Both INSERT and MERGE statements (not shown here) must use the partition extended syntax to identify the partition to which the row should be added. The tuple (4,5) could have been inserted into any of the four partitions created in Example A. DATAOBJ_TO_PARTITION can also be used, as demonstrated by Example C.

Starting with Oracle Database 12c, Oracle recommends using DATAOBJ_TO_MAT_PARTITION, as demonstrated in Example D, instead of the DATAOBJ_TO_PARTITION function. The DATAOBJ_TO_MAT_PARTITION function supports local domain indexes on interval partitioned tables.

Note that the first line of code shows how to insert data into a named partition, while the second line of code shows that data can also be inserted into a partition based on the partition's order. The support for bind variables, illustrated on the third code line, is important because it allows cursor sharing between INSERT statements.

The DATAOBJ_TO_PARTITION function shown in Example C determines the absolute partition number, given the physical partition identifier. However, if the base table is interval partitioned, then there might be holes in the partition numbers corresponding to unmaterialized partitions. Because the system partitioned table only has materialized partitions, DATAOBJ_TO_PARTITION numbers can cause a mis-match between the partitions of the base table and the partitions of the underlying system partitioned index storage tables.

The new function, DATAOBJ_TO_MAT_PARTITION, shown in Example D, returns the materialized partition number (as opposed to the absolute partition number) and helps keep the two tables in sync. Indextypes planning to support local domain indexes on interval partitioned tables should migrate to the use of this function.

 

Example B: Inserting Data into System-Partitioned Tables

INSERT INTO SystemPartitionedTable PARTITION (p1) VALUES (4,5);

 

Example C: Inserting Data into System-Partitioned Tables; DATAOBJ_TO_PARTITION

INSERT INTO SystemPartitionedTable PARTITION

  (DATAOBJ_TO_PARTITION (base_table, :physical_partid))

  VALUES (...);

 

Example D: Inserting Data into System-Partitioned Tables; DATAOBJ_TO_MAT_PARTITION

INSERT INTO SystemPartitionedTable PARTITION

  (DATAOBJ_TO_MAT_PARTITION (base_table, :physical_partid))

  VALUES (...);

 

Deleting and Updating Data in a System-Partitioned Table

While delete and update operations do not require the partition extended syntax, Oracle recommends that you use it if at all possible. Because there is no partition pruning, the entire table is scanned to execute the operation if the partition-extended syntax is omitted. This highlights the fact that there is no implicit mapping between the rows and the partitions.

 

Supporting Operations with System-Partitioned Tables

The following operations continue to be supported by system partitioning:

• Partition maintenance operations and other DDLs, with the exception of:
• ALTER INDEX SPLIT PARTITION
• ALTER TABLE SPLIT PARTITION
• CREATE TABLE (as SELECT)
• Creation of local indexes, with the exception of unique local indexes because they require a partitioning key
• Creation of local bitmapped indexes
• Creation of global indexes
• All DML operations
• INSERT AS SELECT operations with partition extended syntax, as shown in the following code example:

Inserting Data into a Particular Partition of a Table

 

INSERT INTO TableName

  PARTITION (

    PartitionName|

    DATAOBJ_TO_MAT_PARTITION (base_table, :physical_partid))

  AS SubQuery

 The following operations are no longer supported by system partitioning because system partitioning does not use a partitioning method, and therefore does not distribute rows to partitions.

• CREATE TABLE AS SELECT An alternative approach is to first create the table, and then insert rows into each partition.
• INSERT INTO TableName AS SubQuery

Key Points about System Partitioning:

1. No Partitioning Key in Table Definition
• Unlike range, list, or hash partitioning, you don’t define partitioning columns or keys.
• The table definition contains partitions, but Oracle does not evaluate values to decide where to place a row.
2. Application-Controlled Placement
• When inserting rows, you must use the PARTITION clause to tell Oracle which partition the row should go into.

Example:

INSERT INTO sales PARTITION (q1_2025) VALUES (1001, DATE '2025-02-15', 500);

Here, the row is explicitly directed into partition q1_2025.

3. Oracle Does Not Validate Partitioning Keys
• Since there is no defined partitioning key, Oracle does not check whether a row “belongs” to that partition.
• This gives maximum flexibility but also more responsibility to the application.
4. Performance
• System partitioning is efficient because Oracle avoids partition pruning logic and checks.
• Useful when the application already knows where rows should be stored.
5. Typical Use Cases
• Data warehouse staging tables where ETL processes already know partition mapping.
• Distributed or sharded-like applications where the application controls row distribution.
• Temporary partitioned data where rules are too dynamic to encode in the schema.
6. Restrictions
• Some Oracle features that rely on partitioning keys (like automatic partition pruning) won’t work in the same way.
• You must carefully manage partition references in queries.

Conclusion:

In Oracle Database, System Partitioning is a partitioning strategy where the database does not automatically decide how rows should be distributed among partitions. Instead, the application (or the user’s code) must explicitly direct rows into the correct partition when inserting data.

System Partitioning in Oracle means the application controls row placement into partitions instead of Oracle using partitioning keys. It’s flexible and fast but shifts responsibility from the database to the developer.