Chinaunix首页 | 论坛 | 博客
  • 博客访问: 294739
  • 博文数量: 50
  • 博客积分: 2855
  • 博客等级: 上尉
  • 技术积分: 353
  • 用 户 组: 普通用户
  • 注册时间: 2009-12-08 13:43
文章分类

全部博文(50)

文章存档

2019年(1)

2016年(2)

2015年(6)

2014年(2)

2013年(4)

2012年(34)

2011年(1)

分类: Oracle

2015-01-10 16:11:21

原文地址:Oracle分区表和索引 作者: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除外),则不会删除分区。

阅读(1357) | 评论(0) | 转发(0) |
0

上一篇:Oracle dblink详解

下一篇:TKPROF使用学习

给主人留下些什么吧!~~