分区应用详解
一、概述：
1、分区：将表数据划分成更小的子集。
经验：在一个表的数据超过2000万条或占用2G 空间时，建议建立分区表。2、好处：1）提高可用性。
如100GB 的表，如果划分成2G 的50个分区，只要查询的数据所处的分区online，而其他的分区可以是offline，数据库依然工作正常。2）减轻管理负担。
对分区数据的维护，比如删除、更新、分析、数据恢复等，可以以分区为单位进行维护。
3）提高DML 和查询操作性能。
利用CBO 的分区排除机制，跳过未含有相关数据的表分区，减少I/O。分区表使用CBO，CBO 具有很好的“分区意识能力”，直接排除不属于查询结果的分区。（注：分区键列上同样不能应用函数，否则不会使用索引）
对于分区的表，应尽可能的利用分区键作为查询条件，不然会执行full table scan，会比不分区的全表扫描花费更多的时间，失去分区的意义。3、分区信息可查询：select *from user_tab_partitions;
（比如小区表，可以按小时分区，小时里再按hash 分区防止分区不均匀，此时再对各分区进行truncate 等维护，可提高性能）
二、操作：
1、创建分区
分区可分为如下几种：（范围分区，hash 分区，列表分区，范围-hash 分区，范围-列表分区等）
A
l l R i g
h
t s
R e
s
e r v
e
d
A、范围分区
使用列值的范围来确定一个数据行被插入到那个分区中。1）分区键最多可16列，一个表最多可65636个分区。2）分区范围中不能有任何空隙。
3）每个分区中，等于上限的值都是存储在上一个分区中的。
4）less then 子句指定的值，必须是一个用RPAD 函数填充过的值、日期（必须包含世纪）或常数。
5）分区表不能包含带有long,long raw 的数据列。
6）试图插入一个无法放入任一分区的数据，都导致ORA-14400错误。7）通过更新数据使数据在分区间移动不被允许，除非创建表的时候指定了enable row movement 子局。
注：如果插入的数据导致分区不均匀，应考虑使用散列分区。例：
create table myrange (empno number(4),ename varchar2(13),hiredate date,
sale number(10))
PARTITION BY RANGE (hiredate)
(PARTITION p_2000VALUES LESS THAN (to_date('2001-1-1','yyyy-mm-dd'))TABLESPACE users,
PARTITION p_2001VALUES LESS THAN (to_date('2002-1-1','yyyy-mm-dd')),PARTITION p_other VALUES LESS THAN (MAXVALUE))
A
l l R i g
h
t s
R e
s
e r v
e
d
SQL>select *from myrange partition(p_2000);
B、列表分区
类似范围分区，区别是它基于一组指定的值，范围分区是基于一个范围。试图插入一个无法放入任一分区的数据，都导致ORA-14400错误。create table mylist (empno number(4),ename varchar2(13),hiredate date,sale number(10))
PARTITION BY LIST (ename)
(PARTITION p_manager VALUES ('KING','CLARK','SCOTT'),PARTITION p_boss VALUES ('TINA'),
PARTITION p_CLARK VALUES ('SMITH','ALLEN','WARD','JONES','MARTIN'))
insert into mylist
select empno,ename,hiredate,sal from scott.emp
where ename='KING';
C、散列分区
使用一个散列算法来分配数据被插入到那个分区中，一般分配很均匀，对于序数的列很适合。
1）分区键应具有很高基数。
2）如果是唯一键分区，效果最好。如果是范围查找，不会有性能提升。
A
l l R i g
h
t s
R e
s
e r v
e
d
3）通过更新数据使数据在分区间移动不被允许。4）总分区数，应该是2的整数倍。
举例：
create table myhash1(empno number(4),ename varchar2(13),hiredate date)
PARTITION BY HASH (empno)(PARTITION h1,PARTITION h2,PARTITION h3)
create table myhash2(empno number(4),
ename varchar2(13),hiredate date)
PARTITION BY HASH (empno)PARTITIONS 3
STORE(USERS，USERS，USERS)
D、组合分区
创建范围分区，在该分区内又再创建散列分区。（一般用在需要范围分区，而范围分区又不均匀的情况下）
A
l l R i g
h
t s
R e
s
e r v
e
d
1）分区是逻辑结构，表数据被存储在子分区级上。
2）组合分区对：分区级的、历史的、日期的，查询十分好使。3）组合分区对子分区级的并行操作也十分有用。4）分区级的连接操作通过使用组合的局部索引支持。create table myrh (empno number(4),ename varchar2(13),hiredate date,sale number(10))
PARTITION BY RANGE (hiredate)SUBPARTITION BY HASH (empno)SUBPARTITIONS 2
STORE IN (users,users)(
PARTITION p_2000VALUES LESS THAN (to_date('2001-1-1','yyyy-mm-dd')),PARTITION p_2001VALUES LESS THAN (to_date('2002-1-1','yyyy-mm-dd')),
PARTITION p_other VALUES LESS THAN (MAXVALUE))
三、索引<一>、索引分区
分两类：全局与局部索引，前缀与非前缀索引。
A
l l R i g
h
t s
R e
s
e r v
e
d
●
全局与局部索引：与该索引的分区结构是否与被索引基础表结构匹配有关系。
局部索引：索引中的分区与基础表的分区逐个匹配，也称分区索引。即1.分区索引就是在所有每个区上单独创建索引，它能自动维护
2.在drop 或truncate 某个分区时不影响该索引的其他分区索引的使用，
也就是索引不会失效，维护起来比较方便，但是在查询性能稍微有点影响3.可以在任何分区上创建局部分区索引
4.创建后，Oracle 自动维护表分区与索引分区之间的关系
5.分区上的位图索引必须是局部的索引举例：
create index myemp_idx_t on myemp(hiredate)local (
partition idx_1tablespace idxtbs1,partition idx_2tablespace idxtbs2,partition idx_3tablespace idxtbs3);
●全局索引：索引中的分区与基础表的分区数不逐个匹配。即：1、全局索引就是在全表上创建索引
2、它可以创建自己的分区，可以和分区表的分区不一样，也就是它是独立的
索引。
3、在drop 或truncate 某个分区时需要重建索引alter index idx_xx
rebuild，
4、也可以alter table table_name drop partition partition_name update
global indexes;实现，但是要花很长时间在重建索引上。
A
l l R i g
h
t s
R e
s
e r v
e
d
5、可以通过查询user_indexes、user_part_indexes 和
user_ind_partitions 视图来查看索引是否有效。
6、可以在任何分区上创建全局分区索引，但它自身必须是范围的。
7、最高分区必须以MAXVALUE 参数来定义。
8、使用global 创建与表分区一样多的索引，Oracle 也不会认为是局部索引。举例：
create index myemp_idx_t on myemp()global partition by range(sal)(
partition idx_1values less than (1000)tablespace idxtbs1,partition idx_2values less than (5000)tablespace idxtbs2,partition idx_3values less than (maxvalue)tablespace idxtbs3);
global 子句允许指定索引的范围值，这个范围值为索引字段的范围值.create index my_idx_t on dinya_test(empno);
<二>、前缀与非前缀索引：
与该索引是否有分区键及分区键出现在索引结构内的位置有关系。
1、前缀索引：索引的最左列与分区键相同。可以是唯一的，也可以不是唯一的。前缀局部索引：
create index myrange_lp_idx on myrange (hiredate )local;
此时默认与基础表分区数一样。以hiredate 范围分区(这里hiredate 是分区键)。
前缀全局索引：
A
l l R i g
h
t s
R e
s
e r v
e
d
2、非前缀索引：
索引的最左列与分区键不相同。可以是唯一的，也可以不是唯一的。非前缀局部索引：(不存在全局非前缀索引)
SQL>create index myrange_lp_idx on myrange (empno)local;
分区索引不能作为整体一次重建。
alter index local2_alarminfo rebuild partition l_ind2_alarminfo_050915;
注：局部前缀索引比局部非前缀索引具有更好的性能，因为它们将检查的索引的数目最小化。
注：在单表查询中，局部非前缀索引可能增加可用性，也更加实用。例如表T （a，b）按a 区间分区，若在b 上建立本地索引，则当某个分区离线，仅查询b 的某个值时，该索引可用，而索引（a，b）不可用；
举例：
create table partitioned_table (a int,b int )
partition by range(a)(
partition part_1values less than (2),partition part_2values less than (3)
A
l l R i g
h
t s
R e
s
e r v
e
d
SQL>create index local_prefixed on partitioned_table(a,b)local;
SQL>create index local_nonprefixed on partitioned_table(b)local;
四、管理分区●收集分区的统计数据
●可以针对段、分区或子分区级上收集。●
例（收集分区表和分区索引）：
SQL>execute
dbms_stats.gather_table_stats(ownname=>’APPS’,tabname=>’STUDENT_H ISTORY’,partname=>’P_1998’,granularity=>’PARTITION’);
SQL>execute
dbms_stats.gather_index_stats(ownname=>’SYS’,indname=>’STUDENT_HI STORY_GP_IDX’,partname=>’P_200n’);●
查询分区信息的视图DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS DBA_LOB_PARTITIONS
DBA_LOB_SUBPARTITIONS DBA_PART_COL_STATISTICS DBA_PART_HISTOGRAMS DBA_PART_INDEXES DBA_PART_KEY_COLUMNS DBA_PART_LOBS
A
l l R i g
h
t s
R e
s
e r v
e
d
DBA_PART_TABLES
DBA_SUBPART_COL_STATISTICS DBA_SUBPART_HISTOGRAMS DBA_SUBPART_KEY_COLUMNS DBA_TAB_PARTITIONS DBA_TAB_SUBPARTITIONS ●
增加分区
例：给范围分区增加分区create table myrange (empno number(4),ename varchar2(13),hiredate date,sale number(10))
PARTITION BY RANGE (hiredate)
(PARTITION p_2000VALUES LESS THAN (to_date('2001-1-1','yyyy-mm-dd'))TABLESPACE users,
PARTITION p_2001VALUES LESS THAN (to_date('2002-1-1','yyyy-mm-dd')));
alter table myrange add partition p_2002values less
than(to_date('2003-1-1','yyyy-mm-dd'))tablespace mytbs1
注意：可以不断的增加分区，但values less than ()的值不能比已经存在的分区的值小。
可以用maxvalue 关键字指定列最大值。●
truncate 分区
A
l l R i g
h
t s
R e
s
e r v
e
d
例：
alter table myrange truncate partition p_2001;
将分区数据清空。不同于delete。●
丢弃分区
例：
alter table myrange drop partition p_2001;将丢弃此分区的数据，不会影响其他分区。●
交换分区
（可应用于大量数据插入中，先把数据临时插到一个临时表，再把该临时表与一个分区交换，需要考虑执行时间，未测试）注意：
1）是将某一个分区于某一个表的数据交换。2）临时表结构和分区表结构必须一样。3）临时表的数据必须符合分区规则。
4）若分区里有局部分区索引，此时将不可用，必须重建。（除数据外，包括索引一起交换的除外---includeing indexes）
例：创建分区表create table mytest (empno number(4),ename varchar2(10),hiredate date,sale number(7,2))
A
l l R i g
h
t s
R e
s
e r v
e
d
PARTITION BY RANGE (hiredate)
(PARTITION p_2000VALUES LESS THAN (to_date('2001-1-1','yyyy-mm-dd'))TABLESPACE users,
PARTITION p_2001VALUES LESS THAN (to_date('2002-1-1','yyyy-mm-dd')),PARTITION p_other VALUES LESS THAN (MAXVALUE))
创建普通临时表
create table mytemp as select empno,ename,hiredate,sal sale from emp where 1=2;
插入数据
insert into mytemp select empno,ename,hiredate,sal from emp;
交换分区
alter table mytest exchange partition p_2000with table mytemp
●移动分区
例：
SQL>alter table myrange move partition p_2001tablespace mytbs2;注意：此时索引需要重建。●
修改分区
例：
A
l l R i g
h
t s
R e
s
e r v
e
d
SQL>alter table myrange modify partition p_2000storage(pctincrease 20);修改分区存储参数。●
重命名分区
例：
SQL>alter table myrange rename partition p_oldname to p_newname;
●分割分区
例：
创建一个分区
SQL>alter table C_PERF_CELLCIRCUIT add partition part25VALUES LESS THAN 25tablespace D_PF_CELL_DBS25;分割原来的分区
SQL>alter table C_PERF_CELLCIRCUIT split partition part26at (25)
into (partition part25,(partition part26);
前一个分区小于at 指定的值，后一个分区大于at 指定的值。At 指定的值不能等于本分区和上一个分区的上限值。如果被分割的分区有数据，则local 索引需要重建。●
分区的逻辑备份与恢复
%exp username/passwd file=xxxx.dmp tables=(p_nor_mcbtss2:p_nor_mcbtss2_part04,p_nor_mcbtss2:p_nor_mcbtss2_part05)
A
l l R i g
h
t s
R e
s
e r v
e
d
%imp username/passwd file=xxxx.dmp tables=(p_nor_mcbtss2:
p_nor_mcbtss2_part04,p_nor_mcbtss2:p_nor_mcbtss2_part05)ignore=y
A l l R
i g
h t
s
R e
s e
r v
e d
oracle 建立分区表 从上次在亚旭培训的时候，我和dba讨论一次我开发系统中为了一张表不是非常的大，采用了动态sql创建多个部门的表，然后存取相应的数据，从而解决了一张表过大的问题。当时dba和我说了分区表，我第一感觉，如果当时我知道数据库还有这种表，那我当时开发起来应该轻松的多，后来就一直有个想法，去了解分区表，因为最近自己一直都比较忙，被琐事所困，今天晚上终于抽出了点时间，了解了相关的知识，并做了400多w条数据的一个分区表的测试。 一.范围分区 范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。 1 2 3 4 5 6 7 8 9 10 11 12 --例一取值范围： 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) (
13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01, PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02, PARTITION CUS_PART3 VALUES LESS THEN(MAXVALUE) 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')) TABLESPACE ORD_TS01, PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE ORD_TS02, PARTITION ORD_ACT_PART03 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) TABLESPACE ORD_TS03
下载的，写的非常好，给大家分享下。 什么时候使用分区： 1、大数据量的表，比如大于2GB。一方面2GB文件对于32位os是一个上限，另外备份时间长。 2、包括历史数据的表，比如最新的数据放入到最新的分区中。典型的例子：历史表，只有当前月份的数据可以被修改，而其他月份只能read-only ORACLE只支持以下分区：tables, indexes on tables, materialized views, and indexes on materialized views 分区对SQL和DML是透明的(应用程序不必知道已经作了分区)，但是DDL 可以对不同的分区进行管理。 不同的分区之间必须有相同的逻辑属性，比如共同的表名，列名，数据类型，约束; 但是可以有不同的物理属性，比如pctfree, pctused, and tablespaces. 分区独立性：即使某些分区不可用，其他分区仍然可用。 最多可以分成64000个分区，但是具有LONG or LONG RAW列的表不可以，但是有CLOB or BLOB列的表可以。 可以不用to_date函数，比如： alter session set nls_date_format='mm/dd/yyyy'; CREATE TABLE sales_range (salesman_id NUMBER(5), salesman_name VARCHAR2(30), sales_amount NUMBER(10), sales_date DATE) PARTITION BY RANGE(sales_date) ( PARTITION sales_jan2000 VALUES LESS THAN('02/01/2000'),
详解ORACLE簇表、堆表、IOT表、分区表 簇和簇表 簇其实就是一组表，是一组共享相同数据块的多个表组成。将经常一起使用的表组合在一起成簇可以提高处理效率。 在一个簇中的表就叫做簇表。建立顺序是：簇→簇表→数据→簇索引 1、创建簇的格式 CREATE CLUSTER cluster_name (column date_type [,column datatype]...) [PCTUSED 40 | integer] [PCTFREE 10 | integer] [SIZE integer] [INITRANS 1 | integer] [MAXTRANS 255 | integer] [TABLESPACE tablespace] [STORAGE storage] SIZE：指定估计平均簇键，以及与其相关的行所需的字节数。 2、创建簇 create cluster my_clu (deptno number) pctused60 pctfree10 size1024 tablespace users storage( initial128k next128k minextents2 maxextents20
);
3、创建簇表 create table t1_dept( deptno number, dname varchar2(20) ) cluster my_clu(deptno); create table t1_emp( empno number, ename varchar2(20), birth_date date, deptno number ) cluster my_clu(deptno); 4、为簇创建索引 create index clu_index on cluster my_clu; 注：若不创建索引，则在插入数据时报错：ORA-02032: clustered tables cannot be used before the cluster index is built 管理簇 使用ALTER修改簇属性(必须拥有ALTER ANY CLUSTER的权限) 1、修改簇属性 可以修改的簇属性包括： * PCTFREE、PCTUSED、INITRANS、MAXTRANS、STORAGE * 为了存储簇键值所有行所需空间的平均值SIZE * 默认并行度
Oracle 分区的概念 分区是指将巨型的表或索引分割成相对较小的、可独立管理的部分，这些独立的部分称为原来表或索引的分区。分区后的表与未分区的表在执行查询语句或其他DML语句时没有任何区别，一旦进行分区之后，还可以使用DDL语句对每个单独的分区进行操作。因此，对巨型表或者索引进行分区后，能够简化对它们的管理和维护操作，而且分区对于最终用户和应用程序是完全透明的。 在对表进行分区后，每一个分区都具有相同的逻辑属性。例如，各个分区都具有相同的字段名、数据类型和约束等。但是各个分区的物理属性可以不同，例如，各个分区可以具有不同的存储参数，或者位于不同的表空间中。 如果对表进行了分区，表中的每一条记录都必须明确地属于某一个分区。记录应当属于哪一个分区是记录中分区字段的值决定的。分区字段可以是表中的一个字段或多个字段的组合，这时在在创建分区表时确定。在对分区表执行插入、删除或更新等操作时，Oracle会自动根据分区字段的值来选择所操用的分区。分区字段由1～16个字段以某种顺序组成，但不能包含ROWID等伪列，也不能包含全为NULL值的字段。 图10-1显示了一个典型的分区表。通常在对表进行分区时也会将地对应的索引进行分区，但是未分区的表可以具有分区的索引，而分区的表也可以具有未分区的索引。 索引索引 索引 索引 5月6月7月 未分区的表（分区的索引） 分区的表（分区的索引） 图10-1 分区表与分区索引 一个表可以被分割成任意数目的分区，但如果在表中包含有LONG或LONG RAW类型的字段，则不能对表分区。对于索引组织表而言，虽然也可以分区，但是有如下一些限制： ●索引组织表仅支持范围和散列分区，不能以列表或复合方式对索引组织表进行分 区。 ●分区字段必须是主键字段的一个子集。 ●如果在索引组织表中使用了OVERFLOW子句，溢出存储段将随表的分区进行相 同的分割。 下面给出了应当考虑对表进行分区的一些常见情况： ●如果一个表的大小超过了2GB，通常会对它进行分区。 ●如果要对一表进行并行DML操作，则必须对它进行分区。 ●如果为了平衡硬盘I/O操作，需要将一个表分散存储在不同的表空间中，这时就必 须对表进行分区。 ●如果需要将表的一部分置为只读，而另一部分为可更新的，则必须以它进行分区。
PostgreSQL和oracle表分区对比 PostgreSQL是开源数据库，完全免费，oracle是有强大厂商支持和维护的数据库，把这两个的表分区特性放在一起对比，似乎有些勉强。但对于我们多了解一些特性，在实际开发中可以更好地进行理性选择和快速入手。
总结，数据库的表分区特性优点很多，比如： 1、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用； 3、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可； 4、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。 5、将很少用的数据可以移动到便宜的、慢一些地存储介质上。 这两种数据库的分区表都具有这些优点。 对比来说，Oracle的分区创建和管理更加方便，很多工作是由oracle的内部机制来实现的。postgreSQL的分区表其实是一个个实际存在的数据表，分区的创建和管理都需要我们用语言来控制，增加了应用人员的工作量。 但，由于oracle自身的“侵占式”硬盘存储，对过期数据进行清除时，即便是drop分区表，也不能直接释放硬盘空间，属于“占了就占了”，这个管理起来就比较麻烦，除非对每个分区表都建立各个独立的tablespace，放在独立的物理文件上，删除过期分区表时，可以同时drop tablespace including contents。而postgreSQL在truncate 分区表时，可以直接释放硬盘，会看到硬盘使用率下降了，这一点对硬盘资源紧张时，就非常好了。 两种数据库的分区表使用，各有利弊，但总的来说，比较偏向postgreSQL，毕竟硬盘有限。而且，oracle收费。 Ps，在数据量很大时，任何关系型数据库都有性能上的瓶颈，不属于我们这两种数据库分区表对比的范围了。 以上，是一些使用中的总结，还请达人们指教：）。
Oracle分区技术总结 电信事业部 张雷
一．分区概述： 为了简化数据库大数据量的管理，ORACLE推出了分区选项。分区将表或索引分离在若干不同的表空间上，用分而治之的方法来支撑无限膨胀的大表和索引，从而提高大表和索引在物理一级的可管理性。将它们分割成较小的分区可以改善表和分区的维护、备份、恢复、事务及查询性能。 二．分区的特点： ◆所有的分区的逻辑属性是相同的，但他们的物理属性可以不同。 ◆分区的剪枝（Partition Pruning） Oracle server 可以自动识别分区，根据select 语句所指定的选择条件，只查询 有用的分区。如果语句的条件中对分区字段使用了函数，优化器则不能进行分区剪 枝，但to_date函数除外。 ◆分区的优点 (1) 高可用性：如果表的一个分区由于系统故障而不能使用，表的其余好的分区仍 然可以使用； (2) 减少关闭时间：如果系统故障只影响表的一部分分区，那么只有这部分分区需 要修复，故能比整个大表修复花的时间更少； (3) 维护轻松：对于大型的历史数据表，将其分区，分别管理和方便地添加和删除。； (4) 均衡I/O:可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能； (5) 改善性能：对大表的查询、增加、修改等操作可以分解到表的不同分区来并行 执行，可使运行速度更快； (6) 基于分区的 join 操作，会提高查询性能 (7) 分区对用户透明，最终用户感觉不到分区的存在。 三．分区的方法： ◆Range Partitioning (范围分区)
范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。如根据城市分区，根据时间进行分区等。实现方法就是在CREATE TABLE命令中增加PARTITION BY RANGE子句。 例如：CREATE TABLE UNITELE.BB_ACCOUNT_INFO_T ( ACCOUNT_ID NUMBER(10), CITY_CODE V ARCHAR2(8 BYTE) NOT NULL, CUSTOMER_ID NUMBER(10) NOT NULL, MAIL_SERVICE NUMBER(4), UNIT_COUNT NUMBER(8) DEFAULT 0, REMARK V ARCHAR2(256 BYTE), IF_VALID NUMBER(2) DEFAULT 1, ACCOUNT_FA VOUR_ID NUMBER(8) DEFAULT 0 NOT NULL ) TABLESPACE TS_TAB_BASE PARTITION BY RANGE (CITY_CODE) ( PARTITION PART840 V ALUES LESS THAN ('841'), PARTITION PART_OTHER V ALUES LESS THAN (MAXV ALUE) ); 分区的字段可以是一个列，也可以是多个列。 ★范围分区的特点 a、Range分区特别适合于按时间周期进行数据的存储。日、周、月、年等。 b、数据管理能力强，可以进行数据迁移，数据备份以及数据交换的操作。 c、范围分区的数据可能分布不均匀。 d、范围分区与记录值相关，实施难度和可维护性相对较差。有可能出现一个表分 成上万个分区，还可能出现后期拆分分区，增加分区的操作。 Hash Partitioning（散列分区）;
ORACLE 表分区 表分区的好处和事处理 表分区描述 表分区（partition）:表分区技术是在超大型数据库(VLDB)中将大表及其索引通过分区（patition）的形式分割为若干较小、可管理的小块，并且每一分区可进一步划分为更小的子分区（sub partition）。而这种分区对于应用来说是透明的。Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。 分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。每个分区都是一个独立的段（SEGMENT），可以存放到相同（不同）的表空间中。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用SQL DML 命令访问分区后的表时，无需任何修改。(对于高效率查询是有影响,主要差别是对某一分区数据时行查询时和对整体数据进行查询) 表分区的好处 通过对表进行分区，可以获得以下的好处： 1)增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用； 2)维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可； 3)均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能； 4)改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速
oracle 表分区 partition技术（转） 2009年06月12日星期五 11:23 一下内容转至：https://www.doczj.com/doc/d113582603.html,/blog/166078 一、Oracle分区简介 ORACLE的分区是一种处理超大型表、索引等的技术。分区是一种“分而治之”的技术，通过将大表和索引分成可以管理的小块，从而避免了对每个表作为一个大的、单独的对象进行管理，为大量数据提供了可伸缩的性能。分区通过将操作分配给更小的存储单元，减少了需要进行管理操作的时间，并通过增强的并行处理提高了性能，通过屏蔽故障数据的分区，还增加了可用性。 二、Oracle分区优缺点 优点：λ 增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用； 维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能； 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。缺点：λ 分区表相关：已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能。 三、Oracle分区方法 范围分区：λ 范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。如根据序号分区，根据业务记录的创建日期进行分区等。 Hash分区（散列分区）：λ 散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O 设备上进行散列分区，使得这些分区大小一致。 λ List分区（列表分区）： 当你需要明确地控制如何将行映射到分区时，就使用列表分区方法。与范围分区和散列分区所不同，列表分区不支持多列分区。如果要将表按列分区，那么分区键就只能由表的一个单独的列组成，然而可以用范围分区或散列分区方法进行分区的所有的列,都可以用列表分区方法进行分区。 范围-散列分区（复合分区）：λ 有时候我们需要根据范围分区后，每个分区内的数据再散列地分布在几个表空间中，这样我们就要使用复合分区。复合分区是先使用范围分区，然后在每个分区内再使用散列分区的一种分区方法（注意：先一定要进行范围分区） 范围-列表分区（复合分区）：λ 范围和列表技术的组合，首先对表进行范围分区，然后用列表技术对每个范围分区再次分区。与组合范围-散列分区不同的是，每个子分区的所有内容表示数据的逻辑子集，由适当的范围和列表分区设置来描述。（注意：先一定要进行范围分区） 四、Oracle表分区表操作 --Partitioning 是否为true select * from v$option s order by s.PARAMETER desc
表分区有以下优点： 1、数据查询：数据被存储到多个文件上，减少了I/O负载，查询速度提高。 2、数据修剪：保存历史数据非常的理想。 3、备份：将大表的数据分成多个文件，方便备份和恢复。 4、并行性：可以同时向表中进行DML操作，并行性性能提高。 当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。 Oracle中提供了以下几种表分区： 一、范围分区：这种类型的分区是使用列的一组值，通常将该列成为分区键。示例1：假设有一个CUSTOMER表，表中有数据200000行，我们将此表通过CUSTOMER_ID进行分区，每个分区存储100000行，我们将每个分区保存到单独的表空间中，这样数据文件就可以跨越多个物理磁盘。下面是创建表和分区的代码，如下： 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 ) 在创建表进行分区时，表空间必须先存在，而且建议将不同的分区放入不同的表空间中。 示例2：假设有ORDER_ACTIVITIES表，每6个月对订单进行清理，我们可以按月份对表进行分区，分区代码如下： 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
摘要：本篇文章介绍了ORACLE数据库的新特性—分区管理，并用例子说明使用方法。 关键词：ORACLE，分区 一、分区概述： 为了简化数据库大表的管理，ORACLE8推出了分区选项。分区将表分离在若干不同的表空间上，用分而治之的方法来支撑无限膨胀的大表，给大表在物理一级的可管理性。将大表分割成较小的分区可以改善表的维护、备份、恢复、事务及查询性能。针对当前社保及电信行业的大量日常业务数据，可以推荐使用ORACLE8的该选项。 二、分区的优点： 1 、增强可用性：如果表的一个分区由于系统故障而不能使用，表的其余好的分区仍然可以使用； 2 、减少关闭时间：如果系统故障只影响表的一部分分区，那么只有这部分分区需要修复，故能比整个大表修复花的时间更少； 3 、维护轻松：如果需要重建表，独立管理每个分区比管理单个大表要轻松得多； 4 、均衡I/O:可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能；
5 、改善性能：对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快； 6 、分区对用户透明，最终用户感觉不到分区的存在。 三、分区的管理： 1 、分区表的建立： 某公司的每年产生巨大的销售记录，DBA向公司建议每季度的数据放在一个分区内，以下示范的是该公司1999年的数据(假设每月产生30M的数据)，操作如下： STEP1、建立表的各个分区的表空间： CREATE TABLESPACE ts_sale1999q1 DATAFILE ‘/u1/oradata/sales/sales1999_q1.dat’ SIZE 100M DEFAULT STORAGE (INITIAL 30m NEXT 30m MINEXTENTS 3 PCTINCREASE 0) CREATE TABLESPACE ts_sale1999q2 DATAFILE ‘/u1/oradata/sales/sales1999_q2.dat’ SIZE 100M DEFAULT STORAGE (INITIAL 30m NEXT 30m MINEXTENTS 3 PCTINCREASE 0)
深入学习Oracle分区表及分区索引 关于分区表和分区索引(About Partitioned Tables and Indexes)对于10gR2而言，基本上可以分成几类： ?Range(范围)分区 ?Hash(哈希)分区 ?List(列表)分区 ?以及组合分区：Range-Hash,Range-List。 对于表而言(常规意义上的堆组织表)，上述分区形式都可以应用(甚至可以对某个分区指定compress属性)，只不过分区依赖列不能是lob,long之类数据类型，每个表的分区或子分区数的总数不能超过1023个。 对于索引组织表，只能够支持普通分区方式，不支持组合分区，常规表的限制对于索引组织表同样有效，除此之外呢，还有一些其实的限制，比如要求索引组织表的分区依赖列必须是主键才可以等。 注：本篇所有示例仅针对常规表，即堆组织表! 对于索引，需要区分创建的是全局索引，或本地索引： l 全局索引(global index)：即可以分区，也可以不分区。即可以建range分区，也可以建hash分区，即可建于分区表，又可创建于非分区表上，就是说，全局索引是完全独立的，因此它也需要我们更多的维护操作。 l 本地索引(local index)：其分区形式与表的分区完全相同，依赖列相同，存储属性也相同。对于本地索引，其索引分区的维护自动进行，就是说你add/drop/split/truncate表的分区时，本地索引会自动维护其索引分区。 Oracle建议如果单个表超过2G就最好对其进行分区，对于大表创建分区的好处是显而易见的，这里不多论述why，而将重点放在when以及how。 ORACLE对于分区表方式其实就是将表分段存储，一般普通表格是一个段存储，而分区表会分成多个段，所以查找数据过程都是先定位根据查询条件定位分区范围，即数据在那个分区或那几个内部，然后在分区内部去查找数据，一个分区一般保证四十多万条数据就比较正常了，但是分区表并非乱建立，而其维护性也相对较为复杂一点，而索引的创建也是有点讲究的，这些以下尽量阐述详细即可。 range分区方式，也算是最常用的分区方式，其通过某字段或几个字段的组合的值，从小到大，按照指定的范围说明进行分区，我们在INSERT数据的时候就会存储到指定的分区中。 List分区方式，一般是在range基础上做的二级分区较多，是一种列举方式进行分区，一般讲某些地区、状态或指定规则的编码等进行划分。 Hash分区方式，它没有固定的规则，由ORACLE管理，只需要将值INSERT进去，ORACLE 会自动去根据一套HASH算法去划分分区，只需要告诉ORACLE要分几个区即可。 WHEN 一、When使用Range分区 Range分区呢是应用范围比较广的表分区方式，它是以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中，比如按照时间划分，2008年1季度的数据
oracle大表分区 回答人：软界网友我来回答回答（3） STEP2、利用操作系统的工具删除以上表空间占用的文件（表空间基于裸设备无须次步）,UNIX系统为例： oracle$ rm /u1/oradata/sales/sales1999_q1.dat oracle$ rm /u1/oradata/sales/sales1999_q2.dat oracle$ rm /u1/oradata/sales/sales1999_q3.dat oracle$ rm /u1/oradata/sales/sales1999_q4.dat 4 、分区的其他操作： 分区的其他操作包括截短分区（truncate），将存在的分区划分为多个分区(split)，交换分区(exchange)，重命名（rename）,为分区建立索引等。DBA能够依照适当的情形使用。 以下仅说明分裂分区（split）,例如该公司1999年第四季度销售明细数据急剧增加（因为庆国庆、迎千禧、贺回来），DBA向公司建议将第四季度的分区划分为两个分区，每个分区放两个月份的数据，操作如下： STEP1、按（1）的方法建立两个分区的表空间ts_sales1999q4p1, ts_sales1999q4p2；
STEP2、给表添加两个分区sales1999_q4_p1,sales1999_q4_p2; STEP3、分裂分区： ALTER TABLE sales SPLIT PARTITON sales1999_q4 AT TO_DATE (‘1999-11-01’,’YYYY-MM-DD’) INTO (partition sales1999_q4_p1, partition sales1999_q4_p2) 5 、查看分区信息： DBA要查看表的分区信息，可查看数据字典USER_EXTENTS,操作如下：SVRMGRL>SELECT * FROM user_extents WHERE SEGMENT_NAME=’SALES’; SEGMENT_NA PARTITION_ SEGMENT_TYPE TABLESPACE ---------- ------------ --------------- -------------- SALES SALES1999_Q1 TABLE PARTITION TS_SALES1999Q1 SALES SALES1999_Q2 TABLE PARTITION TS_SALES1999Q2 SALES SALES1999_Q3 TABLE PARTITION TS_SALES1999Q3 SALES SALES1999_Q4 TABLE PARTITION TS_SALES1999Q4 SALES SALES2000_Q1 TABLE PARTITION TS_SALES1999Q1 SALES SALES2000_Q2 TABLE PARTITION TS_SALES1999Q2 SALES SALES2000_Q3 TABLE PARTITION TS_SALES1999Q3 SALES SALES2000_Q4 TABLE PARTITION TS_SALES1999Q4
1. 分区建立 1.1. 建立分区表 create table bigtable ( sale_date date, product_id number, sale_count number, charge number, sales_id number ) tablespace ts_partition pctfree 5 pctused 80 initrans 1 maxtrans 255 parallel(degree 2) storage ( initial 2M next 2M minextents 1 maxextents unlimited pctincrease 0 ) partition by range(sale_date) ( partition sale_date_20020101 values less than (to_date('20020102','yyyymmdd')), partition sale_date_20020102 values less than (to_date('20020103','yyyymmdd')) ); 1.2. 建立分区索引 create index create index idx_bigtable_product_id on bigtable(product_id) parallel 2 local tablespace users on bigtable(product_id) parallel 2 local tablespace users；分区索引和全局索引：
分区索引就是在所有每个区上单独创建索引，它能自动维护，在drop或truncate某个分区时不影响该索引的其他分区索引的使用，也就是索引不会失效，维护起来比较方便，但是在查询性能稍微有点影响。create index idx_ta_c2 on ta(c2) local (partition p1,partition p2,partition p3,partition p4); 或者 create index idx_ta_c2 on ta(c2) local ; 另外在create unique index idx_ta_c2 on ta(c2) local ;系统会报ORA-14039错误，这是因为ta表的分区列是c1，不支持在分区表上创建PK主键或时主键列不包含分区列，创建唯一约束也不可以这样。 oracle 全局索引就是在全表上创建索引，它可以创建自己的分区，可以和分区表的分区不一样，也就是它是独立的索引。 在drop或truncate某个分区时需要创建索引alter index idx_xx rebuild，也可以通过alter table table_name drop partition partition_name update global indexes;实现，但是如果数据量很大则要花很长时间在重建索引上。 可以通过查询user_indexes、user_part_indexes和user_ind_partitions视图来查看索引是否有效。 create index idx_ta_c3 on ta(c3); 或者把全局索引分成多个区(注意和分区表的分区不一样)： create index idx_ta_c4 on ta(c4) global partition by range(c4)(partition ip1 values less than(10000),partition ip2 values less than(20000),partition ip3 values less than(maxvalue)); 注意全局索引上的引导列要和range后列一致，否则会有ORA-14038错误。如不能这样写：create index idx_ta_c4 on ta(c1) global partition by range(c4)(partition ip1 values less than(10000),partition ip2 values less than(20000),partition ip3 values less than(maxvalue)); oracle会对主键自动创建全局索引 如果想使某个分区索引置为不可用则可以用如下脚本： alter index idx_tab1 modify partition "ind partition name" unusable 如果想在主键的列上创建分区索引，除非主键包括分区键，还有就是主键建在两个或以上列上，否则不能创建。
分区表_分区索引_索引分区 在大量业务数据处理的项目中，可以考虑使用分区表来提高应用系统的性能并方便数据管理，本文详细介绍了分区表的使用。 在大型的企业应用或企业级的数据库应用中，要处理的数据量通常可以达到几十到几百GB，有的甚至可以到TB级。虽然存储介质和数据处理技术的发展也很快，但是仍然不能满足用户的需求，为了使用户的大量的数据在读写操作和查询中速度更快，Oracle提供了对表和索引进行分区的技术，以改善大型应用系统的性能。 使用分区的优点： ·增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用； ·维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可； ·均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能； ·改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。 Oracle数据库提供对表或索引的分区方法有三种： ·范围分区 ·Hash分区（散列分区） ·复合分区 下面将以实例的方式分别对这三种分区方法来说明分区表的使用。为了测试方便，我们先建三个表空间。 create tablespace dinya_space01 datafile ’/test/demo/oracle/demodata/dinya01.dnf’ size 50M create tablespace dinya_space01 datafile ’/test/demo/oracle/demodata/dinya02.dnf’ size 50M create tablespace dinya_space01 datafile ’/test/demo/oracle/demodata/dinya03.dnf’ size 50M 1.1. 分区表的创建 1.1.1. 范围分区
多做知识的积累详解ORACLE数据库的分区表 此文从以下几个方面来整理关于分区表的概念及操作: 1.表空间及分区表的概念 2.表分区的具体作用 3.表分区的优缺点 4.表分区的几种类型及操作方法 5.对表分区的维护性操作. (1.) 表空间及分区表的概念 表空间： 是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表，所以称作表空间。 分区表： 当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。 ( 2).表分区的具体作用 Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。通常，分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。 分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。从数据库管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。 什么时候使用分区表： 1、表的大小超过2GB。 2、表中包含历史数据，新的数据被增加都新的分区中。 (3).表分区的优缺点 表分区有以下优点： 1、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。 2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用; 3、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可; 4、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。 缺点：
oracle分区表彻底删除的办法 当对一个不再使用的分区表进行drop后,查询user_tab_partitions视图发现出现如下不规则的分区表表名: SQL> select distinct table_name from user_tab_partitions; BIN$l+Pv5l1jCMXgQKjAyQFA0A==$0 这样很容易导致自己写的"自动增加表的分区"的存过发生错误,因此为了避免再修改存过,只能把这些不规则的表名删除才行.现提供如下方法彻底删除这些不规则的表名. 其实当我们执行drop table tablename的时候,不是直接把表删除掉,而是放在了回收站里,可以通过查询user_recyclebin查看被删除的表信息.这样，回收站里的表信息就可以被恢复或彻底清除。 通过查询回收站user_recyclebin获取被删除的表信息，如果想恢复被drop掉的表,可以使用如下语句进行恢复 flashback table
oracle分区表的建立方法 Oracle的分区表能够包括多个分区，每个分区差不多上一个独立的段（SEGMENT），能够存放到不同的表空间中。查询时能够通过查询表来访咨询各个分区中的数据，也能够通过在查询时直截了当指定分区的方法来进行查询。 分区提供以下优点： 由于将数据分散到各个分区中，减少了数据损坏的可能性； 能够对单独的分区进行备份和复原； 能够将分区映射到不同的物理磁盘上，来分散IO； 提高可治理性、可用性和性能。 Oracle提供了以下几种分区类型： 范畴分区（range）； 哈希分区（hash）； 列表分区（list）； 范畴－哈希复合分区（range-hash）； 范畴－列表复合分区（range-list）。 Oracle的一般表没有方法通过修改属性的方式直截了当转化为分区表，必须通过重建的方式进行转变，下面介绍三种效率比较高的方法，并讲明它们各自的特点。
方法一：利用原表重建分区表。 步骤： SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, TIME DATE); 表已创建。 SQL> INSERT INTO T SELECT ROWNUM, CREATED FROM DBA_OBJECTS; 已创建6264行。 SQL> COMMIT; 提交完成。 SQL> CREATE TABLE T_NEW (ID, TIME) PARTITION BY RANGE (TIME) 2 (PARTITION P1 V ALUES LESS THAN (TO_DATE('2004-7-1', 'YYYY-MM-DD')), 3 PARTITION P2 V ALUES LESS THAN (TO_DA TE('2005-1-1', 'YYYY-MM-DD')), 4 PARTITION P3 V ALUES LESS THAN (TO_DA TE('2005-7-1', 'YYYY-MM-DD')), 5 PARTITION P4 V ALUES LESS THAN (MAXV ALUE)) 6 AS SELECT ID, TIME FROM T; 表已创建。 SQL> RENAME T TO T_OLD;