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

This article mainly explains "what is the daily maintenance method of ORACLE partition table". The content of 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 daily maintenance method of ORACLE partition table".

1. Test table preparation

In order to facilitate the demonstration of the specific operation, first prepare a Range test partition table TEST_RANGE_PARTITION.

The test data here comes from the emp table under the oracle test user scott.

-- create partition table TEST_RANGE_PARTITION

-- here, the table structure of emp table is obtained through dbms_metadata.get_ddl and then modified.

SQL > CREATE TABLE "SCOTT". "TEST_RANGE_PARTITION"

("EMPNO" NUMBER (4. 0)

"ENAME" VARCHAR2 (10)

"JOB" VARCHAR2 (9)

"MGR" NUMBER (4. 0)

"HIREDATE" DATE

"SAL" NUMBER (7. 2)

"COMM" NUMBER (7. 2)

"DEPTNO" NUMBER (2. 0)

)

PARTITION BY RANGE ("SAL")

(PARTITION "TEST_RANGE_SAL_01" VALUES LESS THAN (1000)

PARTITION "TEST_RANGE_SAL_02" VALUES LESS THAN (2000)

PARTITION "TEST_RANGE_SAL_03" VALUES LESS THAN (3000)

PARTITION "TEST_RANGE_SAL_MAX" VALUES LESS THAN (MAXVALUE)

);

Table created.

SQL > insert into TEST_RANGE_PARTITION select * from emp

14 rows created.

SQL > commit

Commit complete.

Use the following methods to learn about the basic data distribution of the partitioned table created above.

Copy the code

-- query the conditions of each partition of the table and the distribution of the database

-- you can see that the NUM_ROWS column is empty at this time, mainly because the statistics of the table are not collected.

SQL > select a.TABLENINGONAMEMagnedPartitionNameTYPEPartitionNameNAME HIGHangVALUENow usernames partitions b where a.TABLE_NAME=b.TABLE_NAME and a.tablebooks namebooks

TABLE_NAME PARTITION PARTITION_NAME HIGH_VALUE NUM_ROWS

TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_01 1000

TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_02 2000

TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_03 3000

TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_MAX MAXVALUE

-- collect statistics for the partition table TEST_RANGE_PARTITION

SQL > analyze table TEST_RANGE_PARTITION compute statistics

Table analyzed.

As you can see, the data of each partition has been displayed at this time.

SQL > select a.TABLENINGONAMEMagnedPartitionNameTYPEPartitionNameNAME HIGHangVALUENow usernames partitions b where a.TABLE_NAME=b.TABLE_NAME and a.tablebooks namebooks

TABLE_NAME PARTITION PARTITION_NAME HIGH_VALUE NUM_ROWS

TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_01 1000 2

TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_02 2000 6

TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_03 3000 3

TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_MAX MAXVALUE 3

Through the above operation, a range partition table has been successfully created.

2. Add partition maintenance operation (add)

Increase partition maintenance operations, as the name implies, mainly for the current partition table to add new partitions.

When there is a default conditional partition in the partition table, such as the MAXVALUE partition of the RANGE partition table and the DEFAULT partition of the LIST partition table, adding the partition operation will cause an error.

Here's an attempt to add a partition TEST_RANGE_SAL_04 to the test table directly by adding a partition operation

SQL > alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_04 values less than (4000)

Alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_04 values less than (4000)

*

ERROR at line 1:

ORA-14074: partition bound must collate higher than that of the last partition

As you can see, the add partition operation cannot be performed for partitioned tables that have default conditions.

Solution:

1. Delete the original default condition partition, and then add the default condition partition again after the partition is added.

2. Use the method of split partition (split), which is described later.

Here, we try to operate under the method of solution 1.

-- Delete the partition where the default condition MAXVALUE exists

SQL > alter table TEST_RANGE_PARTITION drop partition TEST_RANGE_SAL_MAX

Table altered.

-- re-collect statistics for partition tables

SQL > analyze table TEST_RANGE_PARTITION compute statistics

Table analyzed.

-- observing the information of the partition table, you can see that the partition of the default condition MAXVALUE no longer exists.

SQL > select a.TABLENINGONAMEMagnedPartitionNameTYPEPartitionNameNAME HIGHangVALUENow usernames partitions b where a.TABLE_NAME=b.TABLE_NAME and a.tablebooks namebooks

