Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the function of Oracle 11g citation partition table". The explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what is the role of Oracle 11g citation partition table"?

Reference partition tables are a new feature introduced by Oracle11g and are very useful in specific situations, solving the problem that not all tables have columns that you need to partition.

According to the official Oracle documentation, the reference partition table is implemented through the PARTITION BY REFERENCE statement, and you need to specify the name of the reference constraint, which will become the constraint applied to the reference partition table. This constraint must be enabled and enforced.

Like other partition tables, you can set object-level default properties, or you can set properties in specific partitions to override object-level properties.

The following sql statement creates a range partition table orders. The reference partition child table order_items contains four partitions, Q1 partition 2005, Q2 partition 2005, Q3 partition 2005, and Q4 partition 2005. The order_items data contained in each partition is stored according to the range partition of the column associated with the parent table.

Click (here) to collapse or open

CREATE TABLE orders

(order_id NUMBER (12)

Order_date TIMESTAMP

Order_mode VARCHAR2 (8)

Customer_id NUMBER (6)

Order_status NUMBER (2)

Order_total NUMBER (8 dint 2)

Sales_rep_id NUMBER (6)

Promotion_id NUMBER (6)

CONSTRAINT orders_pk PRIMARY KEY (order_id)

)

PARTITION BY RANGE (order_date)

(PARTITION Q1 2005 VALUES LESS THAN (TO_DATE ('01MurAPRMI 2005'))

PARTITION Q2q2005 VALUES LESS THAN (TO_DATE ('01murJULMI 2005))

PARTITION Q3, 2005 VALUES LESS THAN (TO_DATE (01murOct, 2005))

PARTITION Q4001 2005 VALUES LESS THAN (TO_DATE ('01MurJANMI 2006))

);

CREATE TABLE order_items

(order_id NUMBER (12) NOT NULL

Line_item_id NUMBER (3) NOT NULL

Product_id NUMBER (6) NOT NULL

Unit_price NUMBER (8 dint 2)

Quantity NUMBER (8)

CONSTRAINT order_items_fk

FOREIGN KEY (order_id) REFERENCES orders (order_id)

)

PARTITION BY REFERENCE (order_items_fk)

In the above sql statement, the reference partition child table does not specify a partition description, which is consistent with the parent table by default; if you want to specify a partition description, the number of partition descriptions must be the same as the number of partitions of the parent table. If the parent table is a compound partitioned table, the partition of the child table corresponds to the child partition of the parent table; otherwise, the partition of the child table corresponds to the partition of the parent table one by one.

It is important to note that the boundary of the partition cannot be specified in the reference partition table.

A partition that references a partition table can be named separately, or if the partition name is not explicitly defined, it directly inherits its associated parent table partition name, unless the parent table partition name conflicts with the existing name. In this case, a partition name is automatically generated. Since the above sql statement does not specify a name, the child table partition and the parent table partition should have the same name.

Click (here) to collapse or open

SQL > select table_name,partition_name from user_tab_partitions

TABLE_NAME PARTITION_NAME

ORDER_ITEMS Q4_2005

ORDER_ITEMS Q3_2005

ORDER_ITEMS Q2_2005

ORDER_ITEMS Q1_2005

ORDERS Q4_2005

ORDERS Q3_2005

ORDERS Q2_2005

ORDERS Q1_2005

8 rows selected.

SQL >

If the sql statement does not explicitly specify the tablespace, the partition that references the partitioned table will be placed with the related partition of the parent table.

Thank you for your reading, the above is the content of "what is the use of Oracle 11g reference partition table". After the study of this article, I believe you have a deeper understanding of the role of Oracle 11g reference partition table, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.