[计算机软件及应用]oracle表分区详解

[计算机软件及应用]oracle表分区详解 oracle表分区解详详 此文从以下几个方面来整理于分区表的概念及操作关关关关关关关关关关关: 1.关关关关关关关关表空及分区表的概念 2.表分区的具体作用 3.关关关表分区的缺点 4.关关关关关关关关表分区的几型及操作方法 5.关关关关关关表分区的性操作. (1.) 关关关关关关关关表空及分区表的概念 表空:是一个或多个数据文件的集合,所有的数据象都存放在指定的表空中,但主要关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关存放的是表,所以称作表空。 关关 分区表:当表中的数据量不断增大,数据的速度就会慢,用程关关关关关关关关关关关关关关关序的性能就会下降,就考表行分区。表行分区后,关关关关关关关关关关关关关关关关关关关关关关关上表仍然是一完整的表,只是将表中的数据在物理上存放到多个表空关关关关关关关关关关关关关关关关关关关关关关关关关关(物理文件上)关关,关关关关关关关关关关关数据,不至于次都描整表。每 ( 2).表分区的具体作用 Oracle关关关关关关关关关关关关关关关关关的表分区功能通改善可管理性、性能和可用性,从而 关关关关关关关关关关关关关关关关关关关关关关关各式用程序来了极大的好。通常,分区可以使某 些以及操作的性能大大提高。此外关关关关关关关关关关关关关关关关关关,关关关关关关关关关关关关分区可以极大化常的管理任,分区是构建千兆字数据系或超高可用性系的工具。关关关关关关关关关关关关关关关关关关关关关关关关关关关关关 分区功能能将表、索引或索引表一关关关关关关关关关关关关关关关关关关关关分段,些数据象的步 段叫做分区。个分区有自己的名称,可以自己的存特性。从数每关关关关关关关关关关关关关关关据管理的角度来看,一个分区后的象具有多个段,关关关关关关关关关关关关关关关关关关关关关关关 关关关关关关关关关关关关关关关关关关关关关关关些段既可行集体管理,也可独管理,就使数据管理 关关关关关关关关关关关关关关关关关关关关在管理分区后的象有相当大的灵活性。但是,从用程 序的角度来看,分区后的表与非分区表完全相同,使用 SQL DML 关关关关关关命令分区后的表,无需任何修改。关关关关关关关关关 什候使用分区表:关关关关关关关关关 1关、表的大小超2GB。 2关关关关关关关关关关关关关关关关关关关、表中包含史数据,新的数据被增加都新的分区中。 (3).关关关表分区的缺点 表分区有以下点:关关关 1关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关、改善性能:分区象的可以搜索自己心的分区,提高索速度。 2关关关关关关关关关关关关关关关关关关、增可用性:如果表的某个分区出故障,表在其他分区的数据仍然可用,强 3关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关、方便:如果表的某个分区出故障,需要修数据,只修分区即可, 4、均衡I/O关关关关:可以把不同的分区映射到磁以平衡I/O关关关关,改善整个系性能。 缺点: 分区表相:已存在的表没有方法可以直接化分区表关关关关关关关关关关关关关关关关关关关关关关 不关 Oracle 关关关关关关关关关提供了在重定表的功能。 (4).关关关关关关关关表分区的几型及操作方法 一.关关关关范分区: 范分区将数据基于范映射到关关关关关关关关关关关关关关关关关关关关一个分区,个范是你在建每 分区指定的分区决定的。分区方式是最常用的,关关关关关关关关关关关关关关关关关关关关关关关关 并且分区常采用日期。个例子:你可能会将售数据按照月份行分区。关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关当使用范分区,考以下几个:关关关关关关关关关关关关关关关 1关关关关、一个分区都必有一个每VALUES LESS THEN关关子句,它指定了一个不包括在分区中的上限。分区的任何等于或者大于个上限的关关关关关关关关关关关关关关关关关关关关关关关 都会被加入到下一个高一些的分区中。 2关关关关关关关关关关关关关关、所有分区,除了第一个,都会有一个式的下限,个就是此分 区的前一个分区的上限。关关 3、在最高的分区中,MAXVALUE关关被定。MAXVALUE关关关关关代表了一个不确定的。个高于其它分区中的任何分区的,也可以理解高于任何分区中指定的关关关关关关关关关关关关关关关关关关关关VALUE LESS THEN关关关关关关关关关的,同包括空。 例一: 假有一个关关关关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 ) 例二:按划分关关关关 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 ); 二.列表分区: 关关关关关关关关关关关关关关关关关关关关关关关关关分区的特点是某列的只有几个,基于的特点我可以采用列表分区。 例一 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 ); ) 三.散列分区: 关关关关关关关关关关关关关关关关关关关关关分区是在列上使用散列算法,以确定将行放入哪个分 区中。当列的没有合适的条件,建使用散列分区。关关关关关关关关关关关关关关关关关关关 散列分区通指定分区号来均匀分布数据的一分区型,因通在关关关关关关关关关关关关关关关关关关关关关关关关关关关关关I/O关关关上行散列分区,使得些分区大小一致。关关关关关关关关关 例一: 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 8STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8); hash分区最主要的机制是根据hash关关关关关关关关关关关关关关关关关关算法来算具体某条插入到哪个分区中,hash算法中最重要的是hash函数,Oracle中如果你要使用hash分区,只需指定分区的数量即可。建分区的数量采用关关关关关关关关2的n关关关关关关关关关关关关关关关关关关关关次方,可以使得各个分区数据分布更加均匀。 四.关关关关关关合范散列分区 关关关关关关关关关关关关关关关关关关关关关关分区是基于范分区和列表分区,表首先按某列行范分 区,然后再按某列行列表分区,分区之中的分区被称子分区。关关关关关关关关关关关关关关关关关关关关关 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 ) ) 五.关关关关关关关合范散列分区: 关关关关关关关关关关关关关关关关关关关关关关分区是基于范分区和散列分区,表首先按某列行范分 区,然后再按某列行散列分区。关关关关关关关 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) ); (5).关关关关关关关关关关关关关有表分区的一些性操作: 一、添加分区 以下代关关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 SPLIT 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; 一. 关关关分区表理知 Oracle关关关关提供了分区技以支持VLDB(Very Large DataBase)关关关关。分区表通分区 列的判断,把分区列不同的,放到不同的分区中。分区完全用关关关关关关关关关关关关关关关关关关关关关关透明。 Oracle的分区表可以包括多个分区,个分区都是一个独每立的段，SEGMENT,,可以存放到不同的表空中。可以通表来各个分区中的关关关关关关关关关关关关关关关关关关关关关关 数据,也可以通在直接指定分区的方法来行。关关关关关关关关关关关关关关关关关关关关 When to Partition a Table关关关关关关关关关关关关什候需要分区表,官网的2关关关关个建如下: ，1,Tables greater than 2GB should always be considered for partitioning. ，2,Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only. 在oracle 10g中最多支持:1024k-1个分区: Tables can be partitioned into up to 1024K-1 separate partitions 关关关关关关关关关关关关机文档上有分区表和索引的明: Partitioned Tables and Indexes #sth ref2604 分区提供以下点:关关关 ，1关关关关关关关,由于将数据分散到各个分区中,减少了数据坏的可能性, ，2关关关关关关关关关关关关关关,可以独的分区行份和恢, ，3关关关关关关,可以将分区映射到不同的物理磁上,来分散IO, ，4,提高可管理性、可用性和性能。 Oracle 10g关关关关关关提供了以下几分区型: ，1关关关关,范分区，range,, ，2,哈希分区，hash,, ，3,列表分区，list,, ，4关关关关关关关关关,范,哈希合分区，range-hash,, ，5关关关关关关关关关,范,列表合分区，range-list,。 Range分区: 　　Range关关关关关关关关关关关关关关关关关关关关分区是用范比广的表分区方式,它是以列的的范来做分区的划分条件,将存放到列所在的关关关关关关关关关关关关关关关关关关关关关关关range分区中。 关关关关关如按照划分,2010年1月的数据放到a分区,2月的数据放到b关关分区,在建的候,需要指定基于的列,以及分区的范。关关关关关关关关关关关关关关关关关关关关关 关关关关关关关关关关关关关关关关关关关关关关关关在按分区,如果某些无法范,可以建maxvalue分区,所有不在指定范关关关关关关关关关关关内的都会被存到maxvalue所在分区中。 如: create table pdba (id number, time date) partition by range (time) ( partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')), partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')), partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')), partition p4 values less than (maxvalue)) Hash分区: 　　关关关关关关关关关关关关关关关关关关关于那些无法有效划分范的表,可以使用hash关关关关关关关关关分区,于提高性能是会有一定的帮助。hash分区会将表中的数据平均分配到你指定的几个分区中,列所在分区是依据分区列的hash关关关关关关关关关关关关关关关关关自分配,因此你并不能控制也不知道哪条会被放到哪个分区中,关关关关关关关关关关关关hash关关关分区也可以支持多个依列。 如: create table test ( transaction_id number primary key,item_id number(8) not null ) partition by hash(transaction_id)( partition part_01 tablespace tablespace01,partition part_02 tablespace tablespace02,partition part_03 tablespace tablespace03); 在关关关关关关关关关关关关关关个分区的表空。里,我指定了每 List分区: 　　List关关关关关关关关关关关关关关关关关关关关关关关关关关分区也需要指定列的,其分区必明确指定,分区列只能有一个,不能像range或者hash关关关关关关关关关关关关关关关关关关分区那同指定多个列做分区依列,但它的个分区可以是多个。关关关关关关关关关关关关关 　　在分区必确定分区列可能存在的,一关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关旦插入的列不在分区范内,插入/更新就会失关关关关关关关关关关,因此通常建使用list关关关关关关关分区,要建一个default关关关关关分区存那些不在指定范关关关关关关关关内的,似range分区中的maxvalue分区。 在根据某字段,如城市代分区,可以指定关关关关关关关关关default关关关关关关关,把非分区的数据,全 部放到个关关default分区。 如: create table custaddr ( id varchar2(15 byte) not null, areacode varchar2(4 byte) ) partition by list (areacode) ( partition t_list025 values ('025'), partition t_list372 values ('372') , partition t_list510 values ('510'), partition p_other values (default) ) 关合分区: 关关关关关关关关关关关关关如果某表按照某列分区之后,仍然大,或者是一些其它的需求, 关关关关关关关关关关关关关关关关关关关关关关关关关关关关关可以通分区内再建子分区的方式将分区再分区,即合分区的方式。 　　关关关关关关合分区呢在10g关关中有两:range-hash,range-list关关关关关关关关关。注意序,根分区只能是 range分区,子分区可以是hash分区或list分区。 如: create table test ( transaction_id number primary key, transaction_date date ) partition by range(transaction_date) subpartition by hash(transaction_id) subpartitions 3 store in (tablespace01,tablespace02,tablespace03) ( partition part_01 values less than(to_date(’2009-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)); create table emp_sub_template (deptno number, empname varchar(32), grade number) partition by range(deptno) subpartition by hash(empname) subpartition template (subpartition a tablespace ts1, subpartition b tablespace ts2, subpartition c tablespace ts3, subpartition d tablespace ts4 ) (partition p1 values less than (1000), partition p2 values less than (2000), partition p3 values less than (maxvalue) ); create table quarterly_regional_sales (deptno number, item_no varchar2(20), txn_date date, txn_amount number, state varchar2(2)) tablespace ts4 partition by range (txn_date) subpartition by list (state) (partition q1_1999 values less than (to_date('1-apr-1999','dd-mon-yyyy')) (subpartition q1_1999_northwest values ('or', 'wa'), subpartition q1_1999_southwest values ('az', 'ut', 'nm'), subpartition q1_1999_northeast values ('ny', 'vm', 'nj'), subpartition q1_1999_southeast values ('fl', 'ga'), subpartition q1_1999_northcentral values ('sd', 'wi'), subpartition q1_1999_southcentral values ('ok', 'tx') ), partition q2_1999 values less than ( to_date('1-jul-1999','dd-mon-yyyy')) (subpartition q2_1999_northwest values ('or', 'wa'), subpartition q2_1999_southwest values ('az', 'ut', 'nm'), subpartition q2_1999_northeast values ('ny', 'vm', 'nj'), subpartition q2_1999_southeast values ('fl', 'ga'), subpartition q2_1999_northcentral values ('sd', 'wi'), subpartition q2_1999_southcentral values ('ok', 'tx') ), partition q3_1999 values less than (to_date('1-oct-1999','dd-mon-yyyy')) (subpartition q3_1999_northwest values ('or', 'wa'), subpartition q3_1999_southwest values ('az', 'ut', 'nm'), subpartition q3_1999_northeast values ('ny', 'vm', 'nj'), subpartition q3_1999_southeast values ('fl', 'ga'), subpartition q3_1999_northcentral values ('sd', 'wi'), subpartition q3_1999_southcentral values ('ok', 'tx') ), partition q4_1999 values less than ( to_date('1-jan-2000','dd-mon-yyyy')) (subpartition q4_1999_northwest values ('or', 'wa'), subpartition q4_1999_southwest values ('az', 'ut', 'nm'), subpartition q4_1999_northeast values ('ny', 'vm', 'nj'), subpartition q4_1999_southeast values ('fl', 'ga'), subpartition q4_1999_northcentral values ('sd', 'wi'), subpartition q4_1999_southcentral values ('ok', 'tx') ) ); 在Oracle 11g关关关关关关关关关关关关中,合分区功能有所增,又增加了强range-range,list-range, list-list,list-hash ,并且11g关关关里面支持Interval关关关关关分区和虚列分区。 关关可以参考Blog: Oracle 11g 关关新特性介 分区表之Interval 关 分区和虚列按星期分区表 二. 关关关关关关普通表分区表方法 将普通表成分区表有关关关关关关关4关方法: 1. Export/import method 2. Insert with a subquery method 3. Partition exchange method 4. DBMS_REDEFINITION 具体参考: How to Partition a Non-partitioned Table [ID 1070693.6] 关关关关关关关关关关关关关关关关关关关关关关关关出入里就不做明,我看看其他三方法。 2.1 插入:Insert with a subquery method 关关方法就是使用insert 关关 关关关关关关关关关关关关关来。当然在建分区表的候可以一起插 入数据,也可以建好后在关关关关关insert 关 关关去。方法采用DDL关关关句,不生UNDO关关,只生 少量REDO关关关关关关关关关关关,建表完成后数据已在分布到各个分区中。 SQL> select count(*) from dba; COUNT(*) ---------- 2713235 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';会已更改。关关关关关 SQL> select time_fee from dba where rownum<5; TIME_FEE ------------------- 2011-02-17 19:29:09 2011-02-17 19:29:15 2011-02-17 19:29:18 2011-02-17 19:29:20 SQL> 2.1.1 Oracle 11g的Interval 在11g里的Interval关关关关关关 关关关关关建,方法没有写全的分区会自建。比如我里只写了 1关关关关关关关关关月日期,如果插入的数据有其他月份的,会自生成的分区。 /* Formatted on 2011/03/02 15:41:09 (QP5 v5.115.810.9015) */CREATE TABLE intervaldave PARTITION BY RANGE (time_fee) INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') ) (PARTITION part1 VALUES LESS THAN (TO_DATE ('01/12/2010', 'MM/DD/YYYY')))AS SELECT ID, TIME_FEE FROM DAVE; SQL> select table_name,partition_name from user_tab_partitions where table_name='INTERVALDAVE'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------INTERVALDAVE PART1 INTERVALDAVE SYS_P24 INTERVALDAVE SYS_P25 INTERVALDAVE SYS_P26 INTERVALDAVE SYS_P33 INTERVALDAVE SYS_P27 INTERVALDAVE SYS_P28 2.1.2 Oracle 10g 版本 在10g里面,我需要写全所有的分区。 sql> create table pdba (id, time) partition by range (time) 2 (partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')), 3 partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')), 4 partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')), 5 partition p4 values less than (maxvalue)) 6 as select id, time_fee from dba;表已建。关关关 SQL> select table_name,partition_name from user_tab_partitions where table_name='PDBA'; TABLE_NAME PARTITION_NAME------------------------------ ------------------------------ PDBA P1 PDBA P2 PDBA P3 PDBA P4 sql> select count(*) from pdba partition (p1); count(*) ---------- 1718285 sql> select count(*) from pdba partition (p2); count(*) ---------- 183667 sql> select count(*) from pdba partition (p3); count(*) ---------- 188701 sql> select count(*) from pdba partition (p4); count(*) ---------- 622582 sql> 关关关关关关关关关关关关关关关关关在分区表已建好了,但是表名不一,需要用rename关表重命名一下: SQL> rename dba to dba_old; 表已重命名。 SQL> rename pdba to dba; 表已重命名。 SQL> select table_name,partition_name from user_tab_partitions where table_name='DBA'; TABLE_NAME PARTITION_NAME------------------------------ ------------------------------ DBA P1 DBA P2 DBA P3DBA P4 2.2 . 关关关关交分区:Partition exchange method 关关关关关关关关关关关关关关关关关关关关关关方法只是数据字典中分区和表的定行了修改,没 有数据的修改或制,关关关关关关关关关关关关关关关关关关关效率最高。适用于包含大数据量的表关关关关关关关关到分区表中的一个分区的操作。尽量在行操作。 交关关关关关如下:分区的操作步 1. 关关关建分区表,假有2个分区,P1,P2. 2. 关建表A存放P1关关的数据。 3. 关建表B 存放P2关关的数据。 4. 用表A 和P1 关 分区交。把表A的数据放到到P1分区 5. 用表B 和p2 关 分区交。把表B的数据存放到P2分区。 关建分区表: sql> create table p_dba 2 (id number,time date) 3 partition by range(time) 4 ( 5 partition p1 values less than (to_date('2010-09-1', 'yyyy-mm-dd')), 6 partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')) 7 ); 表已建。关关关 注意:我关关关关关关里只建了2关关关关关关关关关关关关个分区,没有建存放其他数据的分区。 关建2关关关关关关关关关个分分区的基表: SQL> CREATE TABLE dba_p1 as SELECT id,time_fee FROM dba_old WHERE time_fee CREATE TABLE dba_p2 as SELECT id,time_fee FROM dba_old WHERE time_feeTO_DATE('2010-09-1', 'YYYY-MM-DD'); 表已建。关关关 SQL> select count(*) from dba_p1; COUNT(*) ---------- 1536020 SQL> select count(*) from dba_p2; COUNT(*) ---------- 365932 SQL> 关2个基表与2关关关关关个分区行交: SQL> alter table p_dba exchange partition p1 with table dba_p1; 表已更改。 SQL> alter table p_dba exchange partition p2 with table dba_p2; 表已更改。 关关2个分区: SQL> select count(*) from p_dba partition(p1); COUNT(*) ---------- 1536020 SQL> select count(*) from p_dba partition(p2); COUNT(*) ---------- 365932 注意:数据和之前的基表一致。 关关原来的2个基表: SQL> select count(*) from dba_p2; COUNT(*) ---------- 0 SQL> select count(*) from dba_p1; COUNT(*) ---------- 0 注意: 2关关关个基表的数据成成0。 关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关在里我看一个,一般情况下,我在建分区表的候,都会有一个其他分区,用来存放不匹配分区的数据。关关 关关关关关关关关关关关在个例子中,我只建了2关关个分区,没有建maxvalue分区。在我来插入一条不 关关关关关关关关关关关关关足的数据,看果: SQL> insert into p_dba values(999999,to_date('2012-12-29','yyyy-mm-dd')); insert into p_dba values(999999,to_date('2012-12-29','yyyy-mm-dd')) * 第 1 关关关行出: ORA-14400: 关关关关关关关关关关关插入的分区字未映射到任何分区 SQL> insert into p_dba values(999999,to_date('2009-12-29','yyyy-mm-dd')); 已建关关 1 行。 SQL> select * from p_dba where id=999999; ID TIME ---------- -------------- 999999 29-12月-09 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; 会已更改。关关关关关 SQL> select * from p_dba where id=999999; ID TIME ---------- ------------------- 999999 2009-12-29 00:00:00 SQL> 关关关关关关关关关关关关关关关关关关关关关关关关关关关通个可以清楚,如果插入的数据不足分区,会ORA-14400关关。 2.3 . 关关关关关使用在重定:DBMS_REDEFINITION 关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关在重定能保数据的一致性,在大部分内,表都可以正常行DML操作。只在切关关关关关关关关关关关关关关关关关关关关关关关的的瞬强表,具有很高的可用性。方法具有很灵活性,各不同的需要都能关关关关关关关关关关关关关关关关关关关关关关关足。而且,可以在切前行相的关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关授并建立各束,可以做到切完成后不再需要任何外的管理操作。 关于DBMS_REDEFINITION关关关关关关关关关的介,参考官方接: #C BBFDJBC 关关关关关关关关关关关关关关于用在重定建分区表,参考: How To Partition Existing Table Using DBMS_Redefinition [ID 472449.1] 关个功能只在9.2.0.4关关关关关关关关关关关关以后的版本才有,在重定表具有以下功能: ，1关关关关,修改表的存参数, ，2关关关关关关关关关,将表移到其他表空, ，3关关关关关,增加并行, ，4关关关关关,增加或除分区, ，5,重建表以减少碎片, ，6关关关关关关关关关关关关关,将堆表改索引表或相反的操作, ，7关关关关关关,增加或除一个列。 使用在重定的一些限制条件:关关关关关关关关关关关关 ，1 ,There must be enough space to hold two copies of the table.，2 ,Primary key columns cannot be modified. ，3 ,Tables must have primary keys. ，4 ,Redefinition must be done within the same schema.，5 ,New columns added cannot be made NOT NULL until after the redefinition operation. ，6 ,Tables cannot contain LONGs, BFILEs or User Defined Types.，7 ,Clustered tables cannot be redefined. ，8 ,Tables in the SYS or SYSTEM schema cannot be redefined.，9 ,Tables with materialized view logs or materialized views defined on them cannot be redefined. ，10 ,Horizontal sub setting of data cannot be performed during the redefinition. 在Oracle 10.2.0.4和11.1.0.7 关关关关关关关关关关关版本下,在重定可能会遇到如下bug: Bug 7007594 - ORA-600 [12261] 在重定的大致操作关关关关关关关关关关关关关关流程如下: ，1关关关关关,建基表A,如果存在,就不需要操作。 ，2关关关关关关关关,建的分区表B。 ，3关关关关关关关关关,始重定,将基表A关关关关关关关的数据入分区表B。 ，4关关关关关关关关关,束重定,此在DB 的Name Directory关关里,已将2关关关关关个表行了交。 即此基表关关关A关关关关关关关关关成了分区表,我建的分区表B 关关关关关关成了普通表。此我可以 除我建的表关关关关关关关B关关关关关关。它已是普通表。 下面看一个示例: 1. 关建基本表和索引 sql> conn icd/icd; 已接。关关关 sql> create table unpar_table ( 2 id number(10) primary key, 3 create_date date 4 ); 表已建。关关关 sql> insert into unpar_table select rownum, created from dba_objects;已建关关72288行。 sql> create index create_date_ind on unpar_table(create_date);索引已建。关关关 sql> commit; 提交完成。 2. 关关关关收集表的信息 sql> exec dbms_stats.gather_table_stats('icd', 'unpar_table', cascade => true);pl/sql 关程已成功完成。 3. 关关关关关关建分区表 sql> create table par_table (id number primary key, time date) partition by range (time) 2 (partition p1 values less than (to_date('2004-7-1', 'yyyy-mm-dd')), 3 partition p2 values less than (to_date('2005-1-1', 'yyyy-mm-dd')), 4 partition p3 values less than (to_date('2005-7-1', 'yyyy-mm-dd')), 5 partition p4 values less than (maxvalue)); 表已建。关关关 4. 关关关关行重定操作 4.1 关关关关关关关重定的合理性 sql> exec dbms_redefinition.can_redef_table('icd', 'unpar_table');pl/sql 关程已成功完成。 4.2 如果4.1 关关关关关关关关关关关关关关关关关关关关没有,始重定,个程可能要等一会。 关关关关里要注意:如果分区表和原表列名相同,可以用如下方式行: SQL> BEGIN DBMS_REDEFINITION.start_redef_table( uname => 'ICD', orig_table => 'unpar_table', int_table => 'par_table'); END; / 关关关关关关关关关关关关关关如果分区表的列名和原表不一致,那在始重定的候,需要重 新指定映射系:关关关 SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE( 'ICD', 'unpar_table', 'par_table', 'ID ID, create_date TIME', -- 关关关关关关关关关关在里指定新的映射系 DBMS_REDEFINITION.CONS_USE_PK); 关关关关关关关关关关关关关关关关关关一操作步步到个的分区表束后,数据就已同里来了。 4.3 关关关关关关关同新表,是可的操作步 SQL> BEGIN 2 dbms_redefinition.sync_interim_table( 3 uname => 'ICD', 4 orig_table => 'unpar_table', 5 int_table => 'par_table'); 6 END; 7 / PL/SQL 关程已成功完成。 4.4 关关关关关关关关关关关关关关关关关关关关关关建索引,在重定只重定数据,索引需要独建立。 sql> create index create_date_ind2 on par_table(time); 索引已建。关关关 4.5 关关关关收集新表的信息 sql> exec dbms_stats.gather_table_stats('icd', 'par_table', cascade => true); pl/sql 关程已成功完成。 4.6 关关束重定 SQL> BEGIN 2 dbms_redefinition.finish_redef_table( 3 uname => 'ICD', 4 orig_table => 'unpar_table', 5 int_table => 'par_table'); 6 END; 7 / PL/SQL 关程已成功完成。 关关关关关关束重定的意: 基表unpar_table 关关关关关和分区表par_table 关关 关关关关关关行了交。此分区表par_table成了普通表,我的基表关关关关unpar_table成了分区表。 关关关关关关关关关关关我在重定的候,基表unpar_table关关是可以行DML 操作的。只有在2关个表行切关关关关关关关关关关关关的候会有短的表。 5. 关关关关除表 SQL> DROP TABLE par_table; 表已除。关关关 6. 索引重命名 SQL> ALTER INDEX create_date_ind2 RENAME TO create_date_ind; 索引已更改。 7. 关关 sql> select partitioned from user_tables where table_name = 'UNPAR_TABLE'; par --- yes sql> select partition_name from user_tab_partitions where table_name = 'UNPAR_TABLE'; partition_name ------------------------------ p1 p2 p3 p4 sql> select count(*) from unpar_table; count(*) ---------- 72288 sql> select count(*) from unpar_table partition (p4); count(*) ---------- 72288 sql> 三. 分区表的其他操作 3.1 添加新的分区 添加新的分区有2中情况: ，1关关关,原分区里界是maxvalue或者default 关关关关关关关关关关。情况下,我需要把界分区drop掉,加上新分区后,在添加上新的分区。或者采用 split关关关关关关关关关关,界分区行拆分。 ，2关 关关,没有界分区的。情况下,直接添加分区就可以了。 以界分区添加新分区关关关关关关关关关关关关示例: ，1,分区表和索引的信息如下: SQL> create table custaddr 2 ( 3 id varchar2(15 byte) not null, 4 areacode varchar2(4 byte) 5 ) 6 partition by list (areacode) 7 ( 8 partition t_list556 values ('556') tablespace icd_service, 9 partition p_other values (default)tablespace icd_service 10 ); 表已建。关关关 SQL> create index ix_custaddr_id on custaddr(id) 2 local ( 3 partition t_list556 tablespace icd_service, 4 partition p_other tablespace icd_service 5 ); 索引已建。关关关 ，2关关关关关,插入几条数据: SQL> insert into custaddr values('1','556');已建关关 1 行。 SQL> insert into custaddr values('2','551');已建关关 1 行。 SQL> insert into custaddr values('3','555');已建关关 1 行。 SQL> commit; 提交完成。 SQL> select * from custaddr; ID AREA --------------- ---- 1 556 2 551 3 555 SQL> select * from custaddr partition(t_list556);ID AREA --------------- ---- 1 556 SQL> ，3关关,除default分区 sql> alter table custaddr drop partition p_other;表已更改。 sql> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'; table_name partition_name------------------------------ ------------------------------ custaddr t_list556 ，4,添加新分区 SQL> alter table custaddr add partition t_list551 values('551') tablespace icd_service; 表已更改。 SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------CUSTADDR T_LIST556 CUSTADDR T_LIST551 ，5,添加default 分区 SQL> alter table custaddr add partition p_other values (default) tablespace icd_service; 表已更改。 SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------CUSTADDR T_LIST556 CUSTADDR T_LIST551 CUSTADDR P_OTHER ，6关关关关关关关,于局部索引,oracle关关关关关关关关关关关关关关关关关会自增加一个局部分区索引。一下: sql> select owner,index_name,table_name,partitioning_type from dba_part_indexes where index_name='ix_custaddr_id'; owner index_name table_name ---------------------- ------------------------------ ------------------icd ix_custaddr_id custaddr sql> select index_owner,index_name,partition_name from dba_ind_partitions where index_name='ix_custaddr_id'; index_owner index_name partition_name------------------------------ ------------------------------ ------------------icd ix_custaddr_id p_othericd ix_custaddr_id t_list551icd ix_custaddr_id t_list556 分区索引自建了。关关关关关 3.2 split 分区拆分 在3.1 关关关关关关关关中,我明了可以使用split 关关关的方式来添加分区。里我用split关关关方法上 面的。关关关 sql> alter table custaddr split partition p_other values('552') into (partition t_list552 tablespace icd_service, partition p_other tablespace icd_service);表已更改。 --关关关关关关关关关关关注意里色的地方,如果是Range关型的,使用at,List使用Values。 SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------CUSTADDR T_LIST556 CUSTADDR T_LIST551 CUSTADDR T_LIST552 CUSTADDR P_OTHER SQL> select index_owner,index_name,partition_name from dba_ind_partitions where index_name='IX_CUSTADDR_ID'; index_owner index_name partition_name------------------------------ ------------------------------ ------------------icd ix_custaddr_id p_othericd ix_custaddr_id t_list551icd ix_custaddr_id t_list552icd ix_custaddr_id t_list556 注意:分区表会自关关关关关关关关关关关关关关关关关关关关关关局部分区索引。全局索引会失效,需要行rebuild。 3.3 合并分区Merge 关关关关关关相的分区可以merge关关关关关关关关关关关关关一个分区,新分区的下界原来界低的分 区,上界原来界高的分区,原先的关关关关关关关关关关关关关关关关关关关关关关关关局部索引相也 会合并,全局索引会失效,需要rebuild。 SQL> alter table custaddr merge partitions t_list552,p_other into partition p_other;表已更改。 SQL> select index_owner,index_name,partition_name from dba_ind_partitions where index_name='IX_CUSTADDR_ID'; index_owner index_name partition_name -------------------- ------------------------------ ------------------icd ix_custaddr_id p_other icd ix_custaddr_id t_list551 icd ix_custaddr_id t_list556 SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'; table_name partition_name ------------------------------ ------------------------------custaddr t_list556 custaddr t_list551 custaddr p_other 3.4 . 关关关移分区 SQL> alter table custaddr move partition P_OTHER tablespace system; 表已更改。 SQL> alter table custaddr move partition P_OTHER tablespace icd_service; 表已更改。 注意:分区移关关关关关关关关关关关关关会自局部分区索引,oracle关关关关关关关关关关不会自全局索引,所以 需要我重新关关关rebuild分区索引,具体需要rebuild关哪些索引,可以通 dba_part_indexes,dba_ind_partitions去判断。 SQL> Select index_name,status From user_indexes Where table_name='CUSTADDR'; INDEX_NAME STATUS ------------------------------ -------- IX_CUSTADDR_ID N/A 3.5. Truncate分区 SQL> select * from custaddr partition(T_LIST556);ID AREA --------------- ---- 1 556 SQL> alter table custaddr truncate partition(T_LIST556);表被截断。 SQL> select * from custaddr partition(T_LIST556);未关关关定行 关明: Truncate关相delete关关关关关关关关关关关关关操作很快,数据中的大量数据的批量数据 加可能会有用到,截断分区同会自关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关局部分区索引,同会使全局索引unusable, 需要重建 3.6. Drop分区 SQL> alter table custaddr drop partition T_LIST551;表已更改。 SQL> select table_name,partition_name from user_tab_partitions where table_name='CUSTADDR'; TABLE_NAME PARTITION_NAME------------------------------ ------------------------------ CUSTADDR T_LIST556CUSTADDR P_OTHER 同会自关关关关关关关关关关关关关关关关关关关关关局部分区索引,同会使全局索引unusable,需要重建 四. 分区表的索引 关关关分区索引分本地(local index)索引和全局索引(global index)。局部索引比全局索引容易管理, 关关关而全局索引比快。 与索引有的表:关关关关 dba_part_indexes 关关关关关关关关关分区索引的概要个表上有哪些分信息,可以得知每区索引,分区索引的型关关(local/global) dba_ind_partitions 关关关关关每个分区索引的分区信息 dba_indexes/dba_part_indexes 可以得到个表上有哪些非分区索引每 Local索引肯定是分区索引,Global关关关关关关关关关关关索引可以是否分区,如果分区,只能是有前的分区索引。关关关关关关关 分区索引分2关关:有前(prefix)关的分区索引和无前(nonprefix)的分区索引: ，1关关关关关关关关关关关关关关关关关关关关关关关关关关关,有前的分区索引指包含了分区,并且将其作引列的索引。 如: create index i_id_global on PDBA(id) global --关关引列 2 partition by range(id) --关分区 3 (partition p1 values less than (200), 4 partition p2 values less than (maxvalue) 5 ); 关里的ID 关关关关关关关就是分区,并且分区id 关关关也是索引的引列。 ，2关关关关关关关关关关关关关关关关关关关关关关关关关关关,无前的分区索引的列不是以分区,或者不包含分区列。 如: create index ix_custaddr_local_id_p on custaddr(id) local ( partition t_list556 tablespace icd_service, partition p_other tablespace icd_service) 关个分区是按照areacode关关关来的。但是索引的引列是ID 关关关。所以它就是非前分区索引。 全局分区索引不支持非前的分区索引,如果建,如下:关关关关关关关关关关关关关关关关关 SQL> create index i_time_global on PDBA(id) global --关关索引引列 2 partition by range(time) --分区建 3 (partition p1 values less than (TO_DATE('2010-12-1', 'YYYY-MM-DD')), 4 partition p2 values less than (maxvalue) 5 ); partition by range(time) * 第 2 关关关行出: ORA-14038: GLOBAL 关关关关关分区索引必加上前 4.1 Local 本地索引 关于local关关关关关关关关关关关关索引,当表的分区生化,索引的由Oracle关关关关自行。 注意事关关: ，1 关关关关关关关关关关,局部索引一定是分区索引,分区等同于表的分区。 ，2, 关关关关关关关关关关关关关关关关关关关关关前和非前索引都可以支持索引分区消除,前提是的条件中包含索引分区。关关关关关关关关关关关关关关 ，3 ,局部索引只支持分区内的唯一性,无法支持表上的唯一性,因此如果要用局部索引去关关关关关关关关关关关关关关关关关关关关关关关表做唯一性束,束中必要包括分区列。 ，4 关关关关关关关关关关关关关关,局部分区索引是个分区的,个分区索引只指向一个表分每区,全局索引不然,一个分区索引能指关关关关关关关关关关关关关向n关关关关关关关关关关关关关个表分区,同,一个表分区,也可能指向n关关关关关关关关关关关个索引分区,分区表中的某个分区做truncate或者move,shrink等,可能会影响到n个全局索引分区,正因点,关关关关关关关关关关关关关关关关关关关局部分区索引具有更高的可用性。 ，5 关关关关关关关关关关关关关,位索引必是局部分区索引。 ，6 关关关关关关关关关关关,局部索引多用于数据境中。 ，7 ,B关关关关关关关关关关关关索引和位索引都可以分区,但是HASH索引不可以被分区。 示例: sql> create index ix_custaddr_local_id on custaddr(id) local; 索引已建。关关关 和下面SQL 关效果相同,因local索引就是分区索引: create index ix_custaddr_local_id_p on custaddr(id)local ( partition t_list556 tablespace icd_service, partition p_other tablespace icd_service) SQL> create index ix_custaddr_local_areacode on custaddr(areacode) local; 索引已建。关关关 关关2关关关个索引的型: SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name='CUSTADDR'; index_name table_name partition locali alignment------------------------------ ---------- --------- ------ ------------ ix_custaddr_local_areacode custaddr list local prefixed ix_custaddr_local_id custaddr list local non_prefixed 关关关关因我的custaddr表是按areacode关行分区的,所以索引 ix_custaddr_local_areacode关关关关关是有前的索引，prefixed,。而ix_custaddr_local_id是非 前索引。关关关关 4.2 Global索引 关于global关关关关关关关关关关关关关关关关关索引,可以是否分区,而且索引的分区可以不 与表分区相。全关关关关关关关关关关关关局分区索引只能是B关关关索引,到目前止(10gR2),oracle只支持有前 关的全局索引。 另外oracle关关关关关关关关关关关关关关关关关关关不会自的全局分区索引,当我在表的分 区做修改之后,如果分区行操作不加上关关关关关关关关关关关关关update global indexes关关关关的,通常 会致全关关关关关关关局索引的INVALDED关关关关关关关关 ,必在行完操作后REBUILD。 注意事关关: ，1关关关关关关关关,全局索引可以分区,也可以是不分区索引,全局索引必是前索引,即 全局索引的索引列必是以索引分区作其前几列。关关关关关关关关关关关关关关关 ，2,全局索引可以依附于分区表,也可以依附于非分区表。 ，3关关关关关关关,全局分区索引的索引条目可能指向若干个分区,因此,于全局分区索 引,即使只截断一个分区中的数据,都需要rebulid若干个分区甚至是整个索引。 ，4关关关,全局索引多用于oltp关关关系中。 ，5关关关关关关关关,全局分区索引只按范或者散列分区,hash分区是10g以后才支持。 ，6 ,oracle9i关关关关关以后分区表做move或者truncate关关关关的可以用update global indexes关句 来同更新全步局分区索引,用消耗一定关关关关关关关关关关关关源来取高度的可用性。 ，7 ,表用a列作分区,索引用b做局部分区索引,若where条件中用b关关关关关来,那 oracle关关关关关关关关关关关关关关关关关关关关关关关关关关关关关会描所有的表和索引的分区,成本会比分区更高,此可以考用b做全局分区 索引。 注意:Oracle只支持2关关关关关关关关关关中型的全局分区索引: range partitioned 和Hash Partitioned. 官网的关关关关关明如下: Global Partitioned Indexes Oracle offers two types of global partitioned index: range partitioned and hash partitioned. ，1,Global Range Partitioned Indexes Global range partitioned indexes are flexible in that the degree of partitioning and the partitioning key are independent from the table's partitioning method. They are commonly used for OLTP environments and offer efficient access to any individual record. The highest partition of a global index must have a partition bound, all of whose values are MAXVALUE. This ensures that all rows in the underlying table can be represented in the index. Global prefixed indexes can be unique or nonunique. You cannot add a partition to a global index because the highest partition always has a partition bound of MAXVALUE. If you wish to add a new highest partition, use the ALTER INDEX SPLIT PARTITION statement. If a global index partition is empty, you can explicitly drop it by issuing the ALTER INDEX DROP PARTITION statement. If a global index partition contains data, dropping the partition causes the next highest partition to be marked unusable. You cannot drop the highest partition in a global index.，2,Global Hash Partitioned Indexes Global hash partitioned indexes improve performance by spreading out contention when the index is monotonically growing. In other words, most of the index insertions occur only on the right edge of an index. ，3,Maintenance of Global Partitioned Indexes By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable: ADD (HASH) COALESCE (HASH) DROP EXCHANGE MERGE MOVE SPLIT TRUNCATE 示例1 关关关关关关关关关关关全局索引,全局索引所有分区型都支持: sql> create index ix_custaddr_ global_id on custaddr(id) global; 索引已建。关关关 示例2:全局分区索引,只支持Range 分区和Hash 分区: ，1关关,建2关关关关关关个分区表: sql> create table pdba (id number, time date) partition by range (time) 2 ( 3 partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')), 4 partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')), 5 partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')), 6 partition p4 values less than (maxvalue) 7 ); 表已建。关关关 SQL> create table Thash 2 ( 3 id number primary key, 4 item_id number(8) not null 5 ) 6 partition by hash(id) 7 ( 8 partition part_01, 9 partition part_02, 10 partition part_03 11 ); 表已建。关关关 ，2关关关关关关,建分区索引 示例2:全局分区索引 SQL> create index i_id_global on PDBA(id) global 2 partition by range(id) 3 (partition p1 values less than (200), 4 partition p2 values less than (maxvalue) 5 ); 索引已建。关关关 --关关关关关关关关个是有前的分区索引。 SQL> create index i_time_global on PDBA(id) global 2 partition by range(time) 3 (partition p1 values less than (TO_DATE('2010-12-1', 'YYYY-MM-DD')), 4 partition p2 values less than (maxvalue) 5 ); partition by range(time) * 第 2 关关关行出: ORA-14038: GLOBAL 关关关关关分区索引必加上前 SQL> create index i_time_global on PDBA(time) global 2 partition by range(time) 3 (partition p1 values less than (TO_DATE('2010-12-1', 'YYYY-MM-DD')), 4 partition p2 values less than (maxvalue) 5 ); 索引已建。关关关 --关关关关关关有前的分区索引 SQL> select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name='PDBA'; index_name table_name partition locali alignment------------------------------ ---------- --------- ------ ------------i_id_global pdba range global prefixedi_time_global pdba range global prefixed SQL> CREATE INDEX ix_hash ON PDBA (id,time) GLOBAL 2 PARTITION BY HASH (id) 3 (PARTITION p1, 4 PARTITION p2, 5 PARTITION p3, 6 PARTITION p4); 索引已建。关关关 只要索引的引列包含分区,就是有前的分区索引。关关关关关关关关关关关关关关关关关关关 4.3 关关索引重建 ，1,分区索引 关关关关关关关关关关关关关关关关关关关关关关关于分区索引,不能整体行重建,只能个分区行重建。法如下: Alter index idx_name rebuild partition index_partition_name [online nologging] 关明: online:关关关关关关关表示重建的候不会表。 nologging关关关关关关关关关关关关关:表示建立索引的候不生成日志,加快速度。 如果要重建分区索引,只能drop关关关表原索引,在重新建: SQL>create index loc_xxxx_col on xxxx(col) local tablespace SYSTEM; 关关关关关关关关关关关关关关个操作要求大的表空和排序区。 示例: SQL> select index_name,partition_name from user_ind_partitions where index_name='I_TIME_GLOBAL'; INDEX_NAME PARTITION_NAME ------------------------------ ------------------------------I_TIME_GLOBAL P1 I_TIME_GLOBAL P2 SQL> alter index I_TIME_GLOBAL rebuild partition p1 online nologging;索引已更改。 SQL> alter index I_TIME_GLOBAL rebuild partition p2 online nologging;索引已更改。 ，2,全局索引 Oracle 关关关关关关关关关关关关关关关关关关关关关关关关关关关关关关会自分区索引,于全局索引,如果在分区表操作,没有指定 update index关关关关关关关关关关关关关关关关,会致全局索引失效,需要重建。 SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME='IX_PDBA_GLOBAL'; owner index_name table_name status------------------------------ ------------------------------ ---------- -------sys ix_pdba_global pdba valid 关除一个分区: SQL> alter table pdba drop partition p2; 表已更改。 SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME='IX_PDBA_GLOBAL'; owner index_name table_name status ------------------------------ ------------------------------ ---------- -------sys ix_pdba_global pdba valid split 分区: SQL> alter table pdba split partition P4 at(TO_DATE('2010-12-21 00:00:00','YYYY-MM-DD HH24:MI:SS')) into (partition P4, partition P5); 表已更改。 SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME='IX_PDBA_GLOBAL'; owner index_name table_name status------------------------------ ------------------------------ ---------- -------sys ix_pdba_global pdba valid drop 关关关分区使用update indexes SQL> alter table pdba drop partition P4 UPDATE INDEXES; 表已更改。 SQL> select owner,index_name,table_name,status from dba_indexes where INDEX_NAME='IX_PDBA_GLOBAL'; owner index_name table_name status ---------------------- ------------------------------ ---------- -------sys ix_pdba_global pdba valid 做了几个drop 关关关关关关关关关分区操作,全局索引没有失效,有点奇怪。不如果在生境中, 关是小心点。 重建全局索引命令如下: Alter index idx_name rebuild [online nologging] 示例: SQL> Alter index ix_pdba_global rebuild online nologging; 索引已更改。 关关关关关关关关充一点,分区表存空的: SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name='DBA'; TABLE_NAME PARTITION_NAME TABLESPACE_NAME ---------- ------------------------------ ------------------------------DBA P1 SYSTEM DBA P2 SYSTEM DBA P3 SYSTEM DBA P4 SYSTEM 通关user_tab_partitions 关关关关关关关表可以看到个分区的tablesapce_name. 关每但是,如果通 all_tables 关关关关关关关关关关关关关关关表,却不到分区表表空的信息。 分区表: SQL> select owner,table_name,tablespace_name,cluster_name from all_tables where table_name='DBA'; OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAME----- ---------- ------------------------------ ----------------------------------------------------- SYS DBA 普通表: SQL> select owner,table_name,tablespace_name,cluster_name from all_tables where table_name='DAVE'; OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAME ----- ---------- ------------------------------ --------------------------------------------------- SYS DAVE SYSTEM 整理自网关