TABLE_NAME PARTITION PARTITION_NAME HIGH_VALUE NUM_ROWS

TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_01 1000 2

TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_02 2000 6

TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_03 3000 3

-- add a new partition TEST_RANGE_SAL_04

SQL > alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_04 values less than (4000)

Table altered.

-- re-add the default conditional MAXVALUE partition

SQL > alter table TEST_RANGE_PARTITION add partition TEST_RANGE_SAL_MAX values less than (maxvalue)

Table altered.

Through the above method, the operation of adding partitions has been completed. Let's further verify the operation of adding partitions.

SQL > analyze table TEST_RANGE_PARTITION compute statistics

Table analyzed.

-- looking at the partition table information, you can see the new partition added above

SQL > select a.TABLENINGONAMEMagnedPartitionNameTYPEPartitionNameNAME HIGHangVALUENow usernames partitions b where a.TABLE_NAME=b.TABLE_NAME and a.tablebooks namebooks

TABLE_NAME PARTITION PARTITION_NAME HIGH_VALUE NUM_ROWS

--

TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_01 1000 2

TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_02 2000 6

TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_03 3000 3

TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_MAX MAXVALUE 0

TEST_RANGE_PARTITION RANGE TEST_RANGE_SAL_04 4000 0

It should be noted that if the partition of the default condition is deleted, the data will not be redistributed to other partitions, but will be deleted. Therefore, it should be used cautiously in the production environment.

At this point, the introduction of additional partition maintenance operations is over.

3. Mobile partition maintenance operation (move)

Mobile partition maintenance operations are mainly about moving partitions from one table space to another.

-- View the tablespaces corresponding to the current partition

SQL > select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from user_tab_partitions

TABLE_NAME PARTITION_NAME TABLESPACE_NAME

-

TEST_RANGE_PARTITION TEST_RANGE_SAL_02 USERS

TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS

TEST_RANGE_PARTITION TEST_RANGE_SAL_01 USERS

TEST_RANGE_PARTITION TEST_RANGE_SAL_MAX USERS

TEST_RANGE_PARTITION TEST_RANGE_SAL_04 USERS

-- perform mobile partition operation

SQL > alter table TEST_RANGE_PARTITION move partition TEST_RANGE_SAL_01 tablespace PARTITION_TS

Table altered.

-- verify the tablespace where the partition is located after the move

SQL > select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from user_tab_partitions

TABLE_NAME PARTITION_NAME TABLESPACE_NAME

-

TEST_RANGE_PARTITION TEST_RANGE_SAL_02 USERS

TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS

TEST_RANGE_PARTITION TEST_RANGE_SAL_01 PARTITION_TS

TEST_RANGE_PARTITION TEST_RANGE_SAL_MAX USERS

TEST_RANGE_PARTITION TEST_RANGE_SAL_04 USERS

It is important to note that:

For combined partitions, the partition cannot be moved directly, otherwise an ORA-14257 error will be thrown, as shown in the following example:

-- prepare a combined partition table for list-list

SQL > CREATE TABLE "EMPLOYEE_LIST_LIST_PART"

("EMPNO" NUMBER (4. 0)

"ENAME" VARCHAR2 (10)

"JOB" VARCHAR2 (9)

"MGR" NUMBER (4. 0)

"HIREDATE" DATE

"SAL" NUMBER (7. 2)

"COMM" NUMBER (7. 2)

"DEPTNO" NUMBER (2. 0)

)

PARTITION BY LIST (DEPTNO)

SUBPARTITION BY LIST (JOB)

(

PARTITION EMPLOYEE_DEPTNO_10 VALUES (10)

(SUBPARTITION EMPLOYEE_10_JOB_MAGAGER VALUES ('MANAGER')

SUBPARTITION EMPLOYEE_10_JOB_DEFAULT VALUES (DEFAULT)

),

PARTITION EMPLOYEE_DEPTNO_20 VALUES (20)

(SUBPARTITION EMPLOYEE_20_JOB_MAGAGER VALUES ('MANAGER')

SUBPARTITION EMPLOYEE_20_JOB_DEFAULT VALUES (DEFAULT)

),

PARTITION EMPLOYEE_DEPTNO_OTHERS VALUES (DEFAULT)

(SUBPARTITION EMPLOYEE_30_JOB_MAGAGER VALUES ('MANAGER')

SUBPARTITION EMPLOYEE_30_JOB_DEFAULT VALUES (DEFAULT)

)

);

