一、几个基本概念
表空间以及分区表概念
表空间:是一个或多个数据文件的集合,所有的数据对象都存放在指定的表空间中,主要作用是存储表;
分区表:当表中的数据量不断增大,查询数据的速度就会变慢,应用程序的性能就会下降,这时就应该考虑对表进行分区。
表进行分区后,逻辑上表仍然是一张完整的表,只是将表中的数据在物理上存放到多个表空间(物理文件上),这样查询数据时,不至于每次都扫描整张表。
注意:
1.分区表中,每个分区的逻辑结构必须相同。如:列名、数据类型;
2.分区表中,每个分区的物理存储参数可以不同。如:各个分区所在的表空间;
3.各个分区可以存放在不同的表空间中,但这些表空间所使用的块大小(block_size)必须一致;
4.除了包含LONG以及LONG RAW字段的表无法使用分区外,其他表均可以使用分区,包括含有LOB字段的表;
5.对于应用而言完全透明,分区前后没有变化,不需要进行修改;
二、表分区意义与作用
意义与作用
表分区功能通过改善可管理性、性能和可用性,从而为应用程序带来了极大的好处。 通常,分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务,分区是构建千兆字节数据系统或超高可用性系统的关键工具。
分区功能能够将表、索引或索引组织表进一步细分为片段,这些数据库对象的片段统称为分区;每个分区都有各自名称,可以定义各自的存储特性。 从DBA角度来讲,一个分区后的对象有多个段,这些段可以集中式管理,亦可单独管理,这就使DBA在管理分区后的对象时有相当大的灵活性。从应用角度来看 分区后的表与非分区表完全相同,使用 SQL DML 命令访问分区后的表时,无需任何修改。
表分区场景
1、表的大小超过2GB;
2、表中包含历史数据,新的数据被增加都新的分区中;
三、表分区的优点与局限性
表分区有以下优点
1.改善查询性能:对于用户的请求,只检索需要的分区;
2.增强可用性:某个分区出现故障不会影响其它分区表使用;
3.维护方便:可以在分区级别,针对单独的分区,进行索引的维护、数据的加载以及备份恢复等操作,大大降低了维护时长;
4.均衡I/O:可以把不同的分区映射到磁盘以平衡I/O,改善整个系统性能;
表分区局限性
已经存在的表没有方法可以直接转化为分区表,在oracle中提供了在线重定义表的功能。
四、分区键
分区键就是决定表中的数据行,属于哪一个分区的一组数据列。在执行DML操作时,数据库会根据分区键选择分区;
五、常用分区方法及使用方法
A.范围分区(range partition)
范围分区将数据基于范围映射到每一个分区,这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的,并且分区键经常采用日期;
范围分区特点:
1.范围分区主要依据分区键定义时给出的键值范围,根据实际的取值,进行分区的选择,进而在相应分区中存储数据;
2.范围分区比较合适存在以数字为导向,方便进行数字范围划分的数据列。如:员工表的雇佣日期列、工资列等;
3.范围分区的数据分布可能不均匀;
范围分区定义规则
1、在定义范围分区时,每个分区定义必须使用 values less than(value)子句。其中(value)表示该分区的上限值;
2、在定义范围分区时,最后一个分区可以是values less than(maxvalue)。其中(maxvalue)表示该分区存储高于其他分区上限值的数据行;
e.g
CREATE TABLE CUSTOMER
(
CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,
FIRST_NAME VARCHAR2(30) NOT NULL,
LAST_NAME VARCHAR2(30) NOT NULL,
PHONE VARCHAR2(15) NOT NULL,
EMAIL VARCHAR2(80),
STATUS CHAR(1)
)
PARTITION BY RANGE (CUSTOMER_ID)
(
PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01,
PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02
)
例二:按时间划分
CREATE TABLE ORDER_ACTIVITIES
(
ORDER_ID NUMBER(7) NOT NULL,
ORDER_DATE DATE,
TOTAL_AMOUNT NUMBER,
CUSTOTMER_ID NUMBER(7),
PAID CHAR(1)
)
PARTITION BY RANGE (ORDER_DATE)
(
PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01- MAY -2003','DD-MON-YYYY')) TABLESPACEORD_TS01,
PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE ORD_TS02,
PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) TABLESPACE ORD_TS03
)
例三:MAXVALUE
CREATE TABLE RangeTable
(
idd INT PRIMARY KEY ,
iNAME VARCHAR(10),
grade INT
)
PARTITION BY RANGE (grade)
(
PARTITION part1 VALUES LESS THEN (1000) TABLESPACE Part1_tb,
PARTITION part2 VALUES LESS THEN (MAXVALUE) TABLESPACE Part2_tb
);
B、列表分区(list partition)
列表分区特点:
1.列表分区主要依据分区键定义时给出的取值列表,根据实际的取值,进行分区的选择,进而在相应分区中存储数据;
2.列表分区比较合适列唯一取值有限,且较为固定的数据列。如:员工表的部门列;
3.列表分区的数据分布可能不均匀。
列表分区定义规则:
1、在定义范围分区时,每个分区定义必须使用 values('value01','value02'....)子句。表示该分区存储包含相关value值的数据行;
2、在定义范围分区时,最后一个分区可以是values(DEFAULT)。表示该分区存储未在其他分区定义的数据行;
e.g
CREATE TABLE PROBLEM_TICKETS
(
PROBLEM_ID NUMBER(7) NOT NULL PRIMARY KEY,
DESCRIPTION VARCHAR2(2000),
CUSTOMER_ID NUMBER(7) NOT NULL,
DATE_ENTERED DATE NOT NULL,
STATUS VARCHAR2(20)
)
PARTITION BY LIST (STATUS)
(
PARTITION PROB_ACTIVE VALUES ('ACTIVE') TABLESPACE PROB_TS01,
PARTITION PROB_INACTIVE VALUES ('INACTIVE') TABLESPACE PROB_TS02
例二
CREATE TABLE ListTable
(
id INT PRIMARY KEY ,
name VARCHAR (20),
area VARCHAR (10)
)
PARTITION BY LIST (area)
(
PARTITION part1 VALUES ('guangdong','beijing') TABLESPACE Part1_tb,
PARTITION part2 VALUES ('shanghai','nanjing') TABLESPACE Part2_tb
);
)
C.HASH分区(hash partition)
HASH分区特点:
1.HASH分区主要通过hash算法确定相应数据行应该被存放到哪个分区中;
2.HASH分区比较适合列差异值很多的数据列;
HASH分区的注意事项:
1.对于HASH分区,无法控制一条数据在分区间的具体分布。具体分布由hash算法决定;
2.对于HASH分区,如果更改分区的数量,将导致所有数据在分区间的重新分布;
HASH分区定义规则:
在定义HASH分区时,其分区数量应为2的N次方,如:2,4,8,16等;
e.g
CREATE TABLE HASH_TABLE
(
COL NUMBER(8),
INF VARCHAR2(100)
)
PARTITION BY HASH (COL)
(
PARTITION PART01 TABLESPACE HASH_TS01,
PARTITION PART02 TABLESPACE HASH_TS02,
PARTITION PART03 TABLESPACE HASH_TS03
)
简写:
CREATE TABLE emp
(
empno NUMBER (4),
ename VARCHAR2 (30),
sal NUMBER
)
PARTITION BY HASH (empno) PARTITIONS 8
STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);
D.组合分区(composite partition)
组合分区的特点:
1.组合分区中,主要通过在不同列上,使用“范围分区”、“列表分区”以及“HASH分区”不同组合方式,进而实现组合分区;
2.组合分区中,分区本身没有相应的segment,可以认为是一个逻辑容器,只有子分区拥有实际segment,用于存放数据;
组合分区的注意事项:
1.在oracle 11g以前,组合分区主要有两种组合方式:“RANGE-HASH”以及“RANGE-LIST”;
- 在oracle 11g以后,组合分区新增了四种组合方式:“RANGE-RANGE”、“LIST-RANGE”、“LIST-HASH”以及“LIST-LIST”;
e.g1
CREATE TABLE SALES
(
PRODUCT_ID VARCHAR2(5),
SALES_DATE DATE,
SALES_COST NUMBER(10),
STATUS VARCHAR2(20)
)
PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)
(
PARTITION P1 VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACE rptfact2009
(
SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,
SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009
),
PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009 (
SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,
SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009
)
)
e.g 2
create table dinya_test
(
transaction_id number primary key,
item_id number(8) not null,
item_description varchar2(300),
transaction_date date
)
partition by range(transaction_date)subpartition by hash(transaction_id) subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)
(
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)
);
注意:对于同一张组合分区表而言,子分区的名字不能相同,即使不在相同的分区里;
五、有关表分区的一些维护性操作
1、添加分区
以下代码给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');
2、删除分区
以下代码删除了P3表分区:
ALTER TABLE SALES DROP PARTITION P3;
在以下代码删除了P4SUB1子分区:
ALTER TABLE SALES DROP SUBPARTITION P4SUB1;
注意:如果删除的分区是表中唯一的分区,那么此分区将不能被删除,要想删除此分区,必须删除表。
3、截断分区
截断某个分区是指删除某个分区中的数据,并不会删除分区,也不会删除其它分区中的数据。当表中即使只有一个分区时,也可以截断该分区。通过以下代码截断分区:
ALTER TABLE SALES TRUNCATE PARTITION P2;
通过以下代码截断子分区:
ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;
4、合并分区
合并分区是将相邻的分区合并成一个分区,结果分区将采用较高分区的界限,值得注意的是,不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并:
ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;
5、拆分分区
拆分分区将一个分区拆分两个新分区,拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。
ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);
6、接合分区(coalesca)
结合分区是将散列分区中的数据接合到其它分区中,当散列分区中的数据比较大时,可以增加散列分区,然后进行接合,值得注意的是,接合分区只能用于散列分区中。通过以下代码进行接合分区:
ALTER TABLE SALES COALESCA PARTITION;
7、重命名表分区
以下代码将P21更改为P2
ALTER TABLE SALES RENAME PARTITION P21 TO P2;
8、相关查询
跨分区查询
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;