Oracle_ Advanced Features (6) partitioning

Oracle Partition Table
1. Partition table:
When the amount of data in the table is increasing, the query data slows down and the performance of the application degrades, so you should consider partitioning the table.
After the table is partitioned, the logical table is still a complete table, but the data in the table is physically stored in multiple table spaces (physical files),
This will not scan the entire table every time you query the data.

2. The specific role of table partitioning
Oracle's table partitioning capabilities bring great benefits to a wide variety of applications by improving manageability, performance, and availability.
In general, partitioning can greatly improve the performance of certain queries and maintenance operations.
In addition, partitioning can greatly simplify common administrative tasks, and partitioning is a key tool for building gigabytes of data systems or ultra-high availability systems.
Each partition has its own name, and you can choose your own storage features.
From the database administrator's point of view, a partitioned object has multiple segments that can be collectively managed or managed separately,
This enables the database administrator to have considerable flexibility in managing the objects after partitioning.
However, from the application's point of view, the partitioned table is exactly the same as the non-partitioned table, and no modifications are required to access the partitioned table using the SQL DML command.

When do I use a partitioned table?
1, the size of the table is more than 2GB.
2. The table contains historical data, and new data is added to the new partition.

3. Advantages and disadvantages of table partitioning
Advantages:
Improve query performance: Queries on partitioned objects can search only the partitions you care about and improve the retrieval speed.
Enhanced availability: If one partition of the table fails, the data for the table on the other partitions is still available;
Easy maintenance: If a partition of the table fails, the data needs to be repaired, only the partition can be repaired;
Balanced I/O: You can map different partitions to disk to balance I/O and improve overall system performance.
Disadvantages:
Partition Table Related: Existing tables have no methods that can be converted directly into partitioned tables.

4. Table partitioning types and how to do it
4.1 range Partitioning: The
Range partition maps the data to each partition based on the scope, which is determined by the partition key specified when the partition was created.
When using range partitioning, consider the following rules:
Each partition must have a values less then clause that specifies an upper value that is not included in the partition.
all partitions, except the first one, have an implicit lower value, which is the upper limit of the previous partition of this partition.
in the highest partition, MaxValue is defined. The MaxValue represents an indeterminate value.
This value is higher than the value of any partition key in the other partition, or it can be understood to be higher than the value less or less that is specified in any partition, including null values.
Example 1:
assumes that there is a customer table with 200 rows of data, partitioning the table through C_ID, storing 100 rows per partition,
and saving each partition in a separate tablespace so that the data file can span multiple physical disks.
CREATE TABLE Part_range1
(
c_id number primary key,
name Varchar2 (),
phone varchar2 (+),
Email VARCHAR2,
Status varchar2 (1)
)
Partition by Range (C_ID)
(
partition Cus_part1 values less than ( 101) tablespace USERS,
partition Cus_part2 values less than (201) tablespace ts_find
);

Insert into Part_range1 (c_id,name) VALUES (1, ' name ');
Insert into Part_range1 (c_id,name) VALUES ("name");
Insert into Part_range1 (C_id,name) VALUES (101, ' name ');
Insert into Part_range1 (C_id,name) VALUES (+, ' name ');

SELECT * from Part_range1;
SELECT * from Part_range1 partition (CUS_PART1);
SELECT * from Part_range1 partition (CUS_PART2);

Insert into Part_range1 (c_id,name) VALUES (201, ' name ');
Add Partitions to Part_range1:
ALTER TABLE PART_RANGE1 add partition CUS_PART3 values less than (301) tablespace Ts_find;

Example 2: Using MaxValue extension Example 1.
CREATE TABLE Part_range2
(
C_ID Number primary Key,
Name VARCHAR2 (30),
Phone VARCHAR2 (15),
Email VARCHAR2 (80),
Status Varchar2 (1)
)
Partition by range (C_ID)
(
Partition Cus_part1 values less than (101) tablespace USERS,
Partition Cus_part2 values less than (201) tablespace Ts_find,
Partition CUS_PART3 values less than (MaxValue) tablespace ts_find
);

