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

Advantages of partitioned tables:

1. Improve query performance: only specific partitions need to be searched, not the entire table, which improves query speed.

two。 Save maintenance time: data loading, index reconstruction, backup, and maintenance of a single partition are far less than the maintenance time of the entire table.

Let's create a partition table.

Step 1: create a dedicated tablespace for partitioned tables to improve the performance of IO

SELECT NAME FROM vault datafileThere-omit this step if you are familiar with the environment in order to see the current file path

-- create a table space. If there is a special person to maintain the table space, turn off automatic growth as far as possible, and enable automatic growth if it is left unmaintained for a long time. AUTOEXTEND ON SIZE

CREATE TABLESPACE partition_p_Name1 DATAFILE'/ oradata/orawms/partition_p_Name1.dbf' SIZE 200m AUTOEXTEND OFF

CREATE TABLESPACE partition_p_Name2 DATAFILE'/ oradata/orawms/partition_p_Name2.dbf' SIZE 200m AUTOEXTEND OFF

CREATE TABLESPACE partition_p_Name3 DATAFILE'/ oradata/orawms/partition_p_Name3.dbf' SIZE 200m AUTOEXTEND OFF

CREATE TABLESPACE partition_p_Name4 DATAFILE'/ oradata/orawms/partition_p_Name4.dbf' SIZE 200m AUTOEXTEND OFF

CREATE TABLESPACE partition_p_Name5 DATAFILE'/ oradata/orawms/partition_p_Name5.dbf' SIZE 200m AUTOEXTEND OFF

CREATE TABLESPACE goods_inf_max DATAFILE'/ oradata/orawms/partition_p_max.dbf' SIZE 200m AUTOEXTEND OFF

Step 2: create a table partition

Create table partition_tab_Name

(

Create_date DATE

Create_man VARCHAR2 (64)

Modify_date DATE

Modify_man VARCHAR2 (64)

Create_org VARCHAR2 (20)

Create_orgseq VARCHAR2 (512)

Create_role VARCHAR2 (50)

Fields1 VARCHAR2 (200)

Fields2 VARCHAR2 (200)

Fields3 VARCHAR2 (200)

Fields4 VARCHAR2 (200)

Fields5 VARCHAR2 (200)

Fields6 VARCHAR2 (200)

Fields7 VARCHAR2 (200)

Fields8 VARCHAR2 (200)

Fields9 VARCHAR2 (200)

Fields10 VARCHAR2 (200)

)

PARTITION BY RANGE (create_date)

-- range (partition field, where the scope is partitioned by creation time)

(

PARTITION partition_p_Name1 VALUES LESS THAN (TO_date ('2015-01-01-01 / 01 / 01 / 01 / 01 / 01 / 01) TABLESPACE partition_p_Name1

PARTITION partition_p_Name2 VALUES LESS THAN (TO_date ('2016-01-01-01 / 01 / 01 / 01 / 01 / 01 / 01) TABLESPACE partition_p_Name2

PARTITION partition_p_Name3 VALUES LESS THAN (TO_date ('2017-01-01-01 / 01 / 01 / 01 / 01 / 01 / 01) TABLESPACE partition_p_Name3

PARTITION partition_p_Name4 VALUES LESS THAN (TO_date ('2018-01-01-01 / 01 / 01 / 01 / 01 / 01 / 01) TABLESPACE partition_p_Name4

PARTITION partition_p_Name5 VALUES LESS THAN (TO_date ('2019-01-01-01 / 01 / 01 / 01 / 01 / 01 / 01) TABLESPACE partition_p_Name5

PARTITION partition_p_max VALUES LESS THAN (MAXVALUE) TABLESPACE partition_p_max

);

Step 3: insert data

INSERT INTO partition_tab_Name SELECT * FROM 'source table name'

Step 4: query the data test bar ~ SELECT * FROM goods_inf_r1 PARTITION (partition_p_Name1);-- partition_p_Name2,partition_p_Name3~~~

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.