Oracle提升查詢(xún)性能之-簡(jiǎn)單范圍分區(qū)表的創(chuàng)建

分區(qū)表的優(yōu)點(diǎn)：

1.提高查詢(xún)性能：只需要搜索特定分區(qū)，而非整張表，提高了查詢(xún)速度。

2.節(jié)約維護(hù)時(shí)間：?jiǎn)蝹€(gè)分區(qū)的數(shù)據(jù)裝載，索引重建，備份，維護(hù)遠(yuǎn)遠(yuǎn)小于整張表的維護(hù)時(shí)間。
下面就讓我們來(lái)創(chuàng)建一張分區(qū)表

第一步：為了提高IO的性能創(chuàng)建分區(qū)表的專(zhuān)用表空間

SELECT NAME FROM v$datafile;--如果是你熟悉的環(huán)境則省略這一步，目的是查看當(dāng)前文件路徑
--創(chuàng)建表空間，如果有專(zhuān)人維護(hù)表空間盡量關(guān)閉自動(dòng)增長(zhǎng)，長(zhǎng)時(shí)間無(wú)人維護(hù)則開(kāi)啟自動(dòng)增長(zhǎng)，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;

第二步：創(chuàng)建表分區(qū)

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（分區(qū)字段，這里以創(chuàng)建時(shí)間做范圍分區(qū)）
(
PARTITION partition_p_Name1 VALUES LESS THAN (TO_date('2015-01-01','YYYY-MM-DD')) TABLESPACE partition_p_Name1,
PARTITION partition_p_Name2 VALUES LESS THAN (TO_date('2016-01-01','YYYY-MM-DD')) TABLESPACE partition_p_Name2,
PARTITION partition_p_Name3 VALUES LESS THAN (TO_date('2017-01-01','YYYY-MM-DD')) TABLESPACE partition_p_Name3,
PARTITION partition_p_Name4 VALUES LESS THAN (TO_date('2018-01-01','YYYY-MM-DD')) TABLESPACE partition_p_Name4,
PARTITION partition_p_Name5 VALUES LESS THAN (TO_date('2019-01-01','YYYY-MM-DD')) TABLESPACE partition_p_Name5,
PARTITION partition_p_max VALUES LESS THAN (MAXVALUE) TABLESPACE partition_p_max
);

第三步：插入數(shù)據(jù)

INSERT INTO partition_tab_Name SELECT * FROM ‘源表名’;

第四步：查詢(xún)數(shù)據(jù)測(cè)試吧~

SELECT  * FROM goods_inf_r1 PARTITION(partition_p_Name1);--partition_p_Name2，partition_p_Name3~~~