Insert into Part_range2 (c_id,name) VALUES (201, ' name ');
Insert into Part_range2 (C_id,name) VALUES (9999, ' name ');
SELECT * from Part_range2;
SELECT * from Part_range2 partition (CUS_PART3);
Note: The range partition table will not be able to increase the partition after using MaxValue.

Example 3: Sales Order table, partitioned by time range
CREATE TABLE Part_range3
(
ORDER_ID Number (7) Not null primary key,
Order_date date,
Total_amount number,
CUSTOTMER_ID Number (7)
)
Partition by range (order_date)
(
Partition month01 values less than (to_date (' 2017-2-1 ', ' yyyy-mm-dd ') tablespace users,
Partition month02 values less than (to_date (' 2017-3-1 ', ' yyyy-mm-dd ') tablespace ts_find,
Partition month03 values less than (to_date (' 2017-4-1 ', ' yyyy-mm-dd ') tablespace ts_find
);

Partition by range (order_date)
(
Partition YEAR15 values less than (to_date (' 2016-1-1 ', ' yyyy-mm-dd ') tablespace users,
Partition year16 values less than (to_date (' 2017-1-1 ', ' yyyy-mm-dd ') tablespace ts_find,
Partition Year17 values less than (to_date (' 2018-1-1 ', ' yyyy-mm-dd ') tablespace ts_find
)；
4.2 List partition:
This partition is characterized by partitioning the value of a column based on a particular value.
Example 1: The problem complaint form, according to the problem state partition, the status value: ' Active ', ' inactive '
CREATE TABLE Part_list1
(
PROBLEM_ID Number (7) Not null primary key,
Description VARCHAR2 (2000),
CUSTOMER_ID Number (7),
date_entered date,
Status Varchar2 (20)
)
Partition by list (status)
(
Partition active values (' active ') tablespace users,
Partition inactive values (' inactive ') tablespace ts_find
);
--Test
Insert into Part_list1 (problem_id,status) VALUES (one, ' active ');
Insert into Part_list1 (problem_id,status) VALUES ("inactive");
SELECT * from Part_list1;
SELECT * from PART_LIST1 partition (active);
SELECT * from Part_list1 partition (inactive);

Example 2: Population in Beijing, divided by administrative divisions
CREATE TABLE Part_list2
(
ID Number (8) primary key,
Name varchar (20),
Area varchar (10)
)
Partition by list (area)
(
Partition AREA01 values (' East Side ', ' West Side ') tablespace users,
Partition AREA02 values (' Haidian ', ' changping ') tablespace ts_find
);

Example 3: Partitioning by remainder--not supported
CREATE TABLE Part_list3
(
ID Number (8) NOT null primary key,
Des VARCHAR2 (2000)
)
Partition by List (mod (id,3))
(
Partition P1 values (0) tablespace users,
Partition P2 values (1) tablespace Ts_find,
Partition P3 VALUES (2) tablespace Ts_find
);

4.3 Hash partition:
Use the hash algorithm on the column values to determine which partition to put the rows in.
Hash partitioning is recommended when the value of the column does not have an appropriate condition.
A hash partition is a type of partition that distributes data evenly by specifying the partition number
(The number of partitions should be 2 of the secondary, otherwise uneven distribution).
The main mechanism of hash partitioning is to calculate which partition a particular record should be inserted into according to the hash algorithm.

Example 1:2 partitions
CREATE TABLE Part_hash1
(
Col Number (8),
INF VARCHAR2 (100)
)
Partition by hash (COL)
(
Partition P1 tablespace users,
Partition P2 tablespace ts_find
);

INSERT into PART_HASH1 values (1, ' B ');
INSERT into PART_HASH1 values (2, ' B ');
INSERT into PART_HASH1 values (3, ' B ');
INSERT into PART_HASH1 values (4, ' B ');
INSERT into PART_HASH1 values (5, ' B ');
INSERT into PART_HASH1 values (6, ' B ');
INSERT into PART_HASH1 values (7, ' B ');
INSERT into PART_HASH1 values (8, ' B ');
SELECT * from Part_hash1;
SELECT * FROM PART_HASH1 partition (P1);
SELECT * FROM PART_HASH1 partition (P2);

Example 2: A simple notation that specifies the number of partitions, the partition name is specified by the system
CREATE TABLE Part_hash2
(
Empno Number (4),
ename VARCHAR2 (30),
Sal number
)
Partition by hash (EMPNO) Partitions 4
Store in (Users,ts_find);

--View the partition name specified by the system
SELECT * from user_tab_partitions where table_name= ' part_hash2 ';
--Copying data
Insert INTO PART_HASH2 select Empno,ename,sal from emp;
SELECT * from Part_hash2;
SELECT * from PART_HASH2 partition (SYS_P42);

Benefits:
The partition itself does not need to be partitioned on a regular basis (scope partitions and list partitions need to create new partitions on a regular basis for newly added values)
You can eliminate access to hotspot blocks and indexed hotspot blocks, because the index is a sorted structure, and for a column of self-increment columns to join the range partition,
It is possible to make frequent data insertions to high-level blocks of the index, resulting in frequent writes and splits.
For such an index to be eliminated if the hash partition index is added.
Limit:
Partitions cannot be too many, typically about 1000 partitions, and may be slower in parallel access operations where partition firings (predicates cause index range scanning)
Because there are very many additional partition maintenance operations.
Fields that are scanned for long-term use are not suitable for hash partitions because they cause multiple partitions to be scanned, and for fields that are frequently unique to scan, it is appropriate to establish a hash partition.

4.4 Combined Range list partition: (range + list partition)
This partition is based on a range partition and a list partition, and the table is first scoped by a column,
Then the list is partitioned by a column, and the partitions in the partition are called sub-partitions.
CREATE TABLE Part_range_list
(
product_id VARCHAR2 (5),
Sales_date date,
Sales_cost Number (10),
Status Varchar2 (20)
)
Partition by Range (sales_date) Subpartition by list (status)
(
Partition P1 values less than (to_date (' 2017-02-01 ', ' yyyy-mm-dd ') tablespace users
(
Subpartition p1sub1 values (' active ') tablespace users,
Subpartition p1sub2 values (' inactive ') tablespace users
),
Partition P2 values less than (to_date (' 2017-03-01 ', ' yyyy-mm-dd ') tablespace ts_find
(
Subpartition p2sub1 values (' active ') tablespace Ts_find,
Subpartition p2sub2 values (' inactive ') tablespace ts_find
)
);
Insert into Part_range_list (product_id,sales_date,status) VALUES (1,sysdate-480, ' active ');
Insert into Part_range_list (product_id,sales_date,status) VALUES (2,sysdate-481, ' inactive ');
Insert into Part_range_list (product_id,sales_date,status) VALUES (3,sysdate-451, ' active ');
Insert into Part_range_list (product_id,sales_date,status) VALUES (4,sysdate-450, ' inactive ');

SELECT * from Part_range_list;
SELECT * FROM Part_range_list partition (P1);
SELECT * FROM Part_range_list partition (P2);
SELECT * from Part_range_list subpartition (P1SUB1);
SELECT * from Part_range_list subpartition (P1SUB2);
SELECT * from Part_range_list subpartition (P2SUB1);
SELECT * from Part_range_list subpartition (P2SUB2);

4.5 Compound-scoped Hash Partition: (range + hash partition)
This partition is based on a range partition and a hash partition, and the
table is first scoped by a column and then hashed by a column.
CREATE TABLE Part_range_hash
(
transaction_id number primary key,
item_id number (8) NOT NULL,
Item_des Cription varchar2 (+),
Transaction_date date
)
partition by Range (transaction_date) subpartition by hash ( transaction_id)
Subpartitions 2 store in (Users,ts_find)
(
partition part_01 values less than (to_date (' 2017- 01-01 ', ' yyyy-mm-dd '),
partition part_02 values less than (to_date (' 2018-01-01 ', ' yyyy-mm-dd ')
);

5. Partition-related data dictionary
--query all partition tables
SELECT * from User_tables a where a.partitioned= ' YES '
--Display partition table information:
SELECT * FROM DB A_part_tables;
Select * from All_part_tables;
Select * from User_part_tables;
--Display partition information:
SELECT * FROM Dba_tab_partitions
Select * FROM All_tab_partitions
Select * from User_tab_partiti ONS
--Display sub-partition information
SELECT * FROM Dba_tab_subpartitions
Select * FROM All_tab_subpartitions
Select * from User_ta B_subpartitions
--Display partition column information:
SELECT * FROM Dba_part_key_columns
Select * FROM All_part_key_columns
SELECT * From User_part_key_columns
--Displays sub-partition column information:
SELECT * FROM Dba_subpart_key_columns
Select * from All_subpart_key_c Olumns
Select * from User_subpart_key_columns

6. Table Partitioning related operations:
6.1 Adding partitions
Add Partitions to Part_range1:
ALTER TABLE PART_RANGE1 add partition CUS_PART3 values less than (301) tablespace Ts_find;
Insert into Part_range1 (customer_id,name) VALUES (201, ' name ');
Note: The above partition boundaries should be added above the last partition bounds.
Add Partition to Part_range2: MaxValue
ALTER TABLE PART_RANGE2 add partition CUS_PART4 values less than (301) tablespace Ts_find;
--ora-14074: The partition bounds must be adjusted above the last partition boundary

Add partition Part_range_list to the combined range list partition
ALTER TABLE part_range_list ADD partition p3
Values less than (to_date (' 2017-4-1 ', ' yyyy-mm-dd ')) tablespace ts_find;

--View
Select * from User_part_tables where table_name= ' part_range_list ';
SELECT * from user_tab_partitions where table_name= ' part_range_list '; --a sub-partition is generated by default
SELECT * from user_tab_subpartitions where table_name= ' part_range_list ';--sub-partition name: SYS_SUBP65
Select * FROM User_part_key_columns where name= ' part_range_list ';
Select * from User_subpart_key_columns where name= ' part_range_list '; The
check found that P3 has a child partition and that the key value of the sub-partition is default, interpolation data validation:
INSERT INTO part_range_list (product_id,sales_date,sales_cost,status) VALUES (111,to_date (' 2017-03-31 ', ' Yyyy-mm-dd '), 1, ' active ');
INSERT INTO part_range_list (product_id,sales_date,sales_cost,status) VALUES (112,to_date (' 2017-03-31 ', ' Yyyy-mm-dd '), 1, ' inactive ');
Select * from Part_range_list;
SELECT * FROM Part_range_list partition (p3);
Select * from Part_range_list subpartition (sys_subp49);

6.2 Adding sub-partitions
Part_range_list the P3 partition of the table adds a sub-partition (requires: Consistent with the key value of the P1 sub-partition)
ALTER TABLE Part_range_list Modify partition p3 add subpartition p3sub1 values (' active ');
ALTER TABLE Part_range_list Modify partition p3 add subpartition p3sub2 values (' inactive ');
--ora-14621: Cannot add a child partition when the DEFAULT child partition already exists

6.3 Delete (DEFAULT) sub-partition:
ALTER TABLE part_range_list drop subpartition sys_subp49;
--ora-14629: Cannot delete unique sub-partitions in a partition
6.4 Deleting a partition when it cannot delete the only child partition in a partition
ALTER TABLE part_range_list drop partition p3;

6.5 Correct way to add a combined partition: Add a sub-partition when adding a combined partition
ALTER TABLE Part_range_list ADD partition P3 values less than (to_date (' 2017-4-1 ', ' yyyy-mm-dd ')) tablespace Ts_find
(Subpartition p3sub1 values (' active ') tablespace Ts_find,
Subpartition p3sub2 values (' inactive ') tablespace ts_find);
--Verification
Insert into Part_range_list (product_id,sales_date,sales_cost,status)
VALUES (111,to_date (' 2017-03-31 ', ' Yyyy-mm-dd '), 1, ' active ');
Insert into Part_range_list (product_id,sales_date,sales_cost,status)
VALUES (112,to_date (' 2017-03-31 ', ' Yyyy-mm-dd '), 1, ' inactive ');
SELECT * from Part_range_list;
SELECT * FROM Part_range_list partition (p3);
SELECT * from Part_range_list subpartition (P3SUB1);

6.6 Delete sub-partitions
ALTER TABLE part_range_list drop subpartition p3sub1;
--alter table Part_range_list drop subpartition p3sub2;
6.7 Delete Partition:
ALTER TABLE part_range_list drop partition p3;
ALTER TABLE part_range_list drop partition p2;
ALTER TABLE part_range_list drop partition p1;
--ora-14083: Unable to delete unique partition for partitioned table
If the deleted partition is the only partition in the table, then this partition will not be deleted and the table must be deleted if you want to delete the partition.
drop table part_range_list;

6.8 truncate partition
Truncates a partition to delete data from a partition, does not delete the partition, and does not delete data from other partitions.
The partition can be truncated even if there is only one partition in the table.
--then create the partition table Part_range_list and insert the data
INSERT into part_range_list (product_id,sales_date,sales_cost,status) VALUES ( 111,to_date (' 2017-02-21 ', ' Yyyy-mm-dd '), 1, ' active ');
INSERT INTO part_range_list (product_id,sales_date,sales_cost,status) VALUES (112,to_date (' 2017-02-21 ', ' Yyyy-mm-dd '), 1, ' inactive ');
Select * from Part_range_list;
SELECT * FROM Part_range_list partition (P2);
Select * from Part_range_list subpartition (P2SUB1);
Select * from Part_range_list subpartition (P2SUB2);
Truncate sub-partition:
ALTER TABLE part_range_list truncate subpartition p2sub2;
TRUNCATE partition:
ALTER TABLE part_range_list truncate partition p2;

6.9 Merging partitions
Merging partitions is the merging of adjacent partitions into one partition, and the resulting partitions will be bounded by higher partitions.
Note: You cannot merge partitions into lower-bound partitions.
SELECT * from User_part_tables where table_name= ' part_range1 ';
SELECT * from user_tab_partitions where table_name= ' part_range1 ';
SELECT * from User_part_key_columns where name= ' part_range1 ';

SELECT * from Part_range1;
SELECT * from Part_range1 partition (CUS_PART1);
SELECT * from Part_range1 partition (CUS_PART2);
SELECT * from Part_range1 partition (CUS_PART3);
Merge partitions:
ALTER TABLE PART_RANGE1 merge partitions cus_part1,cus_part2 into partition cus_part2;
--alter table Part_range1 Merge partitions cus_part3,cus_part2 into partition cus_part2;
ORA-14273: You must first specify a nether partition
--alter table Part_range1 Merge partitions cus_part2,cus_part3 into partition cus_part2;
ORA-14275: You cannot reuse a nether partition as a result partition

6.10 Split partition
Split partition splits a partition into two new partitions, after which the original partition no longer exists.
ALTER TABLE PART_RANGE1 split partition Cus_part2 at (101) into (partition p1,partition p2);
--Verify
Select * from User_part_tables where table_name= ' part_range1 ';
SELECT * from user_tab_partitions where table_name= ' part_range1 ';
Select * from User_part_key_columns where name= ' part_range1 ';
SELECT * from Part_range1 partition (P1);
SELECT * FROM Part_range1 partition (P2);
SELECT * from Part_range1 partition (CUS_PART3);
Note: You cannot split a hash-type partition.
Select * from User_part_tables where table_name= ' part_hash1 ';
Select * from user_tab_partitions where table_name= ' part_hash1 ';
Select * from User_part_key_columns where name= ' part_hash1 ';
SELECT * FROM PART_HASH1 partition (P2);
ALTER TABLE PART_HASH1 split partition P2 at (4) into (partition p3,partition p4);
--ora-14255: Table is not partitioned by range, list, combined range, or combined list method

6.11 Renaming table Partitions
SELECT * from user_tab_partitions where table_name= ' part_range1 ';
ALTER TABLE PART_RANGE1 Rename partition cus_part3 to P3;

6.12 Partition Table Query
--Partition Table query
SELECT * from Part_range1;
--Single partition query
SELECT * FROM Part_range1 partition (P1);
--cross-partition query
SELECT * FROM
(SELECT * from Part_range1 partition (p1)
UNION ALL
SELECT * FROM Part_range1 partition (p2)
UNION ALL
SELECT * FROM Part_range1 partition (p3));

Oracle_ Advanced Features (6) partitioning