oracle学习笔记之分区表

原创

jsj_007 2012-05-06 18:44:30 博主文章分类：ORACLE ©著作权

文章标签 oracle 分区表 range 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者jsj_007的原创作品，请联系作者获取转载授权，否则将追究法律责任

1、概念：

分区表：当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。

Oracle的分区表可以包括多个分区，每个分区都是一个独立的段（SEGMENT），可以存放到不同的表空间中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。

2、分区的类型：

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

3、示例：

a、Partition exchange method：

1、查看数据：

SELECT to_char(begintime,'yyyy') YEAR,count(*) FROM nmgdx_nopart group by to_char(begintime,'yyyy');

YEAR COUNT(*)
201201 32736
201202 30624
201203 16736

2、创建表:
此表结构与原表一样。
-- Create table
create table NMGDX_PART
(
begintime       DATE not null,
endtime         DATE not null,
city_id         VARCHAR2(16) not null,
city_name       VARCHAR2(32),
ap_num          NUMBER,
available_num   NUMBER,
unavailable_num NUMBER,
available_rate NUMBER,
is_day          NUMBER(1)
)
tablespace MBWBASEDATA
partition by range(begintime)
(partition aprate values less than (maxvalue))

---partition p01 values less than (to_date('201201','yyyymm'),

---partition p02 values less than (to_date('201202','yyyymm'),

3、交换分区：
交换两分区表的数据
alter table nmgdx_part exchange partition aprate with table nmgdx_nopart;

SELECT to_char(begintime,'yyyy'),count(*) FROM nmgdx_part group by to_char(begintime,'yyyy');

YEAR COUNT(*)
201201 32736
201202 30624
201203 16736

SELECT to_char(begintime,'yyyy'),count(*) FROM nmgdx_nopart group by to_char(begintime,'yyyy');
no rows selected

4、拆分分区aprate：
alter table nmgdx_part split partition aprate at (to_date('201201','yyyymm')) into (partition P_201201,partition aprate);
alter table nmgdx_part split partition aprate at (to_date('201202','yyyymm')) into (partition P_201202 ,partition aprate);
alter table nmgdx_part split partition aprate at (to_date('201213','yyyymm')) into (partition P_201203,partition aprate);
alter table nmgdx_part split partition aprate at (to_date('201204','yyyymm')) into (partition P_201204,partition aprate);

5、创建索引：
create index aprate_idx on nmgdx_nopart(begintime) local;

6、重命名分区：

alter table nmgdx_nopart rename to nmgdx_nopart_bak;
alter table nmgdx_part rename to nmgdx_nopart;

7、查询验证：
SELECT count(*) FROM nmgdx_nopart partition(P_201202);

COUNT(*)
1 32736

SELECT count(*) FROM nmgdx_nopart partition(P_201202);

COUNT(*)
1 30624

SELECT count(*) FROM nmgdx_nopart partition(P_201203);

COUNT(*)
1 16736

b、Export/import method

1、导出表：：
exp wipnms1/wipnms1 tables=nmgdx_test file=c:\exp.dmp
此处注意客户端版本要与服务器一致，否则报以下错：
EXP-00008: 遇到 ORACLE 错误 904 ORA-00904: "MAXSIZE": invalid identifier
详细参阅：http://blog.csdn.net/johnstrive/article/details/5660729

2、drop 表:
drop table nmgdx_test purge;

3、创建分区表：
-- Create table
create table NMGDX_test
(
begintime       DATE not null,
endtime         DATE not null,
city_id         VARCHAR2(16) not null,
city_name       VARCHAR2(32),
ap_num          NUMBER,
available_num   NUMBER,
unavailable_num NUMBER,
available_rate NUMBER,
is_day          NUMBER(1)
)
partition by range(begintime)
(
partition P01 values less than(to_date('2012-02-01','yyyy-mm-dd')),
partition P02 values less than(to_date('2012-04-01','yyyy-mm-dd')),
partition P03 values less than(maxvalue));

4、导入数据:
imp wipnms1/wipnms1 file=exp.dmp ignore=y

c、Insert with a subquery method

1) 创建分区table:
create table NMGDX_CS
(
begintime       DATE not null,
endtime         DATE not null,
city_id         VARCHAR2(16) not null,
city_name       VARCHAR2(32),
ap_num          NUMBER,
available_num   NUMBER,
unavailable_num NUMBER,
available_rate NUMBER,
is_day          NUMBER(1)
)
partition by range(begintime)
(
partition P01 values less than(to_date('2012-02-01','yyyy-mm-dd')),
partition P02 values less than(to_date('2012-04-01','yyyy-mm-dd')),
partition P03 values less than(maxvalue));

2) 接数据插入到分区表中:

