基本概念
1.表空间:是一个或多个数据文件的集合,所有的数据对象都存放在指定的表空间中,但主要存放的是表, 所以称作表空间。
2.分区表:当表中的数据量不断增大,查询数据的速度就会变慢,应用程序的性能就会下降,这时就应该考虑对表进行分区。表进行分区后,逻辑上表仍然是一张完整的表,只是将表中的数据在物理上存放到多个表空间(物理文件上),这样查询数据时,不至于每次都扫描整张表。
表分区的作用:
表分区的优点:
表分区的缺点:
已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能。
表分区的类型:
范围分区(RANGE):
范围分区将数据基于范围映射到每一个分区,这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的,并且分区键经常采用日期。举个例子:你可能会将销售数据按照月份进行分区。
散列分区(HASH):
列表分区(LIST):
该分区的特点是某列的值只有几个,基于这样的特点我们可以采用列表分区。
复合分区:
这种分区是基于范围分区和列表分区,表首先按某列进行范围分区,然后再按某列进行列表分区,分区之中的分区被称为子分区。
表分区语法:
范围分区
根据id划分
create table student(
id number(4) primary key,
name varchar2(20) not null,
sex varchar2(2) ,
remark varchar2(500),
birthday date
)
partition by range(id)
(
partition par_student_p1 values less than(10000) tablespace student_space_1,
partition par_student_p2 values less than(20000) tablespace student_space_2,
partition par_student_p2 values less than(MAXVALUE) tablespace student_space_3
)
根据时间划分
create table student(
id number(4) primary key,
name varchar2(20) not null,
sex varchar2(2) ,
remark varchar2(500),
birthday date
)
partition by range(birthday)
(
partition par_student_p1 values less than((TO_DATE('01- MAY -2003','DD-MON-YYYY')) tablespace student_space_1,
partition par_student_p2 values less than(TO_DATE('01-JUN-2003','DD-MON-YYYY')) tablespace student_space_2
)
列表分区:
create table student(
id number(4) primary key,
name varchar2(20) not null,
sex varchar2(2) ,
remark varchar2(500),
birthday date
)
partition by list(sex)
(
partition par_student_p1 values("男") tablespace student_space_1,
partition par_student_p2 values("女") tablespace student_space_2
)
散列分区:
create table student(
id number(4) primary key,
name varchar2(20) not null,
sex varchar2(2) ,
remark varchar2(500),
birthday date
)
partition by hash(id)
(
partition par_student_p1 tablespace student_space_1,
partition par_student_p2 tablespace student_space_2,
partition par_student_p2 tablespace student_space_3
)
复合分区组合范围散列分区
create table student(
id number(4) primary key,
name varchar2(20) not null,
sex varchar2(2) ,
remark varchar2(500),
birthday date
)
partition by range(id) subpartition by list(sex)(
partition par_student_p1 values less than(1000) tablespace student_space_1
(
subpartition par_student_p1_p1 values("男") tablespace student_space_1,
subpartition par_student_p1_p1 values("女") tablespace student_space_1
),
partition par_student_p2 values less than(2000) tablespace student_space_2
(
subpartition par_student_p1_p1 values("男") tablespace student_space_2,
subpartition par_student_p1_p1 values("女") tablespace student_space_2
)
)
复合范围散列分区:
create table student(
id number(4) primary key,
name varchar2(20) not null,
sex varchar2(2) ,
remark varchar2(500),
birthday date
)
partition by range(birthday)subpartition by hash(id) subpartitions 3 store in (student_space_1,student_space_2,student_space_3)
(
partition part_01 values less than(to_date(‘2006-01-01’,’yyyy-mm-dd’)),
partition part_02 values less than(to_date(‘2010-01-01’,’yyyy-mm-dd’)),
partition part_03 values less than(maxvalue)
); 分区的基本操作:
一、添加分区
以下代码给SALES表添加了一个P3分区
ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));
注意:以上添加的分区界限应该高于最后一个分区界限。
以下代码给SALES表的P3分区添加了一个P3SUB1子分区
ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');
二、删除分区
以下代码删除了P3表分区:
ALTER TABLE SALES DROP PARTITION P3;
在以下代码删除了P4SUB1子分区:
ALTER TABLE SALES DROP SUBPARTITION P4SUB1;
注意:如果删除的分区是表中唯一的分区,那么此分区将不能被删除,要想删除此分区,必须删除表。
三、截断分区
截断某个分区是指删除某个分区中的数据,并不会删除分区,也不会删除其它分区中的数据。当表中即使只有一个分区时,也可以截断该分区。通过以下代码截断分区:
ALTER TABLE SALES TRUNCATE PARTITION P2;
通过以下代码截断子分区:
ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;
四、合并分区
合并分区是将相邻的分区合并成一个分区,结果分区将采用较高分区的界限,值得注意的是,不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并:
ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;
五、拆分分区
拆分分区将一个分区拆分两个新分区,拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。
ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);
六、接合分区(coalesca)
结合分区是将散列分区中的数据接合到其它分区中,当散列分区中的数据比较大时,可以增加散列分区,然后进行接合,值得注意的是,接合分区只能用于散列分区中。通过以下代码进行接合分区:
ALTER TABLE SALES COALESCA PARTITION;
七、重命名表分区
以下代码将P21更改为P2
ALTER TABLE SALES RENAME PARTITION P21 TO P2;
八、相关查询
跨分区查询
select sum( *) from
(select count(*) cn from t_table_SS PARTITION (P200709_1)
union all
select count(*) cn from t_table_SS PARTITION (P200709_2)
);
查询表上有多少分区
SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'
查询索引信息
select object_name,object_type,tablespace_name,sum(value)
from v$segment_statistics
where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX'
group by object_name,object_type,tablespace_name
order by 4 desc
--显示数据库所有分区表的信息:
select * from DBA_PART_TABLES
--显示当前用户可访问的所有分区表信息:
select * from ALL_PART_TABLES
--显示当前用户所有分区表的信息:
select * from USER_PART_TABLES
--显示表分区信息 显示数据库所有分区表的详细分区信息:
select * from DBA_TAB_PARTITIONS
--显示当前用户可访问的所有分区表的详细分区信息:
select * from ALL_TAB_PARTITIONS
--显示当前用户所有分区表的详细分区信息:
select * from USER_TAB_PARTITIONS
--显示子分区信息 显示数据库所有组合分区表的子分区信息:
select * from DBA_TAB_SUBPARTITIONS
--显示当前用户可访问的所有组合分区表的子分区信息:
select * from ALL_TAB_SUBPARTITIONS
--显示当前用户所有组合分区表的子分区信息:
select * from USER_TAB_SUBPARTITIONS
--显示分区列 显示数据库所有分区表的分区列信息:
select * from DBA_PART_KEY_COLUMNS
--显示当前用户可访问的所有分区表的分区列信息:
select * from ALL_PART_KEY_COLUMNS
--显示当前用户所有分区表的分区列信息:
select * from USER_PART_KEY_COLUMNS
--显示子分区列 显示数据库所有分区表的子分区列信息:
select * from DBA_SUBPART_KEY_COLUMNS
--显示当前用户可访问的所有分区表的子分区列信息:
select * from ALL_SUBPART_KEY_COLUMNS
--显示当前用户所有分区表的子分区列信息:
select * from USER_SUBPART_KEY_COLUMNS
--怎样查询出oracle数据库中所有的的分区表
select * from user_tables a where a.partitioned='YES'
--删除一个表的数据是
truncate table table_name;
--删除分区表一个分区的数据是
alter table table_name truncate partition p5;