Table created.

-- View the information of the current tablespace where the combined partition is located

SQL > select TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,TABLESPACE_NAME from user_tab_subpartitions

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME

EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_10 EMPLOYEE_10_JOB_MAGAGER USERS

EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_10 EMPLOYEE_10_JOB_DEFAULT USERS

EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20 EMPLOYEE_20_JOB_MAGAGER USERS

EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20 EMPLOYEE_20_JOB_DEFAULT USERS

EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_MAGAGER USERS

EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_DEFAULT USERS

-- distinction between mobile combined partition tables

SQL > alter table EMPLOYEE_LIST_LIST_PART move partition EMPLOYEE_DEPTNO_20 tablespace PARTITION_TS

Alter table EMPLOYEE_LIST_LIST_PART move partition EMPLOYEE_DEPTNO_20 tablespace PARTITION_TS

*

ERROR at line 1:

ORA-14257: cannot move partition other than a Range, List, System, or Hash partition

From the above demonstration, you can clearly see that for combined partitions, you cannot move the partition directly to the new tablespace.

Solution:

Move the child partition of the partition table, and then modify the properties of the current partition. The specific demonstration is as follows:

-- move subpartition

SQL > alter table EMPLOYEE_LIST_LIST_PART move subpartition EMPLOYEE_20_JOB_MAGAGER tablespace PARTITION_TS

Table altered.

SQL > alter table EMPLOYEE_LIST_LIST_PART move subpartition EMPLOYEE_20_JOB_DEFAULT tablespace PARTITION_TS

Table altered.

-- modify the default properties of the partition

SQL > ALTER TABLE EMPLOYEE_LIST_LIST_PART MODIFY DEFAULT ATTRIBUTES FOR PARTITION EMPLOYEE_DEPTNO_20 tablespace PARTITION_TS

Table altered.

-- verify the result after moving the partition

SQL > select TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,TABLESPACE_NAME from user_tab_subpartitions

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME TABLESPACE_NAME

EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_10 EMPLOYEE_10_JOB_MAGAGER USERS

EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_10 EMPLOYEE_10_JOB_DEFAULT USERS

EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20 EMPLOYEE_20_JOB_MAGAGER PARTITION_TS

EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_20 EMPLOYEE_20_JOB_DEFAULT PARTITION_TS

EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_MAGAGER USERS

EMPLOYEE_LIST_LIST_PART EMPLOYEE_DEPTNO_OTHERS EMPLOYEE_30_JOB_DEFAULT USERS

As you can see, the moving operation of the combined partition is completed by moving the subpartition.

4. Truncate partition maintenance operation (truncate)

It is more efficient to delete data than traditional delete operations by truncating partition maintenance operations. And it lowers the high water mark.

The demonstration is as follows:

-- check the current test table partition and the number of records in the partition

SQL > select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions where PARTITION_NAME='TEST_RANGE_SAL_02' or PARTITION_NAME='TEST_RANGE_SAL_03'

TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS

TEST_RANGE_PARTITION TEST_RANGE_SAL_02 USERS 6

TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS 3

-- perform a truncated partition operation

SQL > alter table TEST_RANGE_PARTITION truncate partition TEST_RANGE_SAL_02

Table truncated.

Re-collect statistics for the latest test tables

SQL > analyze table TEST_RANGE_PARTITION compute statistics

Table analyzed.

-- change in the number of records in the partition after verifying the truncation operation

SQL > select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions where PARTITION_NAME='TEST_RANGE_SAL_02' or PARTITION_NAME='TEST_RANGE_SAL_03'

TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS

TEST_RANGE_PARTITION TEST_RANGE_SAL_02 USERS 0

TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS 3

As you can see from the above demonstration, the TEST_RANGE_SAL_02 partition data of the test table is emptied through the truncate operation. At this point, the demonstration is over.

5. Delete partition maintenance operation (drop)

For the partition deletion operation, it should be noted that after the partition is deleted, the data recorded by the partition will not be redistributed to other partitions, but will be deleted as well.

-- check the partition of the current partition table and the distribution of the data

SQL > select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions

TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS

TEST_RANGE_PARTITION TEST_RANGE_SAL_02 USERS 0

TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS 3

