New Oracle 11g Partition: System Partition

Partition partitioning is a performance and Management Optimization Technology launched by Oracle. In the Oracle database technology system, Partition is a big Data processing policy officially recognized by Oracle in the DW (Data Warehouse) system.
 
For the single Partition technology itself, 11G is an important version. Some automated Partitioning technologies and enhancement strategies are available in the 11g version. For example, Reference Partition, Interval Partition, Partitioning Virtual Columns, System Partition, and Extended Composite Partitioning ).
 
This article describes the 11g System Partition feature.

-------------------------------------- Split line --------------------------------------

Oracle 11g new feature: replace variables in the RMAN script

New Features of Oracle 11g-Result Cache

New Features of Oracle 11g-Description of Automatic Diagnostic archive (ADR)

New Features of Oracle 11g -- DB_ULTRA_SAFE parameter description

New Features of Oracle 11g-SQL Plan Management

New Features of Oracle 11g-instructions for managing SPFILE

-------------------------------------- Split line --------------------------------------

1. Environment Introduction
 
 

I chose Oracle 11R2 as the lab object.
 
 

 

SQL> select * from v $ version;
 
 

BANNER
 
--------------------------------------------------------------------------------
 
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
 
PL/SQL Release 11.2.0.1.0-Production
 
CORE 11.2.0.1.0 Production
 
 

TNS for Linux: Version 11.2.0.1.0-Production
 
NLSRTL Version 11.2.0.1.0-Production
 
 

 

2. System Partition
 
 

In other Partition types, the Partition key selection is a problem. The so-called Partition refers to splitting a data table segment into multiple storage segments for storage. In the traditional sense, Partition stays at the definition level, as long as we determine the Partition key and Partition policy when defining the data table. In the subsequent use of data tables, we are actually "Transparent" to partitions.
 
If you perform DML and select operations on the data table, you do not need to specify or control the partition to which the data records are inserted. The only difference is that you can modify the partition key. If you modify the partition key and the partition key affects the partition layout, such DML operations are not allowed.
 
The 11g System Partition provides different options. It provides the SQL DML operator with an option to specify the location where "data is saved.
 
The process of creating System Partition is also determined during the process of defining the data table.
 
 

 

SQL> create table t partition by system (partition p1 tablespace users,
 
2 partition p2 tablespace EXAMPLE) as select * from dba_objects where 1 = 0;
 
 

Create table t partition by system (partition p1 tablespace users,
 
Partition p2 tablespace EXAMPLE) as select * from dba_objects where 1 = 0
 
 

ORA-14704: do not allow the following operations on the SYSTEM partition table: Create table as select
 
 

 

In system partition, spilt and cats operations on partitions are not allowed. The correct processing method is as follows:
 
 

 

SQL> create table t
 
2 (owner varchar2 (100 ),
 
3 object_name varchar2 (1000 ),
 
4 object_id number)
 
5 partition by system
 
6 (partition p1 tablespace users,
 
7 partition p2 tablespace example );
 
 

Table created
 
 

 

In the statement, we specify that the data table adopts the partition policy-system policy, set the two partitions p1 and p2, and specify the tablespace storage location of the two partitions.
 
Note: In this process, we did not specify Partition Rules, that is, we did not tell the database how to perform data sharding when a data is inserted.
 
Check the data dictionary to determine the segment structure.
 
 

 

SQL> select segment_name, partition_name, segment_type from dba_segments where owner = 'Scott 'and segment_name = 'T ';
 
 

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
 
----------------------------------------------------------
 
T P1 TABLE PARTITION
 
T P2 TABLE PARTITION
 
 

SQL> select partitioning_type from dba_part_tables where owner = 'Scott 'and table_name = 'T ';
 
 

PARTITIONING_TYPE
 
-----------------
 
SYSTEM
 
 

 

Partition type, determined to be system partition. In system partiton, you can also create a local index object.
 
 

 

SQL> create index idx_t_id on t (object_id) local;
 
 

Index created
 
 

SQL> select segment_name, partition_name, segment_type from dba_segments where owner = 'Scott 'and segment_name = 'idx _ T_ID ';
 
 

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
 
----------------------------------------------------------
 
IDX_T_ID P1 INDEX PARTITION
 
IDX_T_ID P2 INDEX PARTITION

For more details, please continue to read the highlights on the next page:

1 2 Next Page