Oracle分区表的使用

最新推荐文章于 2022-03-17 17:10:44 发布

时间辜负了谁

最新推荐文章于 2022-03-17 17:10:44 发布

阅读量893

点赞数

本文链接：https://blog.csdn.net/kpchen_0508/article/details/49335861

版权

Oracle分区表的新增、修改、删除、合并；普通表转分区表方法

一. 分区表理论知识

Oracle提供了分区技术以支持VLDB(Very Large DataBase)。分区表通过对分区列的判断，把分区列不同的记录，放到不同的分区中。分区完全对应用透明。
Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（SEGMENT），可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。

在oracle 10g中最多支持：1024k-1个分区：
Tables can be partitioned into up to 1024K-1 separate partitions
联机文档上有关分区表和索引的说明：
Partitioned Tables and Indexes
http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/partconc.htm#sthref2604

分区提供以下优点：

（1）由于将数据分散到各个分区中，减少了数据损坏的可能性；
（2）可以对单独的分区进行备份和恢复；
（3）可以将分区映射到不同的物理磁盘上，来分散IO；
（4）提高可管理性、可用性和性能。

Oracle 10g提供了以下几种分区类型：

（1）范围分区（range）；
（2）哈希分区（hash）；
（3）列表分区（list）；
（4）范围－哈希复合分区（range-hash）；
（5）范围－列表复合分区（range-list）。

Range分区：

Range分区是应用范围比较广的表分区方式，它是以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中。
如按照时间划分，2010年1月的数据放到a分区，2月的数据放到b分区，在创建的时候，需要指定基于的列，以及分区的范围值。
在按时间分区时，如果某些记录暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中。
如：

CREATE TABLE pdba (

id number,

time date

)

partition BY range (time)

(partition p1 VALUES LESS THAN (to_date('2010-10-1', 'yyyy-mm-dd')),

partition p2 VALUES LESS THAN (to_date('2010-11-1', 'yyyy-mm-dd')),

partition p3 VALUES LESS THAN (to_date('2010-12-1', 'yyyy-mm-dd')),

partition p4 VALUES LESS THAN (MAXVALUE))

Hash分区：

对于那些无法有效划分范围的表，可以使用hash分区，这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你指定的几个分区中，列所在分区是依据分区列的hash值自动分配，因此你并不能控制也不知道哪条记录会被放到哪个分区中，hash分区也可以支持多个依赖列。
如：

CREATE TABLE test (

transaction_id number PRIMARY KEY,

item_id number(8) NOT NULL)

partition BY hash(transaction_id)

(partition part_01 tablespace tablespace01,

partition part_02 tablespace tablespace02,

partition part_03 tablespace tablespace03);

在这里，我们指定了每个分区的表空间。

List分区：

List分区也需要指定列的值，其分区值必须明确指定，该分区列只能有一个，不能像range或者hash分区那样同时指定多个列做为分区依赖列，但它的单个分区对应值可以是多个。
在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。
在根据某字段，如城市代码分区时，可以指定default，把非分区规则的数据，全部放到这个default分区。
如：

CREATE TABLE custaddr (

id varchar2(15 byte) NOT NULL,

areacode varchar2(4 byte))

partition BY list (areacode)

( partition t_list025 VALUES ('025'),

partition t_list372 VALUES ('372') ,

partition t_list510 VALUES ('510'),

partition p_other VALUES (DEFAULT));

二. 普通表转分区表方法

将普通表转换成分区表有4种方法：

Export/import method
Insert with a subquery method
Partition exchange method
DBMS_REDEFINITION

参考：http://database.51cto.com/art/201103/250249.htm

三、List分区表的具体实例

1）、创建分区表

--创建List分区表M_CELL_TEMP

create table MIQTEST.M_CELL_TEMP

(

ttime DATE,

createtime DATE,

id VARCHAR2(32) not null,

state_type NUMBER(10),

monitor_type NUMBER(10),

confirm_time DATE,

cancel_time DATE,

keyvalue VARCHAR2(20)

)

--根据keyvalue的值，进入不同的分区（按小时进行分区）

partition by list (keyvalue)

