Shulou(Shulou.com)06/01 Report--

Reference partitions (reference partitioning) are a new feature of Oracle Database 11g Release 1 and above. It deals with parent / child peer partitions. In other words, the child table should be partitioned in some way so that each child table partition has an one-to-one relationship with a partition of your table. This is important in some cases, such as if you have a data warehouse, you want to keep a certain amount of data online (such as ORDER information for the last 5 years), and make sure that the associated sub-table data (ORDER_LINE_ITEMS data) is also online. In this classic example, the ORDERS table usually has an ORDER_DATE column, so it can be easily partitioned by month, which also helps to keep the data online for the last five years. Over time, simply load the next friend's partition and delete the oldest partition. However, when you consider the ORDER_LINE_ITEMS table, you see that there is a problem. It has no ORDER_DATE column, and there is no column in the ORDER_LINE_ ITEMS table that can be partitioned, so it cannot help clear old information or load new information.

In the past, before reference partitions appeared, developers had to denormalize the data by copying the ORDER_DATE attribute from the parent table ORDERS to the child table ORDER_LINE_ITEMS. This introduces redundant data, which in turn leads to a series of common problems with data redundancy, such as increased storage overhead, increased data loading resources, cascading update issues (if you modify the parent table, you must also ensure that all copies of the parent table data are updated), and so on. In addition, if foreign key constraints are enabled in the database (and should indeed be enabled), you will find that the original partition in the parent table cannot be truncated or deleted. For example, let's create traditional ORDERS and ORDER_LINE_ ITEMS tables. Take a look at the ORDERS watch first:

Zx@ORCL > create table orders 2 (3 order# number primary key, 4 order_date date NOT NULL, 5 data varchar2 (30) 6) 7 enable row movement 8 PARTITION BY RANGE (order_date) 9 (10 PARTITION part_2016 VALUES LESS THAN (to_date)), 11 PARTITION part_2017 VALUES LESS THAN (to_date ('01-01-2018') 'dd-mm-yyyy')) 12) 13 / Table created.zx@ORCL > insert into orders values 2 (1, to_date (' 01 to_date), 'dd-mon-yyyy'),' xxx') 1 row created.zx@ORCL > insert into orders values 2 (2, to_date ('01 dd-mon-yyyy'), 'xxx'); 1 row created.zx@ORCL > commit;Commit complete.

Now let's create the ORDER_LINE_ ITEMS table and insert some data to point to the ORDERS table:

Zx@ORCL > create table order_line_items 2 (3 order# number, 4 line# number, 5 order_date date,-- manually copied from ORDERS! 6 data varchar2 (30), 7 constraint c1_pk primary key (order#,line#) 8 constraint c1_fk_p foreign key (order#) references orders 9) 10 enable row movement 11 PARTITION BY RANGE (order_date) 12 (13 PARTITION part_2016 VALUES LESS THAN (to_date ('01-01-2017)), 14 PARTITION part_2017 VALUES LESS THAN (to_date ('01-01-2018)) 16 / Table created.zx@ORCL > insert into order_line_items values 2 (1,1) To_date ('01 judicial 2016, 'dd-mon-yyyy'),' yyy') 1 row created.zx@ORCL > insert into order_line_items values 2 (2, 1, to_date ('01 dd-mon-yyyy'), 'yyy'); 1 row created.zx@ORCL > commit;Commit complete.

Now if you want to delete the ORDER_LINE_ITEMS partition containing 2016 data, you can also delete the corresponding ORDERS partition for 2016 without violating referential integrity constraints. Although we all know this, the database does not know:

Zx@ORCL > alter table order_line_items drop partition part_2016;Table altered.zx@ORCL > alter table orders drop partition part_2016;alter table orders drop partition part_2016*ERROR at line 1:ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Therefore, the work of data de-normalization is clumsy, resource-consuming, and may destroy the integrity of the data. Not only that, it also interferes with the work that often needs to be done when managing partition tables: purging old information.

Let's take a look at the reference partition. With reference partitioning, the child table inherits the partitioning mechanism of the parent table without having to de-normalize the partitioning key, and more importantly, it lets the database know that there is peer-to-peer partitioning between the child table and the parent table. That is, when a businessman's child table partition is truncated or deleted, the parent table partition can also be deleted or deleted.

To re-implement the previous example, the syntax is simple, as shown below. Here you will reuse your table ORDERS, which is now Hu, by truncating it:

Zx@ORCL > drop table order_line_items cascade constraints;Table dropped.zx@ORCL > truncate table orders;Table truncated.zx@ORCL > insert into orders values 2 (1, to_date, 'dd-mon-yyyy'),' xxx'); 1 row created.zx@ORCL > insert into orders values 2 (2, to_date), 'xxx'); 1 row created.zx@ORCL > commit;Commit complete.

