oracle查看分区名称,oracle学习笔记之 分区表

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