(

partition M_CELL_TEMP_part00_03 values ('00','01','02','03'),

partition M_CELL_TEMP_part04_07 values ('04','05','06','07'),

partition M_CELL_TEMP_part08_11 values ('08','09','10','11'),

partition M_CELL_TEMP_part12_15 values ('12','13','14','15'),

partition M_CELL_TEMP_part16_19 values ('16','17','18','19'),

partition M_CELL_TEMP_part20_23 values ('20','21','22','23')

);

--创建List分区表M_CELL_TEMP

create table MIQTEST.M_CELL

(

ttime DATE,

createtime DATE,

id VARCHAR2(32) not null,

state_type NUMBER(10),

monitor_type NUMBER(10),

confirm_time DATE,

cancel_time DATE,

keyvalue VARCHAR2(20)

)

--根据keyvalue的值，进入不同的分区（按星期进行分区）

partition by list (keyvalue)

(

partition M_CELL_total_mon values ('monday'),

partition M_CELL_total_tue values ('tuesday'),

partition M_CELL_total_wed values ('wednesday'),

partition M_CELL_total_thu values ('thursday'),

partition M_CELL_total_fri values ('friday'),

partition M_CELL_total_sat values ('saturday'),

partition M_CELL_total_sun values ('sunday')

);

-- 建立索引

在使用分区表时尽量创建本地索引，不然在删除分区的时候破坏索引

create index index_m_cell_id on m_cell(id) local;

create index index_m_cell_state_type on m_cell(state_type) local;

create index index_m_cell_monitor_type on m_cell(monitor_type) local;

create index index_m_cell_temp_id on m_cell_temp(id) local;

create index index_m_cell_temp_state_type on m_cell_temp(state_type) local;

create index index_m_cell_temp_monitor_type on m_cell_temp(monitor_type) local;

2）、往分区表中插入数据

insert into m_cell

(ttime, createtime, id, state_type, monitor_type, confirm_time, cancel_time, keyvalue)

select ttime, createtime, sys_guid(), state_type, monitor_type, confirm_time, cancel_time,

replace(to_char(createtime,'day','nls_date_language=american'),' ','')

from m_cell_bak

3）、查看分区表

select count(1) from m_cell partition(M_CELL_total_mon);

4）、删除分区表中的一个分区

ALTER TABLE m_cell TRUNCATE PARTITION M_CELL_total_mon;

5）、分区表在存储过程中的使用

CREATE OR REPLACE PROCEDURE alarm_cell_cancle IS

v_temp_week varchar2(20);

v_table_name_week varchar2(50);

v_sql varchar2(200);

BEGIN

-- 查询当前系统时间，获得星期几

select replace(to_char(sysdate,'day','nls_date_language=american'),' ','') into v_temp_week from dual;

-- 根据星期几判断删除哪张分区表

if v_temp_week='monday' then

v_table_name_week := 'M_CELL_total_sun';

elsif v_temp_week='tuesday' then

v_table_name_week := 'M_CELL_total_mon';

elsif v_temp_week='wednesday' then

v_table_name_week := 'M_CELL_total_tue';

elsif v_temp_week='thursday' then

v_table_name_week := 'M_CELL_total_wed';

elsif v_temp_week='friday' then

v_table_name_week := 'M_CELL_total_thu';

elsif v_temp_week='saturday' then

v_table_name_week := 'M_CELL_total_fri';

elsif v_temp_week='sunday' then

v_table_name_week := 'M_CELL_total_sat';

else

dbms_output.put_line('error!');

end if;

-- 更新cell表某个分区的数据是否超过24小时

v_sql:='update m_cell partition('||v_table_name_week||') set state_type = 3, cancel_time = sysdate where state_type = 1 and (sysdate - interval ''24'' hour) > ttime';

EXECUTE IMMEDIATE v_sql;

commit;

END alarm_cell_cancle;

按星期建立分区表（11g，oracle）

CREATE TABLE PT

(

getdate date NOT NULL,

wd NUMBER GENERATED ALWAYS AS (TO_NUMBER (TO_CHAR (getdate, 'D'))) VIRTUAL

)

PARTITION BY LIST (wd)

(PARTITION Sun VALUES (1),

PARTITION Mon VALUES (2),