Oracle分区表和索引
时间：2010-10-27 来源：lzdyxin
什么时候使用分区：
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'),
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 Partitioning
List Partitioning
Hash Partitioning
Composite Partitioning
Composite Partitioning:组合，以及 range-hash and range-list composite partitioning
Range 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 4
STORE 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')) );
分区维护操作：
移动分区：
通常是移动到不同的表空间。Move
Alter 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_2000
at (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, 用来合并并减少一个partition
set line 150
Select segment_name,partition_name from dba_segments
where segment_name=upper(’sales_hash’);
alter table sales_hash coalesce partition;
合并分区：
合并相邻的分区
ALTER TABLE four_seasons
MERGE 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 index
local partitioned index:
每个local index对应一个分区。增加和删除分区自动的增加和删除了local index。
Local index可以是全表unique的，条件是partition key必须是index columns的一部分?
CREATE INDEX employees_local_idx ON employees (employee_id) LOCAL;
分区上的位图索引只能建立为local partitioned index,不能是global
Global partitioned index:
索引也分区，但是分几个区，按照什么样的partition key分区跟表没有关系。不能增加分区，可以使用ALTER INDEX SPLIT PARTITION;删除：ALTER INDEX DROP PARTITION;这些操作会使的索引失效，所以建议：
ALTER TABLE DROP PARTITION P1 UPDATE GLOBAL INDEXES
这样保证索引仍然有效，online，不需重建。
CREATE INDEX employees_global_part_idx ON employees(employee_id)
GLOBAL PARTITION BY RANGE(employee_id)
(PARTITION p1 VALUES LESS THAN(5000),
PARTITION p2 VALUES LESS THAN(MAXVALUE));
Global Nonpartitioned Indexes:
怎样提高性能：
Partition Pruning
根据SQL自动选择应该访问哪些必要的分区，partition pruning可以跳过不必要的索引或者表分区或子分区。但是如果SQL对partition columns作了function( to_date除外)，则不会删除分区。
相关阅读 更多 +
