此文从以下几个方面来整理关于分区表的概念及操作:
表空间及分区表的概念
表分区的具体作用
表分区的优缺点
表分区的几种类型及操作方法
对表分区的维护性操作
() 表空间及分区表的概念
表空间
是一个或多个数据文件的集合所有的数据对象都存放在指定的表空间中但主要存放的是表 所以称作表空间
分区表
当表中的数据量不断增大查询数据的速度就会变慢应用程序的性能就会下降这时就应该考虑对表进行分区表进行分区后逻辑上表仍然是一张完整的表只是将表中的数据在物理上存放到多个表空间(物理文件上)这样查询数据时不至于每次都扫描整张表
( )表分区的具体作用
Oracle的表分区功能通过改善可管理性性能和可用性从而为各式应用程序带来了极大的好处通常分区可以使某些查询以及维护操作的性能大大提高此外分区还可以极大简化常见的管理任务分区是构建千兆字节数据系统或超高可用性系统的关键工具
分区功能能够将表索引或索引组织表进一步细分为段这些数据库对象的段叫做分区每个分区有自己的名称还可以选择自己的存储特性从数据库管理员的角度来看一个分区后的对象具有多个段这些段既可进行集体管理也可单独管理这就使数据库管理员在管理分区后的对象时有相当大的灵活性但是从应用程序的角度来看分区后的表与非分区表完全相同使用 SQL DML 命令访问分区后的表时无需任何修改
什么时候使用分区表
表的大小超过GB
表中包含历史数据新的数据被增加都新的分区中
()表分区的优缺点
表分区有以下优点
改善查询性能对分区对象的查询可以仅搜索自己关心的分区提高检索速度
增强可用性如果表的某个分区出现故障表在其他分区的数据仍然可用
维护方便如果表的某个分区出现故障需要修复数据只修复该分区即可
均衡I/O可以把不同的分区映射到磁盘以平衡I/O改善整个系统性能
缺点
分区表相关已经存在的表没有方法可以直接转化为分区表不过 Oracle 提供了在线重定义表的功能
()表分区的几种类型及操作方法
一范围分区
范围分区将数据基于范围映射到每一个分区这个范围是你在创建分区时指定的分区键决定的这种分区方式是最为常用的并且分区键经常采用日期举个例子你可能会将销售数据按照月份进行分区
当使用范围分区时请考虑以下几个规则
每一个分区都必须有一个VALUES LESS THEN子句它指定了一个不包括在该分区中的上限值分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中
所有分区除了第一个都会有一个隐式的下限值这个值就是此分区的前一个分区的上限值
在最高的分区中MAXVALUE被定义MAXVALUE代表了一个不确定的值这个值高于其它分区中的任何分区键的值也可以理解为高于任何分区中指定的VALUE LESS THEN的值同时包括空值
例一
假设有一个CUSTOMER表表中有数据行我们将此表通过CUSTOMER_ID进行分区每个分区存储行我们将每个分区保存到单独的表空间中这样数据文件就可以跨越多个物理磁盘下面是创建表和分区的代码如下
CREATE TABLE CUSTOMER
(
CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY
FIRST_NAME VARCHAR() NOT NULL
LAST_NAME VARCHAR() NOT NULL
PHONE VARCHAR() NOT NULL
EMAIL VARCHAR()
STATUS CHAR()
)
PARTITION BY RANGE (CUSTOMER_ID)
(
PARTITION CUS_PART VALUES LESS THAN () TABLESPACE CUS_TS
PARTITION CUS_PART VALUES LESS THAN () TABLESPACE CUS_TS
)
例二按时间划分
CREATE TABLE ORDER_ACTIVITIES
(
ORDER_ID NUMBER() NOT NULL
ORDER_DATE DATE
TOTAL_AMOUNT NUMBER
CUSTOTMER_ID NUMBER()
PAID CHAR()
)
PARTITION BY RANGE (ORDER_DATE)
(
PARTITION ORD_ACT_PART VALUES LESS THAN (TO_DATE( MAY DDMONYYYY)) TABLESPACEORD_TS
PARTITION ORD_ACT_PART VALUES LESS THAN (TO_DATE(JUNDDMONYYYY)) TABLESPACE ORD_TS
PARTITION ORD_ACT_PART VALUES LESS THAN (TO_DATE(JULDDMONYYYY)) TABLESPACE ORD_TS
)
例三MAXVALUE
CREATE TABLE RangeTable
(
idd INT PRIMARY KEY
iNAME VARCHAR()
grade INT
)
PARTITION BY RANGE (grade)
(
PARTITION part VALUES LESS THEN () TABLESPACE Part_tb
PARTITION part VALUES LESS THEN (MAXVALUE) TABLESPACE Part_tb
);
二列表分区
该分区的特点是某列的值只有几个基于这样的特点我们可以采用列表分区
例一
CREATE TABLE PROBLEM_TICKETS
(
PROBLEM_ID NUMBER() NOT NULL PRIMARY KEY
DESCRIPTION VARCHAR()
CUSTOMER_ID NUMBER() NOT NULL
DATE_ENTERED DATE NOT NULL
STATUS VARCHAR()
)
PARTITION BY LIST (STATUS)
(
PARTITION PROB_ACTIVE VALUES (ACTIVE) TABLESPACE PROB_TS
PARTITION PROB_INACTIVE VALUES (INACTIVE) TABLESPACE PROB_TS
例二
CREATE TABLE ListTable
(
id INT PRIMARY KEY
name VARCHAR ()
area VARCHAR ()
)
PARTITION BY LIST (area)
(
PARTITION part VALUES (guangdongbeijing) TABLESPACE Part_tb
PARTITION part VALUES (shanghainanjing) TABLESPACE Part_tb
);
)
三散列分区
这类分区是在列值上使用散列算法以确定将行放入哪个分区中当列的值没有合适的条件时建议使用散列分区
散列分区为通过指定分区编号来均匀分布数据的一种分区类型因为通过在I/O设备上进行散列分区使得这些分区大小一致
例一
CREATE TABLE HASH_TABLE
(
COL NUMBER()
INF VARCHAR()
)
PARTITION BY HASH (COL)
(
PARTITION PART TABLESPACE HASH_TS
PARTITION PART TABLESPACE HASH_TS
PARTITION PART TABLESPACE HASH_TS
)
简写
CREATE TABLE emp
(
empno NUMBER ()
ename VARCHAR ()
sal NUMBER
)
PARTITION BY HASH (empno) PARTITIONS
STORE IN (empempempempempempempemp);
hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中hash算法中最重要的是hash函数Oracle中如果你要使用hash分区只需指定分区的数量即可建议分区的数量采用的n次方这样可以使得各个分区间数据分布更加均匀
四组合范围散列分区
这种分区是基于范围分区和列表分区表首先按某列进行范围分区然后再按某列进行列表分区分区之中的分区被称为子分区
CREATE TABLE SALES
(
PRODUCT_ID VARCHAR()
SALES_DATE DATE
SALES_COST NUMBER()
STATUS VARCHAR()
)
PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)
(
PARTITION P VALUES LESS THAN(TO_DATE(YYYYMMDD))TABLESPACE rptfact
(
SUBPARTITION PSUB VALUES (ACTIVE) TABLESPACE rptfact
SUBPARTITION PSUB VALUES (INACTIVE) TABLESPACE rptfact
)
PARTITION P VALUES LESS THAN (TO_DATE(YYYYMMDD)) TABLESPACE rptfact
(
SUBPARTITION PSUB VALUES (ACTIVE) TABLESPACE rptfact
SUBPARTITION PSUB VALUES (INACTIVE) TABLESPACE rptfact
)
)
五复合范围散列分区
这种分区是基于范围分区和散列分区表首先按某列进行范围分区然后再按某列进行散列分区
create table dinya_test
(
transaction_id number primary key
item_id number() not null
item_description varchar()
transaction_date date
)
partition by range(transaction_date)subpartition by hash(transaction_id) subpartitions store in (dinya_spacedinya_spacedinya_space)
(
partition part_ values less than(to_date(yyyymmdd))
partition part_ values less than(to_date(yyyymmdd))
partition part_ values less than(maxvalue)
);
()有关表分区的一些维护性操作
一添加分区
以下代码给SALES表添加了一个P分区
ALTER TABLE SALES ADD PARTITION P VALUES LESS THAN(TO_DATE(YYYYMMDD));
注意以上添加的分区界限应该高于最后一个分区界限
以下代码给SALES表的P分区添加了一个PSUB子分区
ALTER TABLE SALES MODIFY PARTITION P ADD SUBPARTITION PSUB VALUES(COMPLETE);
二删除分区
以下代码删除了P表分区
ALTER TABLE SALES DROP PARTITION P;
在以下代码删除了PSUB子分区
ALTER TABLE SALES DROP SUBPARTITION PSUB;
注意如果删除的分区是表中唯一的分区那么此分区将不能被删除要想删除此分区必须删除表
三截断分区
截断某个分区是指删除某个分区中的数据并不会删除分区也不会删除其它分区中的数据当表中即使只有一个分区时也可以截断该分区通过以下代码截断分区
ALTER TABLE SALES TRUNCATE PARTITION P;
通过以下代码截断子分区
ALTER TABLE SALES TRUNCATE SUBPARTITION PSUB;
四合并分区
合并分区是将相邻的分区合并成一个分区结果分区将采用较高分区的界限值得注意的是不能将分区合并到界限较低的分区以下代码实现了P P分区的合并
ALTER TABLE SALES MERGE PARTITIONS PP INTO PARTITION P;
五拆分分区
拆分分区将一个分区拆分两个新分区拆分后原来分区不再存在注意不能对HASH类型的分区进行拆分
ALTER TABLE SALES SBLIT PARTITION P AT(TO_DATE(YYYYMMDD)) INTO (PARTITION PPARTITION P);
六接合分区(coalesca)
结合分区是将散列分区中的数据接合到其它分区中当散列分区中的数据比较大时可以增加散列分区然后进行接合值得注意的是接合分区只能用于散列分区中通过以下代码进行接合分区
ALTER TABLE SALES COALESCA PARTITION;
七重命名表分区
以下代码将P更改为P
ALTER TABLE SALES RENAME PARTITION P TO P;
八相关查询
跨分区查询
select sum( *) from
(select count(*) cn from t_table_SS PARTITION (P_)
union all
select count(*) cn from t_table_SS PARTITION (P_)
);
查询表上有多少分区
SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME=tableName
查询索引信息
select object_nameobject_typetablespace_namesum(value)
from v$segment_statistics
where statistic_name IN (physical readsphysical writelogical reads)and object_type=INDEX
group by object_nameobject_typetablespace_name
order by 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 apartitioned=YES
删除一个表的数据是
truncate table table_name;
删除分区表一个分区的数据是
alter table table_name truncate partition p;