insert into nmgdx_cs select * from nmgdx_test;

d、DBMS_REDEFINITION

在线重定义的大致操作流程如下：
（1）创建基础表A，如果存在，就不需要操作。
（2）创建临时的分区表B。
（3）开始重定义，将基表A的数据导入临时分区表B。
（4）结束重定义，此时在DB的 Name Directory里，已经将2个表进行了交换。即此时基表A成了分区表，我们创建的临时分区表B 成了普通表。此时我们可以删除我们创建的临时表B。它已经是普通表。

---http://gaoqinqing.blog.163.com/blog/static/47020022011618102610551/

--创建基本表和索引

create table nmgdx_test (
id number(10),
crate_date date,
name varchar2(100)
);

alter table nmgdx_test ADD (CONSTRAINT nmgdx_test PRIMARY KEY (id));
create index create_date_ind on nmgdx_test(crate_date);
INSERT INTO nmgdx_test SELECT ROWNUM,created,object_name FROM Dba_Objects;

--收集表的统计信息
exec dbms_stats.gather_table_stats('wipnms1','nmgdx_test',cascade=>true);

--创建临时分区表
create table nmgdx_cs (
id number(10),
crate_date date,
name varchar2(100)
)
PARTITION BY RANGE(crate_date)
(
PARTITION p001 VALUES LESS THAN(to_date('200912','yyyymm')),
PARTITION p002 VALUES LESS THAN(to_date('201012','yyyymm')),
PARTITION p003 VALUES LESS THAN(to_date('201112','yyyymm')),
PARTITION p004 VALUES LESS THAN(to_date('201212','yyyymm')),
PARTITION p005 values less than(maxvalue));

---进行重定义操作
----检查重定义的合理性
EXEC Dbms_Redefinition.can_redef_table('wipnms1','nmgdx_test');

PL/SQL procedure successfully completed.

---开始重定义,分区表和原表列名相同
begin
dbms_redefinition.start_redef_table(
uname=>'wipnms1',
orig_table=>'nmgdx_test',
int_table=>'nmgdx_cs');
end;
/

--如果分区表的列名和原表不一致，那么在开始重定义的时候，需要重新指定映射关系：
EXEC DBMS_REDEFINITION.START_REDEF_TABLE(
'wipnms1',
'unpar_table',
'par_table',
'ID ID, create_date TIME', -- 在这里指定新的映射关系
DBMS_REDEFINITION.CONS_USE_PK);

---同步新表
begin
dbms_redefinition.sync_interim_table(
uname=>'wipnms1',
orig_table=>'nmgdx_test',
int_table=>'nmgdx_cs');
end;
/

---创建索引，在线重定义只重定义数据，索引还需要单独建立

create index create_date_ind2 on nmgdx_cs(crate_date);

--收集新表的统计信息
exec dbms_stats.gather_table_stats('wipnms1','nmgdx_cs',cascade=>true);

---结束重定义
begin
dbms_redefinition.finish_redef_table(
uname=>'wipnms1',
orig_table=>'nmgdx_test',
int_table=>'nmgdx_cs');
end;
/

---结束重定义的意义：
--基表unpar_table 和临时分区表par_table 进行了交换。此时临时分区表par_table成了普通表，我们的基表unpar_table成了分区表。
--我们在重定义的时候，基表unpar_table是可以进行DML操作的。只有在2个表进行切换的时候会有短暂的锁表。

--- 删除临时表
DROP TABLE nmgdx_cs;

---索引重命名
ALTER INDEX create_date_ind2 RENAME TO create_date_ind;

--验证
select partitioned from user_tables where lower(table_name) = 'nmgdx_test';
par
1 YES

select partition_name from user_tab_partitions where lower(table_name) = 'nmgdx_test';
1 P005
2 P004
3 P003
4 P002
5 P001