• Understand the meaning and usage of table partition

• Master the traditional (10g and before) table partitioning method

• Master the table partition mode of 11g

• Allows users to divide a table into multiple partitions

• Users can execute queries and access only specific partitions in the table

• Different partitions are stored on different disks to improve access performance and security

• Each partition can be backed up and restored independently

Traditional table partition type

[external link image transfer failed. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-gI55sbWn-1624706273362)(F:\Oracle OCA.assets\image-20210626082124840.png)]

Range partition

Partition with a range of values for a column or group of columns in the table

PARTITION BY RANGE (column_name)
(
  PARTITION part1 VALUE LESS THAN(range1),
  PARTITION part2 VALUE LESS THAN(range2),
  ...
  [PARTITION partN VALUE LESS THAN(MAXVALUE)]
);

View partition information

Hash partitioning

• Allows users to partition data that does not have a logical range
• Determine the partition of the storage by executing the HASH function on the partition key
• Distribute data evenly across different partitions

PARTITION BY HASH (column_name)
PARTITIONS number_of_partitions;
or
PARTITION BY HASH (column_name)
( PARTITION part1 [TABLESPACE tbs1],
  PARTITION part2 [TABLESPACE tbs2],
  ...
  PARTITION partN [TABLESPACE tbsN]);

List partition

Allows users to organize irrelevant data together

PARTITION BY LIST (column_name)
(
  PARTITION part1 VALUES (values_list1),
  PARTITION part2 VALUES (values_list2),
  ...
  PARTITION partN VALUES (DEFAULT)
);

Composite partition

A combination of a range partition and a hash partition or a list partition

PARTITION BY RANGE (column_name1)
SUBPARTITION BY HASH (column_name2)
SUBPARTITIONS number_of_partitions
(
  PARTITION part1 VALUE LESS THAN(range1),
  PARTITION part2 VALUE LESS THAN(range2),
  ...
  PARTITION partN VALUE LESS THAN(MAXVALUE)
);

Twelve small partitions

Reference partition

Reference partition: Based on the partition method of the parent table referenced by the foreign key, it depends on the relationship between the existing parent table and the child table. The child table is associated with the parent table through the foreign key, and then inherits the partition method of the parent table without creating it. The child table also inherits the maintenance operation of the parent table.

1. The primary table is a range partition, and the child table is a reference partition
2. The primary table is a list partition, and the child table is a reference partition
3. The primary table is a hash partition, and the child table is a reference partition

Interval partition

Interval partition: the range partition can be created completely automatically according to the interval threshold, which is an extension of the range partition.

It is widely used in data warehouse.

Virtual column based partitioning

Partition based on virtual column: build the partition on a virtual column, that is, on the calculation result of function or expression to complete a certain task.

system partition

System partition: the partition column is not specified. ORACLE controls and manages the partition. It has no boundaries of range partition or list partition.

Manipulate partitioned tables

• Inserting data into a partitioned table is exactly the same as an ordinary table. Oracle will automatically save the data to the corresponding partition
• When querying, modifying, and deleting partitioned tables, you can explicitly specify the partitions to operate on

INSERT INTO SALES3 VALUES ('P001', '02-3 month-2001', 2000);
INSERT INTO SALES3 VALUES ('P002', '10-5 month-2001', 2508);
INSERT INTO SALES3 VALUES ('P003', '05-7 month-2001', 780);
INSERT INTO SALES3 VALUES ('P004', '12-9 month-2001', 1080);

SELECT * FROM SALES3 PARTITION (P3);

DELETE FROM SALES3 PARTITION (P2);

Partition maintenance operation

• The partition maintenance operation modifies the partition of the partitioned table.

• Type of Zone Maintenance:

  • Scheduled events - periodically delete the oldest partitions
  • Unscheduled events - resolve application or system problems

• Partition maintenance operations include:

  • Add partition

    Add partition – adds a new partition after the last partition

    ALTER TABLE SALES ADD PARTITION P4 VALUES LESS THAN (4000);
    

  • delete a partition

    Delete partition – when a specified partition is deleted, the data of the partition is also deleted

    ALTER TABLE SALES DROP PARTITION P4;
    

  • Truncate partition

    Truncate partition – deletes all records in the specified partition

    ALTER TABLE SALES TRUNCATE PARTITION P3;
    

  • Merge partitions

    Merge zone - connects two adjacent zones of a range zone or composite zone

    ALTER TABLE SALES MERGE PARTITIONS S1, S2 INTO PARTITION S2;
    

  • Split partition

    Split partition - split records in one large partition into two partitions

    ALTER TABLE SALES SPLIT PARTITION P2 AT (1500) INTO (PARTITION P21, PARTITION P22);