Oracle提升查询性能之-简单范围分区表的创建

1.提高查询性能：只需要搜索特定分区，而非整张表，提高了查询速度。2.节约维护时间：单个分区的数据装载，索引重建，备份，维护远远小于整张表的维护时间。
下面就让我们来创建一张分区表SELECT NAME FROM v$datafile;–如果是你熟悉的环境则省略这一步，目的是查看当前文件路径
–创建表空间，如果有专人维护表空间尽量关闭自动增长，长时间无人维护则开启自动增长，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 DATAFIL免费云主机域名E ‘/oradata/orawms/partition_p_max.dbf’ SIZE 200m AUTOEXTEND OFF;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 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
);INSERT INTO partition_tab_Name SELECT * FROM ‘源表名’;

相关推荐: latch:library cache lock等待事件

This event controls the concurrency between clients of the library cache. It acquires a lock on the object handle so that either: …