oracle分区的四种方式

来源：互联网发布：php后端管理模板编辑：程序博客网时间：2024/05/02 08:48

splid可以在中间增加分区。

引言：Oracle的range,hash,list三大分区可能我们大家在熟悉不过了，但什么每种分区适用于什么场景可能是很多人的疑惑点，那么在选择不同的分区时应该注意什么，
为什么这么选，有哪些需要我们加以注意的地方，让我们一一来探索一下吧：）

11g之前，维护分区需要手工。11g之后使用interval来实现自动扩展分区，简化了维护。
根据年: INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))

根据月: INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
根据天: INTERVAL(NUMTODSINTERVAL(1,'DAY'))

根据时分秒: NUMTODSINTERVAL( n, { 'DAY'|'HOUR'|'MINUTE'|'SECOND'})
interval (1000) store in (test,PERFSTAT)
ALTER TABLE tb_name ADD PARTITION SYS20160428000000 VALUES LESS THAN (TO_DATE('2016-04-28 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) TABLESPACE DATA_ELMT_SURF_3_TS

--alter table SURF_WEA_CHN_MUL_MIN_TAB_CW drop partition SYS20160410000000 update indexes ;
--alter index usr_sod.UK_MUL_MIN_TAB_CW rebuild;

----------------------------------------------------------------------------------------------------------------------------------------------------

A.创建range分区，一般用于日期化处理，range分区可以很好的管理基于日期来分区的数据
创建一个新用户ls
SYS@LEO> create user ls identified by ls;
SYS@LEO> grant dba to ls;
SYS@LEO> conn ls/ls
创建样本数据表
create table liusheng (orderid number(10),name varchar2(10),ls_date date);
insert into liusheng values (1,'ls1',to_date('1981-01-02','yyyy-mm-dd'));
insert into liusheng values (1,'ls2',to_date('1998-01-03','yyyy-mm-dd'));
insert into liusheng values (1,'ls3',to_date('1999-01-04','yyyy-mm-dd'));
insert into liusheng values (1,'ls4',to_date('2000-01-05','yyyy-mm-dd'));
insert into liusheng values (1,'ls5',to_date('2000-01-06','yyyy-mm-dd'));
insert into liusheng values (1,'ls6',to_date('2001-01-07','yyyy-mm-dd'));
insert into liusheng values (1,'ls7',to_date('2001-01-08','yyyy-mm-dd'));
insert into liusheng values (1,'ls8',to_date('2002-01-09','yyyy-mm-dd'));
insert into liusheng values (1,'ls9',to_date('2002-01-10','yyyy-mm-dd'));
insert into liusheng values (1,'ls10',to_date('2011-01-11','yyyy-mm-dd'));
创建range分区表
create table liusheng_part
partition by range (ls_date)
(
partition liusheng_part_1999_1 values less than (to_date('1999-01-01','yyyy-mm-dd')) ,
partition liusheng_part_2000_1 values less than (to_date('2000-01-01','yyyy-mm-dd')) ,
partition liusheng_part_2001_1 values less than (to_date('2001-01-01','yyyy-mm-dd')) ,
partition liusheng_part_2002_1 values less than (to_date('2002-01-01','yyyy-mm-dd')) ,
partition liusheng_part_2003_1 values less than (to_date('2003-01-01','yyyy-mm-dd')) ,
partition other values less than (maxvalue)
)
as select * from liusheng;
查看分区
select * from liusheng_part partition (liusheng_part_1999_1);
select * from liusheng_part partition (liusheng_part_2000_1);
select * from liusheng_part partition (liusheng_part_2001_1);
select * from liusheng_part partition (liusheng_part_2002_1);
select * from liusheng_part partition (liusheng_part_2003_1);
select * from liusheng_part partition (other);

create table graderecord
02.(
03. sno varchar2(10),
04. sname varchar2(20),
05. dormitory varchar2(3),
06. grade int
07.)
08.partition by hash(sno)
09.(
10. partition p1,
11. partition p2,
12. partition p3
13.);
alter table graderecord add partition p4;

B.创建hash分区，利用hash函数打散某列使数据均匀分布，一般用于均衡I/O，缺点数据不容易管理，哈希分区不能DROP、SPLIT 以及MERGE分区
我们创建了拥有10个分区的哈希分区表“LIUSHENG_HASH”
一、好处
1、对于分区本生不需要定期的进行分区加入（范围分区和LIST分区需要定期的对新加入的值新建分区）
2、可以消除访问热点块及索引热点块，由于索引是排序后的结构，对于一列自增的列加入范围分区，可能对索引的高位块进行频繁的数据插入，导致频繁的写入和分裂
     对于这样的索引如果加入散列分区索引即可消除。
LS@LEO> create table liusheng_hash partition by hash(object_id) partitions 10 as select * from dba_objects;
hash分区所占用的区个数，看每个分区占用的个数都差不多，说明数据还是比较均匀分布的
缺点：hash列上数值不能有太多的重复值，否则会导致数据分布不均匀。
      分区不能太多，典型的大约1000个分区，那么在分区触发（谓词导致索引范围扫描）的并行访问操作时可能更慢，因为有非常多额外的分区维护操作（我曾经遇到过1个1844个HASH分区，导致的并行性能问题
   对于长期使用范围扫描的字段不适合散列分区，因为这样会导致多个分区扫描，而对于经常唯一扫描的字段适合建立HASH分区。
   分区的个数是2的N次密
   对于自增字段非常适合建立HASH分区，这样数据非常适合均匀的HASH分布，对于一个字段建立HASH分区最好事先对字段进行HASH统计，来测试是否能够达到均匀的分布。
   总之，Hash分区有其优势，但也有严重的缺陷，比如这里描述的分区扩展问题。因此在项目设计之初，我们就需要慎重选择分区数。但是随着数据量的增加，
   我们又很难避免为分区表增加分区的操作，这种操作是很耗资源的操作，操作过程中由于锁的问题会影响对原有某些分区的操作。
   但如果我们因为畏惧前面存在的问题拖着不作分区扩展，则越是往后，随着数据量的增加，这种增加分区的操作越难以实施。
select partition_name,count(*) from user_extents where segment_name='LIUSHENG_HASH' group by partition_name;
PARTITION_NAME                   COUNT(*)
------------------------------ ----------
SYS_P27                                 3
SYS_P26                                 3
SYS_P22                                 2
SYS_P28                                 3
SYS_P29                                 2
SYS_P21                                 2
SYS_P23                                 3
SYS_P25                                 3
SYS_P30                                 2
SYS_P24                                 3
select count(*) from liusheng_hash;

COUNT(*)
----------
9860

C.创建list分区，一般用于数据可枚举，有限个值，可以考虑列表分区，例如国家名字，按州来分区
创建list分区表，我们按国家来分别存放在不同的州，每个州是一个分区
create table liusheng_list
(city_id NUMBER(5),
city_name VARCHAR2(30),
city_state VARCHAR2(20),
city_amount NUMBER(10)
)
partition by list (city_name)
(
partition asia VALUES('china','japan'),
partition europe VALUES ('germany','italy'),
partition africa VALUES('libya','brazil'),
partition other VALUES(DEFAULT) --默认分区
);
插入数据
insert into liusheng_list values(1,'china','asia',100);
insert into liusheng_list values(2,'germany','europe',101);
insert into liusheng_list values(3,'libya','africa',102);
insert into liusheng_list values(4,'liusheng_city','other',103);
查看数据
LS@LEO> select * from liusheng_list;

   CITY_ID COUNTRY_NAME                   STATE                CITY_AMOUNT
---------- ------------------------------ -------------------- -----------
         1 china                                               asia                         100
         2 germany                                      europe                       101
         3 libya                                                 africa                       102
         4 liusheng_city                                 other                        103

小结：测试了上面的三大分区后，我相信现在不仅仅有感性的认识而且也加深了理性的认识，分区是个好东西，合理的利用可以提高我们管理收益（但不一定会提高查询收益），使用不当也会给我们添加许多麻烦，三思而后行是做DBA的一个好习惯。

补充：分区之优势
分区技术实质可以把数据分摊到不同的物理位置，增加I/O负载，提高检索效率。
可用性：分区表可以跨越表空间，而普通表则不然，好处就是如果表的一个分区损坏，其他分区不会受到影响我们只需要修复损坏的分区即可

本篇文章来源于 Linux公社网站(www.linuxidc.com) 原文链接：http://www.linuxidc.com/Linux/2012-08/67963.htm

四：复合分区

范围分区与散列分区或列表分区的组合

语法：
[sql]
PARTITION BY RANGE (column_name1)
SUBPARTITION BY HASH (column_name2)
SUBPARTITIONS number_of_partitions
(    www.2cto.com
PARTITION part1 VALUE LESS THAN(range1),
PARTITION part2 VALUE LESS THAN(range2),
...
PARTITION partN VALUE LESS THAN(MAXVALUE)
);

例：
[sql]
CREATE TABLE SALES
(
    PRODUCT_ID VARCHAR2 (5),
    SALES_DATE DATE NOT NULL,
    SALES_COST NUMBER (10)
)    www.2cto.com
PARTITION BY RANGE (SALES_DATE)
SUBPARTITION BY HASH (PRODUCT_ID)
SUBPARTITIONS 5
(
    PARTITION S1 VALUES LESS THAN (TO_DATE(‘01/4月/2001','DD/MON/YYYY')),
    PARTITION S2 VALUES LESS THAN (TO_DATE(‘01/7月/2001','DD/MON/YYYY')),
    PARTITION S3 VALUES LESS THAN (TO_DATE(‘01/9月/2001','DD/MON/YYYY')),
    PARTITION S4 VALUES LESS THAN (MAXVALUE)
);

0 0