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

1. Reference partition table

Make the child table partition using the partitioning condition of the parent table without adding additional columns for partitioning in the child table, resulting in data de-normalization, space waste and other problems.

Reference example:

(1)。 Create a master table

CREATE TABLE orders

(

Order# NUMBER PRIMARY KEY

Order_date DATE NOT NULL

Data VARCHAR2 (30)

)

PARTITION BY RANGE

(order_date)

(

PARTITION

Part_2016 VALUES LESS THAN (TO_DATE ('01-01-2017, 'dd-mm-yyyy'))

PARTITION

Part_2017 VALUES LESS THAN (TO_DATE ('01-01-2018, 'dd-mm-yyyy')

/

Insert into orders values (1, to_date ('01-01-2017, 'dd-mm-yyyy'),' xxx')

Insert into orders values (2, to_date ('01-01-2016, 'dd-mm-yyyy'),' yyy')

Commit

(2)。 Create a child table

Create table order_line_items

(

Order# number NOT NULL

Line# number NOT NULL

Data varchar2 (30)

Constraint c1_pk primary key (order#,line#)

Constraint c1_fk_p foreign key (order#) references orders

)

Enable row movement

Partition by reference (c1_fk_p)

/

Insert into order_line_items values (1,1, 'yyy')

Insert into order_line_items values (2,1, 'yyy')

SELECT table_name, partition_name

FROM user_tab_partitions

WHERE table_name IN ('ORDERS',' ORDER_LINE_ITEMS')

ORDER BY table_name, partition_name

TABLE_NAME PARTITION_NAME

--

ORDERS PART_2016

ORDERS PART_2017

ORDER_LINE_ITEMS PART_2016

ORDER_LINE_ITEMS PART_2017

(3)。 Delete parent table partition. You can cascade delete child table partitions.

Alter table orders drop partition part_2016 update global indexes

TABLE_NAME PARTITION_NAME

--

ORDERS PART_2017

ORDER_LINE_ITEMS PART_2017

(4)。 Add a parent table partition to cascade add child table partitions

Alter table orders add partition part_2018 values less than (to_date ('01-01-2019, 'dd-mm-yyyy'))

TABLE_NAME PARTITION_NAME

--

ORDERS PART_2017

ORDERS PART_2018

ORDER_LINE_ITEMS PART_2017

ORDER_LINE_ITEMS PART_2018

(5)。 Delete child table partition, error reported

Alter table order_line_items drop partition PART_2017 update global indexes

ORA-14255: table is not partitioned by Range, List, Composite Range or Composite List method

(6). Truncate subtable partition, feasible.

Alter table admin.order_line_items truncate partition PART_2017

two。 Interval partition table

Define partition rules that automatically create partitions when eligible data is inserted.

You can use the alter command to change an existing interval partition table to an interval partition, or you can use create to create an interval partition.

Characteristics of interval partition

-derived from the range partition

-create partitions with fixed length and width (such as year, month, specific numbers (e.g. 100, 500, etc.))

-the partition field must be of type number or date

-at least one range partition (permanent partition) must be specified

-when records are inserted, the system automatically creates new partitions and local indexes as needed

-existing range partitions can be converted to spaced partitions (via the ALTER TABLE SET INTERVAL option)

-Interval Partitioning does not support indexing and organizing tables

-domain index cannot be created on the Interval Partitioning table

Reference example:

-create an interval partition table

CREATE TABLE admin.orders2

(

Order# NUMBER PRIMARY KEY

Order_date DATE NOT NULL

)

PARTITION BY RANGE

(order_date)

INTERVAL (NUMTOYMINTERVAL (1, 'month'))

(

PARTITION

P201612 VALUES LESS THAN (TO_DATE ('01-01-2017 VALUES LESS THAN, 'dd-mm-yyyy'))

-insert data

Insert into admin.orders2 values ('01-12-2016 ('dd-mm-yyyy'))

Insert into admin.orders2 values (2 classic date ('02-01-2017 dd-mm-yyyy'))

Insert into admin.orders2 values (3 classic date ('02-02-2017 dd-mm-yyyy'))

View automatically generated partitions

SELECT table_name, partition_name

FROM user_tab_partitions

WHERE table_name IN ('ORDERS2')

ORDER BY table_name, partition_name

TABLE_NAME PARTITION_NAME

--

ORDERS2 P201612

ORDERS2 SYS_P121

ORDERS2 SYS_P122

-the partitions generated by the deleted data remain unchanged

TABLE_NAME PARTITION_NAME

--

ORDERS2 P201612

ORDERS2 SYS_P121

ORDERS2 SYS_P122

-change the original range partition table to interval partition table

ALTER TABLE admin.orders SET INTERVAL (1000000)

ALTER TABLE admin.orders SET INTERVAL (NUMTODSINTERVAL);-- the units commonly used in NUMTODSINTERVAL are ('day','hour','minute','second').

ALTER TABLE admin.orders SET INTERVAL (numtoyminterval);-- the common units of numtoyminterval are 'year','month''.

-convert interval partition table to range partition table

ALTER TABLE admin.orders3 SET INTERVAL ()

3. Virtual column partition

The following is from https://www.cnblogs.com/moonandstar08/p/5100567.html

(1)。 Virtual columns can only be created on the heap organization table (normal table)

(2)。 Virtual column cannot be of type LOB or RAW

(3)。 The value of the virtual column is not real. Only when it is used, the value of the virtual column is calculated according to the expression and is not stored on disk.

(4)。 Virtual columns can be used as partitioning keywords to build partitioned tables, which is another new feature of ORACLE 11g-virtual column partitioning.

Create a partitioned table with virtual columns:

Create table test (N1 number, C1 varchar2 (80), N2 number generated always as (N1 / 0.8))-create a partition with virtual columns

Create table test1 (N1 number

C1 varchar2 (80)

V1 varchar2 (2) generated always as (substr (C1 meme 1))

)

Partition by list (v1)

(partition v11 values ('I')

Partition v12 values ('O')

Partition v13 values ('E')

Partition v15 values (default)

);

Query: select * from test1 partition (v11)

(5)。 Indexes can be built on virtual columns

Create index inx_test on test1 (v1)

(6)。 If you add a virtual column to a table that has been created, if you do not specify the field type of the virtual column

ORACLE automatically sets the type of this field based on the result of the evaluation of the expression after generated always as.

(7)。 The value of the virtual column is automatically calculated by ORACLE according to the expression. You can not do UPDATE and INSERT operations, but can do DELETE operations on the virtual column.

(8)。 All columns in the expression must be in the same table

(9)。 Expressions cannot use other virtual columns

4. System partition table

-the system automatically generates partitions without partition conditions

-data has nothing to do with partitions

-local index cannot be established

-specify the partition when inserting data

CREATE TABLE admin.order4

(

Col1 NUMBER

Name VARCHAR2 (100)

)

PARTITION BY SYSTEM

(PARTITION p1

PARTITION p2

PARTITION p3

PARTITION p4)

Insert into admin.order4 partition (p2) select col1,name from admin.order4

5. Fully combined partition

(1)。 Range Partition (range)

(2)。 Hash partition (hash)

(3)。 List partition (list)

(4)。 Range-Hash compound partition (range-hash)

(5)。 Range-list compound partition (range-list)

New to oracle11g

Range-range,list-range,list-list,list-hash

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.