Oracle分区表和索引.doc

Oracle分区表和索引什么时候使用分区： 1、 大数据量的表，比如大于2GB。一方面2GB文件对于32位os是一个上限，另外备份时间长。2、 包括历史数据的表，比如最新的数据放入到最新的分区中。典型的例子：历史表，只有当前月份的数据可以被修改，而其他月份只能read-onlyORACLE只支持以下分区：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),PARTITION sales_feb2000 VALUES LESS THAN(03/01/2000),PARTITION sales_mar2000 VALUES LESS THAN(04/01/2000),PARTITION sales_apr2000 VALUES LESS THAN(05/01/2000);Partition Key：最多16个columns,可以是nullable的非分区的表可以有分区或者非分区的索引;分区表可以有分区或者非分区的索引;Partitioning 方法：Range PartitioningList PartitioningHash PartitioningComposite PartitioningComposite Partitioning:组合，以及 range-hash and range-list composite partitioningRange Partitioning：每个分区都有VALUES LESS THAN子句，表示这个分区小于(=)前一个分区的VALUES LESS THAN值。MAXVALUE定义最高的分区，他表示一个虚拟的无限大的值。这个分区包括null值。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(TO_DATE(01/02/2000,DD/MM/YYYY),PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE(01/03/2000,DD/MM/YYYY),PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE(01/04/2000,DD/MM/YYYY),PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE(01/05/2000,DD/MM/YYYY),PARTITION sales_2000 VALUES LESS THAN(MAXVALUE);插入数据：Insert into sales_range values(1,2,3,to_date(21-04-2000,DD-MM-YYYY);Insert into sales_range values(1,2,3,sysdate);选择数据：select * from sales_range;select * from sales_range partition(sales_apr2000);select * from sales_range partition(sales_mar2000);select * from sales_range partition(sales_2000);按照多个列分区：CREATE TABLE sales_range1(salesman_id NUMBER(5),salesman_name VARCHAR2(30),sales_amount NUMBER(10),sales_date DATE)PARTITION BY RANGE(sales_date, sales_amount)(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE(01/02/2000,DD/MM/YYYY),1000),PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE(01/03/2000,DD/MM/YYYY),2000),PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE(01/04/2000,DD/MM/YYYY),3000),PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE(01/05/2000,DD/MM/YYYY),4000),PARTITION sales_2000 VALUES LESS THAN(MAXVALUE, MAXVALUE);Insert into sales_range1 values(1,2,500, TO_DATE(21/01/2000,DD/MM/YYYY);Insert into sales_range1 values(2,3,1500, sysdate);如果多个分区列的值冲突，则按照从左到右的优先级。List Partitioning：可以组织无序的，或者没有关系的数据在相同的分区。不支持多列的(multicolumn) partition keys，只能是一个列。DEFAULT表示不满足条件的都放在这个分区。CREATE TABLE sales_list(salesman_id NUMBER(5),salesman_name VARCHAR2(30),sales_state VARCHAR2(20),sales_amount NUMBER(10),sales_date DATE)PARTITION BY LIST(sales_state)(PARTITION sales_west VALUES(California, Hawaii),PARTITION sales_east VALUES (New York, Virginia, Florida),PARTITION sales_central VALUES(Texas, Illinois),PARTITION sales_other VALUES(DEFAULT);Hash Partitioning：不可以作splitting, dropping or merging操作。但是可以added and coalesced.当我们无法判断有多少数据映射或者怎样映射到各个分区时，可以使用这种方法。分区数据最好是2的幂，这样可以平均分配数据。CREATE TABLE sales_hash1(salesman_id NUMBER(5),salesman_name VARCHAR2(30),sales_amount NUMBER(10),week_no NUMBER(2)PARTITION BY HASH(salesman_id)PARTITIONS 4STORE IN (users, TOOLS, TEST, TABLESPACE1);-表空间CREATE TABLE sales_hash(salesman_id NUMBER(5),salesman_name VARCHAR2(30),sales_amount NUMBER(10),week_no NUMBER(2)PARTITION BY HASH(salesman_id)(PARTITION p1 tablespace users,PARTITION p2 tablespace system);Composite Partitioning:先按照range分区，每个子分区又按照list or hash分区。CREATE TABLE sales_composite(salesman_id NUMBER(5),salesman_name VARCHAR2(30),sales_amount NUMBER(10),sales_date DATE)PARTITION BY RANGE(sales_date)SUBPARTITION BY HASH(salesman_id)-子分区SUBPARTITION TEMPLATE(SUBPARTITION sp1 TABLESPACE data1,SUBPARTITION sp2 TABLESPACE data2,SUBPARTITION sp3 TABLESPACE data3,SUBPARTITION sp4 TABLESPACE data4)(PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE(02/01/2000,DD/MM/YYYY)PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE(03/01/2000,DD/MM/YYYY)PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE(04/01/2000,DD/MM/YYYY)PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE(05/01/2000,DD/MM/YYYY)PARTITION sales_may2000 VALUES LESS THAN(TO_DATE(06/01/2000,DD/MM/YYYY);使用TEMPLATE，oracle会这样命名子分区：分区_子分区，比如sales_jan2000_sp1表示将数据放在data1表空间Range-list:CREATE TABLE bimonthly_regional_sales(deptno NUMBER,item_no VARCHAR2(20),txn_date DATE,txn_amount NUMBER,state VARCHAR2(2)PARTITION BY RANGE (txn_date)SUBPARTITION BY LIST (state)SUBPARTITION TEMPLATE(SUBPARTITION east VALUES(NY, VA, FL) TABLESPACE system,SUBPARTITION west VALUES(CA, OR, HI) TABLESPACE users,SUBPARTITION central VALUES(IL, TX, MO) TABLESPACE tools) ( PARTITION janfeb_2000 VALUES LESS THAN (TO_DATE(1-03-2000,DD-Mm-YYYY), PARTITION marapr_2000 VALUES LESS THAN (TO_DATE(1-05-2000,DD-Mm-YYYY), PARTITION mayjun_2000 VALUES LESS THAN (TO_DATE(1-07-2000,DD-Mm-YYYY) );分区维护操作：移动分区：通常是移动到不同的表空间。MoveAlter table sales_hash move partition p2 tablespace users;(单独移动表也可以，达到整理碎片的效果Alter table t move tablespace users;)添加分区：Alter table t add partition p3 values less than只能在已经分区表的最后一个分区之后添加，并且最后一个分区使用特定健值定义，不能是maxvalue.如果想在中间或开始部分，或者maxvalue后，添加分区，使用split 分裂已有分区。拆分分区：ALTER TABLE SALES_RANGE SPLIT PARTITION sales_2000at (TO_DATE(01/05/2001,DD/MM/YYYY)INTO ( PARTITION sales_2000_1, PARTITION sales_2000_2);相当于：PARTITION sales_2000_1 values less than(TO_DATE(01/05/2001,DD/MM/YYYY)删除分区：Alter table t drop partition p3;Alter table t truncate partition p3;结合分区：只是对于hash partition, 用来合并并减少一个partitionset line 150Select segment_name,partition_name from dba_segmentswhere segment_name=upper(sales_hash);alter table sales_hash coalesce partition;合并分区：合并相邻的分区ALTER TABLE four_seasonsMERGE PARTITIONS quarter_one, quarter_two INTO PARTITION quarter_two;交换表分区：CREATE TABLE sales_range_temp(salesman_id NUMBER(5),salesman_name VARCHAR2(30),sales_amount NUMBER(10),sales_date DATE);insert into sales_range_temp values(11,11,11,sysdate);select * from sales_range partition(sales_2000_2);alter table sales_range exchange partition sales_2000 with table sales_range_temp;分区索引：分为global, local indexlocal partitioned index:每个local index对应一个分区。增加和删除分区自动的增加和删除了local index。Local index可以是全表unique的，条件是part