TEST_RANGE_PARTITION TEST_RANGE_SAL_01 PARTITION_TS 2

TEST_RANGE_PARTITION TEST_RANGE_SAL_MAX USERS 0

TEST_RANGE_PARTITION TEST_RANGE_SAL_04 USERS 0

-- perform the deletion of the partition

SQL > alter table TEST_RANGE_PARTITION drop partition TEST_RANGE_SAL_04

Table altered.

-- check again the partition of the partition table and the distribution of the data

SQL > select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions

TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS

TEST_RANGE_PARTITION TEST_RANGE_SAL_02 USERS 0

TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS 3

TEST_RANGE_PARTITION TEST_RANGE_SAL_01 PARTITION_TS 2

TEST_RANGE_PARTITION TEST_RANGE_SAL_MAX USERS 0

As you can see, the deletion of the partition does not affect the distribution of the data.

6. Split partition maintenance operation (split)

In the section "increasing partition maintenance operations", two ways to add partitions to partition tables with default conditions are mentioned. Here we will introduce the method of splitting partitions to increase partitions.

It should be noted that after the target partition is split, the split partition will redistribute the data according to the split rules.

Demo example:

First of all, restore the data distribution of the test table to the data distribution when it was first built.

-- empty all data in the test partition table

SQL > truncate table TEST_RANGE_PARTITION

Table truncated.

-- reload the data of the test partition table

SQL > insert into TEST_RANGE_PARTITION select * from emp

14 rows created.

SQL > commit

Commit complete.

Re-collect the statistics of the test table

SQL > analyze table TEST_RANGE_PARTITION compute statistics

Table analyzed.

-- View the distribution of data between partitions at this time

SQL > select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions

TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS

TEST_RANGE_PARTITION TEST_RANGE_SAL_02 USERS 6

TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS 3

TEST_RANGE_PARTITION TEST_RANGE_SAL_01 PARTITION_TS 2

TEST_RANGE_PARTITION TEST_RANGE_SAL_MAX USERS 3

View the specific data information of the partition TEST_RANGE_SAL_MAX where the default condition MAXVALUE exists at this time:

SQL > select * from TEST_RANGE_PARTITION partition (TEST_RANGE_SAL_MAX)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7902 FORD ANALYST 7566 03-DEC-81 3000 20

The following splits the partition TEST_RANGE_SAL_MAX above, where:

Leave SAL > = 3000 and SAL=4000 's data in the partition TEST_RANGE_SAL_MAX.

-- perform split partition maintenance operations for the target partition

Split the data into partition TEST_RANGE_SAL_04 and TEST_RANGE_SAL_MAX according to the above requirements

SQL > alter table TEST_RANGE_PARTITION split partition TEST_RANGE_SAL_MAX at (4000) into (partition TEST_RANGE_SAL_04,partition TEST_RANGE_SAL_MAX)

Table altered.

-- check the partition and data distribution of the test partition table at this time

SQL > select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions

TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS

TEST_RANGE_PARTITION TEST_RANGE_SAL_02 USERS 6

TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS 3

TEST_RANGE_PARTITION TEST_RANGE_SAL_01 PARTITION_TS 2

TEST_RANGE_PARTITION TEST_RANGE_SAL_04 USERS 2

TEST_RANGE_PARTITION TEST_RANGE_SAL_MAX USERS 1

Verify the actual data content in the partition:

SQL > select * from TEST_RANGE_PARTITION partition (TEST_RANGE_SAL_04)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7902 FORD ANALYST 7566 03-DEC-81 3000 20

SQL > select * from TEST_RANGE_PARTITION partition (TEST_RANGE_SAL_MAX)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7839 KING PRESIDENT 17-NOV-81 5000 10

As you can see, after the split, the data has been stored in two partitions according to the previous requirements.

7. Merge partition maintenance operation (merge)

The operation of merging partitions is mainly to integrate different partitions through the merging of partitions.

It should be noted that:

For list partitions, there is no restriction on merged partitions.

For range partitions, the merged partitions must be adjacent, otherwise the merge operation cannot be performed.

For hash partitions, the merge partition operation cannot be performed.

Demo example:

Merge the partition TEST_RANGE_SAL_01 and the partition TEST_RANGE_SAL_02 of the test table through the merge partition technology, as follows:

-- View the partitions of the current partition table

SQL > select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions

TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS

