Details about Reference Partition in Oracle 11g

Details about Reference Partition in Oracle 11g

Data Partition is a solution for Big Data Objects proposed by Oracle in the early days. After the evolution of several versions, Partition is still a popular and widely used technical strategy with a high degree of acceptance.

From the perspective of the Oracle product line, the success of Partition is inseparable from the continuous enrichment and improvement of Oracle's Partitioning technologies and solutions. In each version, the Partition technology has introduced some new advances and developments. Whether it is 8, 8 I, 11g, 12c, the Partition technology is constantly moving forward to meet more complex practical application requirements.

This article mainly introduces the new Reference Partitioin of 11g. Reference Partition is applicable to primary-foreign key associations. After the primary table is partitioned, the Reference Partition can be used to implement automatic sub-Table Partitioning (no Partition key exists in the table ). After the Reference Partition, the data records in the same primary table Partition and the corresponding sub-table records are all in the same sub-Table Partition.

This feature and partition type can bring many benefits to daily O & M in terms of performance and management. Next, I will introduce Reference Partiton through a series of experiments.

1. experiment environment Introduction

I chose Oracle 11g for testing. The specific version is 11.2.0.4.

SQL> select * from v $ version;

BANNER

-----------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

PL/SQL Release 11.2.0.4.0-Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0-Production

NLSRTL Version 11.2.0.4.0-Production

2. Create a Reference Partition data table

There is no much difference from creating a common primary and foreign key data table. First, we need to create a primary table with partitions.

SQL> create table t_master

2 (object_id number,

3 owner varchar2 (100 ),

4 object_name varchar2 (100 ),

5 object_type varchar2 (100)

6)

7 partition by list (owner)-List partition type

8 (

9 partition p0 values ('public '),

10 partition p1 values ('sys '),

11 partition p3 values (default)

12)

13;

-- Add primary key constraints

SQL> alter table t_master add constraint pk_t_master primary key (object_id );

Table altered

Create a sub-Table. Note: The Reference Partition does not require the sub-table to contain the Partition key. The Reference relationship is the basis of the sub-Table Partition. In addition, using Reference Partition requires that you create a sub-table and define foreign key constraints in the same statement.

SQL> create table t_detail

2 (object_id number,

3 master_id number,

4 obj_comment varchar2 (100 ),

5 obj_type varchar2 (100 ),

6 constraint fk_mas_det foreign key (master_id) references t_master (object_id)

7) partition by reference (fk_mas_det );

 

Create table t_detail

(Object_id number,

Master_id number,

Obj_comment varchar2 (100 ),

Obj_type varchar2 (100 ),

Constraint fk_mas_det foreign key (master_id) references t_master (object_id)

) Partition by reference (fk_mas_det)

ORA-14652: does not support referencing partition Foreign keys

We receive an Oracle error. First, let's take a look at the syntax for defining reference partition. In the create table statement, we need to create a name that defines the foreign key constraint. Then, use the partition by statement to define the foreign key as the division basis.

Current error ORA-14652, check the official explanation for this error.

[Oracle @ localhost ~] $ Oerr ora 1, 14652

14652,000 00, "reference partitioning foreign key is not supported"

// * Cause: The specified partitioning foreign key was not supported

// For reference-partitioned tables. All columns of

// Partitioning foreign key must be constrained not null

// Enabled, validated, and not deferrable constraints. Furthermore,

// A virtual column cannot be part of the partitioning foreign key.

// * Action: Correct the statement to specify a supported

// Partitioning foreign key.

The error cause is prompted. If Reference Partition is used, the foreign key column cannot be empty. The standard foreign key definition does not specify that the foreign key column must be empty. However, if you use the Partitioning technology, you must specify that the foreign key column cannot be empty.

These constraints are actually understandable. You do not need to specify the Partition key for Reference Partition, but you still need the Partition key (or Partition factor ). Without a foreign key value, Oracle will not be allowed to create partitions in the primary table. The statement for modifying the table creation is as follows:

SQL> create table t_detail

2 (object_id number,

3 master_id number not null,

4 obj_comment varchar2 (100 ),

5 obj_type varchar2 (100 ),

6 constraint fk_mas_det foreign key (master_id) references t_master (object_id)

7) partition by reference (fk_mas_det );

Table created

The following two data tables are observed from the perspective of partition tables.