Create a new child table:

Zx@ORCL > create table order_line_items 2 (3 order# number NOT NULL, 4 line# number NOT NULL, 5 data varchar2 (30), 6 constraint c1_pk primary key (order#,line#), 7 constraint c1_fk_p foreign key (order#) references orders 8) 9 enable row movement 10 partition by reference (c1_fk_p) 11 / Table created.zx@ORCL > insert into order_line_items values 2 (1,1, 'yyy') 1 row created.zx@ORCL > insert into order_line_items values 2 (2, 1, 'yyy'), 1 row created.zx@ORCL > commit;Commit complete.

The magic is on line 10 of the CREATE TABLE statement. Here, we replace the interval partition statement with PARTITION BY REFERENCE.

This allows us to discover the partitioning mechanism by specifying which foreign key constraints to use. Here you can see that the foreign key points to the ORDERS table-- the database reads the structure of the ORDERS table and finds that it has two partitions. Therefore, the child table will have two partitions. In fact, if you query the data dictionary now, you can get:

Zx@ORCL > set linesize 200zx@ORCL > col table for a20zx@ORCL > col partition_name for a20zx@ORCL > select table_name, partition_name 2 from user_tab_partitions 3 where table_name in ('ORDERS',' ORDER_LINE_ITEMS') 4 order by table_name Partition_name 5 / TABLE_NAME PARTITION_NAME -ORDERS PART_2016ORDERS PART_2017ORDER_LINE_ITEMS PART_2016ORDER_LINE_ITEMS PART_2017

You can see that the two tables have exactly the same structure. In addition, because the database knows that the two tables are related, you can delete the parent table partition and have it automatically clear the relevant child table partition (because the child table inherits from the parent table). So any adjustments to the parent table partition structure are passed to the lower cascade to the child table partition:

Zx@ORCL > alter table orders drop partition part_2016 update global indexes Table altered.zx@ORCL > select table_name, partition_name 2 from user_tab_partitions 3 where table_name in ('ORDERS',' ORDER_LINE_ITEMS') 4 order by table_name Partition_name 5 / TABLE_NAME PARTITION_NAME -ORDERS PART_2017ORDER_LINE_ITEMS PART_2017

Therefore, DROP that was previously not allowed to complete is now fully allowed, and it is automatically cascaded to the child table. In addition, if you use ADD to add a partition:

Zx@ORCL > alter table orders add partition 2 part_2018 values less than 3 (to_date ('01-01-2019, 'dd-mm-yyyy')) Table altered.zx@ORCL > select table_name, partition_name 2 from user_tab_partitions 3 where table_name in ('ORDERS',' ORDER_LINE_ITEMS') 4 order by table_name Partition_name 5 / TABLE_NAME PARTITION_NAME -ORDERS PART_2017ORDERS PART_2018ORDER_LINE_ITEMS PART_2017ORDER_LINE_ITEMS PART_2018

As you can see, this operation is also passed to the lower cascading. There is an one-to-one relationship between parent and child tables.

Refer to the Art of ORACLE DATABASE 9I10G11G programming

Official document: http://docs.oracle.com/cd/E11882_01/server.112/e25523/partition.htm#CACIHDII

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.