TEST_RANGE_PARTITION TEST_RANGE_SAL_02 USERS 6

TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS 3

TEST_RANGE_PARTITION TEST_RANGE_SAL_01 PARTITION_TS 2

TEST_RANGE_PARTITION TEST_RANGE_SAL_04 USERS 2

TEST_RANGE_PARTITION TEST_RANGE_SAL_MAX USERS 1

-- query the distribution of partition TEST_RANGE_SAL_01 and TEST_RANGE_SAL_ 02 values:

SQL > select * from TEST_RANGE_PARTITION partition (TEST_RANGE_SAL_01)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20

7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30

SQL > select * from TEST_RANGE_PARTITION partition (TEST_RANGE_SAL_02)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30

7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30

7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30

7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30

7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20

7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

6 rows selected.

-- merge partitions

SQL > alter table TEST_RANGE_PARTITION merge partitions TEST_RANGE_SAL_01,TEST_RANGE_SAL_02 into partition TEST_RANGE_SAL_00

Table altered.

-- verify the result of the merged partition

SQL > select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions

TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS

TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS 3

TEST_RANGE_PARTITION TEST_RANGE_SAL_04 USERS 2

TEST_RANGE_PARTITION TEST_RANGE_SAL_MAX USERS 1

TEST_RANGE_PARTITION TEST_RANGE_SAL_00 USERS 8

SQL > select * from TEST_RANGE_PARTITION partition (TEST_RANGE_SAL_00)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20

7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30

7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30

7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30

7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30

7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30

7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20

7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

8 rows selected.

8. Swap partition maintenance operation (exchange)

Swap partition technology is mainly to exchange the data of a non-partition table with "a partition of a partition table". Two-way swapping is supported, either from the partition of the partitioned table to the non-partitioned table or from the non-partitioned table to the partition of the partitioned table.

In principle, the structure and data distribution of non-partitioned tables should conform to the rules for defining the target partitions of partitioned tables.

The demonstration is as follows:

First, empty the data in the test partition table

SQL > truncate table TEST_RANGE_PARTITION

Table truncated.

-Enquiry:

SQL > select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions

TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS

TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS 0

TEST_RANGE_PARTITION TEST_RANGE_SAL_04 USERS 0

TEST_RANGE_PARTITION TEST_RANGE_SAL_MAX USERS 0

TEST_RANGE_PARTITION TEST_RANGE_SAL_00 USERS 0

-create a table based on emp, sal create table emp_test as select * from emp where sal

< 2000; Table created. SQL>

Select count (*) from emp_test

COUNT (*)

-

eight

Note that the amount of data in the non-partitioned table at this time is 8 records.

-perform swap partition operation and observe the record changes of partition tables and non-partition tables

-perform partition swap operation

SQL > alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test

Table altered.

SQL > analyze table TEST_RANGE_PARTITION compute statistics

Table analyzed.

SQL > select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions

TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS

TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS 0

TEST_RANGE_PARTITION TEST_RANGE_SAL_00 USERS 8

TEST_RANGE_PARTITION TEST_RANGE_SAL_04 USERS 0

TEST_RANGE_PARTITION TEST_RANGE_SAL_MAX USERS 0

SQL > select count (*) from emp_test

COUNT (*)

-

0

As you can see, through the partition swap, the data of the non-partitioned table is transferred to the partitioned table, and the records of the non-partitioned table are cleared.

-perform the swap partition operation again to observe the record changes of the partition table and the record changes of the non-partition table

SQL > alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test

Table altered.

SQL > analyze table TEST_RANGE_PARTITION compute statistics

Table analyzed.

SQL > select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions

TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS

TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS 0

TEST_RANGE_PARTITION TEST_RANGE_SAL_04 USERS 0

TEST_RANGE_PARTITION TEST_RANGE_SAL_MAX USERS 0

TEST_RANGE_PARTITION TEST_RANGE_SAL_00 USERS 0

SQL > select count (*) from emp_test

COUNT (*)

-

eight

As you can see, the data of the partitioned table is transferred back to the non-partitioned table again, which proves that the partition exchange technology can be migrated from the partition of the partitioned table to the non-partitioned table as well as from the non-partitioned table to the partition of the partitioned table.

Note: if the data of the non-partitioned table does not comply with the partitioning rules of the partitioned table, the exchange will throw an ORA-14099 error.