SQL> select partition_name, high_value, partition_position from dba_tab_partitions where table_owner = 'sys 'and table_name = 't_ master ';

PARTITION_NAME HIGH_VALUE PARTITION_POSITION

-----------------------------------------------------

P0 'public' 1

P1 'sys '2

P3 default 3

SQL> select partition_name, high_value, partition_position from dba_tab_partitions where table_owner = 'sys 'and table_name = 't_detail ';

PARTITION_NAME HIGH_VALUE PARTITION_POSITION

-----------------------------------------------------

P0 1

P1 2

P3 3

Note: If the high_value column of the t_detail sub-table is empty, the table does not have a clear partition key and the primary table partition key owner does not exist in the sub-table. In addition, the partition structure, quantity, and name of the sub-table are identical to those of the master table.

From the segment structure segment, we can see that the 11g defered segment creation is not applied.

SQL> select segment_name, partition_name, segment_type from dba_segments where owner = 'sys 'and segment_name in ('t_master', 't_ detail ');

SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE

---------------------------------------------------------

T_MASTER P3 TABLE PARTITION

T_MASTER P1 TABLE PARTITION

T_MASTER P0 TABLE PARTITION

T_DETAIL P3 TABLE PARTITION

T_DETAIL P1 TABLE PARTITION

T_DETAIL P0 TABLE PARTITION

6 rows selected

3. Data insertion Experiment

Next we will conduct an experiment on data insertion and Partition Distribution. First, insert the data in the primary table:

SQL> insert into t_master select object_id, owner, object_name, object_type from dba_objects;

120361 rows inserted

SQL> commit;

Commit complete

SQL> exec dbms_stats.gather_table_stats (user, 't_ Master', cascade => true );

PL/SQL procedure successfully completed

Insert sub-table data:

SQL> insert into t_detail select seq_t_detail.nextval, object_id, object_name, object_type from dba_objects where object_name not in

('Seq _ T_DETAIL ');

120361 rows inserted

SQL> commit;

Commit complete

SQL> exec dbms_stats.gather_table_stats (user, 't_detail ', cascade => true );

PL/SQL procedure successfully completed

According to the current data relationship, it should be a master table record, corresponding to the relationship of a table record. We check the data dictionary.

SQL> select table_name, partition_name, high_value, num_rows from dba_tab_partitions where table_owner = 'sys 'and table_name in ('t _ master ');

TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS

---------------------------------------------------------------------------

T_MASTER P0 'public' 33996

T_MASTER P1 'sys' 37817

T_MASTER P3 default 48548

SQL> select table_name, partition_name, high_value, num_rows from dba_tab_partitions where table_owner = 'sys 'and table_name in ('t _ DETAIL ');

TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS

---------------------------------------------------------------------------

T_DETAIL P0 33996

T_DETAIL P1 1 37817

T_DETAIL P3 48548

The data distribution is correct. Note: The subtable partition distribution is based entirely on the partition number corresponding to the primary table record. The sub-table records corresponding to the same primary table partition records must be on the same sub-Table partition. Subtable records corresponding to different primary table partition records cannot be on the same subtable partition.

To facilitate the experiment, insert more data:

SQL> insert into t_detail select seq_t_detail.nextval, object_id, object_name, object_type from dba_objects where object_name not in

('Seq _ T_DETAIL ');

120361 rows inserted

SQL> commit;

Commit complete

SQL> exec dbms_stats.gather_table_stats (user, 't_detail ', cascade => true );

PL/SQL procedure successfully completed

SQL> select table_name, partition_name, high_value, num_rows from dba_tab_partitions where table_owner = 'sys 'and table_name in ('t _ DETAIL ');

TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS

---------------------------------------------------------------------------

T_DETAIL P0 67992

T_DETAIL P1 1 75634

T_DETAIL P3 97096

So what is the significance of Reference Partition in actual O & M scenarios?

After my previous discussions, the starting point of Partition technology is nothing more than performance and management. The most typical examples of performance are partition pruning and local indexing. The most typical examples of management are partition removal and Swip partition archiving. In the early days, Partition was aimed at many performance factors. In recent years, with the improvement of hardware performance and management advantages, more and more attention has been paid to it.

In the next article, we will discuss the role of Reference Partition from the performance and management perspectives.