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

This article mainly explains the "mysql ordinary table how to convert into partition table", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "mysql ordinary table how to convert into partition table" bar!

1) EXPDP\ IMPDP

Using logical export to import is very simple, first create a partition table in the source database, then export the data, and then import it into the new partition table

2) Insert into

Use the original table to rebuild the partition table, and then insert into the partition table select * from original table

3) Exchange Partition (swap partition)

4) DBMS_REDEFINITION (online redefinition)

This paper briefly introduces the experimental process of 1 and 2.

The general table data are as follows

Conn hr/hr

SQL > select * from CUSTOMER99

CUST_NAME CUST_ID

1 1

11 11

21 21

31 31

41 41

The case of Insert into

SQL > CREATE TABLE customer199

(cust_name varchar2 (20)

, cust_id NUMBER

)

PARTITION BY RANGE (cust_id)

(PARTITION Q1t VALUES LESS THAN (10)

, PARTITION Q2t VALUES LESS THAN (20)

, PARTITION q3t VALUES LESS THAN (30)

, PARTITION q4t VALUES LESS THAN (40)

, partition q5t VALUES LESS THAN (maxvalue)

);

SQL > insert into customer199 select * from CUSTOMER99

SQL > select * from customer199 partition (Q3t)

CUST_NAME CUST_ID

21 21

-- case of EXPDP\ IMPDP

Expdp hr/hr directory=DATA_PUMP_DIR dumpfile=CUSTOMER99.dmp tables=CUSTOMER99

CREATE TABLE customer_expdp

(cust_name varchar2 (20)

, cust_id NUMBER

)

PARTITION BY RANGE (cust_id)

(PARTITION Q11 VALUES LESS THAN (10)

, PARTITION Q21 VALUES LESS THAN (20)

, PARTITION Q31 VALUES LESS THAN (30)

, PARTITION Q41 VALUES LESS THAN (40)

, partition Q51 VALUES LESS THAN (maxvalue)

);

Impdp hr/hr directory=DATA_PUMP_DIR dumpfile=CUSTOMER99.dmp remap_table=CUSTOMER99:customer_expdp table_exists_action=append

SQL > select * from customer_expdp partition (Q31)

CUST_NAME CUST_ID

21 21

Thank you for your reading, these are the contents of "how to convert mysql ordinary tables into partition tables". After the study of this article, I believe you have a deeper understanding of how to convert mysql ordinary tables into partition tables, 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.