-- clear the data of the above test non-partitioned table

SQL > truncate table emp_test

Table truncated.

-- load all data from emp to the test non-partitioned table

The reason for using test non-partitioned tables is to consider the data that emp tables may need for other experiments in the future.

By doing this, you can test the data of the non-partitioned table, and the data of the sal2000 exists.

SQL > insert into emp_test select * from emp

14 rows created.

SQL > commit

Commit complete.

-- try to swap partitions and observe the results

SQL > alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test

Alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test

*

ERROR at line 1:

ORA-14099: all rows in table do not qualify for specified partition

As you can see, the exchange failed because the restriction of the TEST_RANGE_SAL_00 partition is the data of sal2000.

Solution:

Through the without validation clause, data validation can be avoided and the exchange is successful. However, there will be data that runs counter to the partition rules, so this method should be cautious.

SQL > alter table TEST_RANGE_PARTITION exchange PARTITION TEST_RANGE_SAL_00 with table emp_test without validation

Table altered.

SQL > analyze table TEST_RANGE_PARTITION compute statistics

Table analyzed.

SQL > select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions

TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS

TEST_RANGE_PARTITION TEST_RANGE_SAL_03 USERS 0

TEST_RANGE_PARTITION TEST_RANGE_SAL_00 USERS 14

TEST_RANGE_PARTITION TEST_RANGE_SAL_04 USERS 0

TEST_RANGE_PARTITION TEST_RANGE_SAL_MAX USERS 0

The idea of expanding the technical solution:

If you plan to use the method of swapping partitions to realize the conversion from non-partitioned tables to partitioned tables, you can first create a single partitioned table with default conditions, and after the partitions exchange data, according to the actual needs, partition operations are carried out through the "split partition" method mentioned earlier. That is, the large table is changed to the partition table (swap partition + partition split)

9. Shrink partition maintenance operation (coalesce)

Shrinking partition maintenance operations can only be used on hash partitions and hash subpartitions of combined partitions.

By using the shrinking partitioning technique, you can shrink the number of partitions in the current hash partition.

For data in hash partitions, oracle automatically redistributes the data between partitions during the contraction process.

The demonstration is as follows:

First, create a hash partition table based on the data of the emp table

SQL > CREATE TABLE "EMPLOYEE_HASH_PART"

("EMPNO" NUMBER (4. 0)

"ENAME" VARCHAR2 (10)

"JOB" VARCHAR2 (9)

"MGR" NUMBER (4. 0)

"HIREDATE" DATE

"SAL" NUMBER (7. 2)

"COMM" NUMBER (7. 2)

"DEPTNO" NUMBER (2. 0)

)

PARTITION BY HASH (ENAME)

(

PARTITION EMPLOYEE_PART01

PARTITION EMPLOYEE_PART02

);

Table created.

SQL > insert into EMPLOYEE_HASH_PART select * from emp

14 rows created.

SQL > commit

Commit complete.

SQL > analyze table EMPLOYEE_HASH_PART compute statistics

Table analyzed.

SQL > select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions

TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS

EMPLOYEE_HASH_PART EMPLOYEE_PART02 USERS 6

EMPLOYEE_HASH_PART EMPLOYEE_PART01 USERS 8

Perform a shrinking partition operation

SQL > alter table EMPLOYEE_HASH_PART coalesce partition

Table altered.

SQL > analyze table EMPLOYEE_HASH_PART compute statistics

Table analyzed.

SQL > select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions

TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS

EMPLOYEE_HASH_PART EMPLOYEE_PART01 USERS 14

As you can see, by shrinking partitions, the original two partitions are integrated into one, and the data is integrated at the same time.

It should be noted that:

When there is only one partition in the hash partition, the shrink operation cannot be performed at this time.

SQL > select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,num_rows from user_tab_partitions

TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS

EMPLOYEE_HASH_PART EMPLOYEE_PART01 USERS 14

SQL > alter table EMPLOYEE_HASH_PART coalesce partition

Alter table EMPLOYEE_HASH_PART coalesce partition

*

ERROR at line 1:

ORA-14285: cannot COALESCE the only partition of this hash partitioned table or index

Thank you for your reading, the above is the content of "what is the daily maintenance method of ORACLE partition table". After the study of this article, I believe you have a deeper understanding of what the daily maintenance method of ORACLE partition table is, 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.