Oracle中分区表
分区表 就是通过使用分区技术，将一张大表，拆分成多个表分区（独立的segment），从而提升数据访问的性能，以及日常的可维护性。
分区表分为 : 范围分区(range),列表分区(list),散列分区(hash),复合分区,交换分区
数据库的逻辑结构 : 表空间tablspace,段segment,区extent,块block
可以对分区表进行 : insert , update ,delete
需要注意：
- 虽然各个分区可以存放在不同的表空间中，但这些表空间所使用的块大小必须一致。
- 分区表是建表之初建立的,不能后期添加
1.RANGE(范围分区)
范围分区 : 按照范围进行分区,通常是按照字段分区,比如申请时间,入职时间等...
创建语法:
CREATE TABLE 表名
( 列名 数据类型,
....
)
PARTITION BY RANGE (字段)
( PARTITION 分区名1 VALUES LESS THAN (值1或日期1),
PARTITION 分区名2 VALUES LESS THAN (值2或日期2),
PARTITION 分区名3 VALUES LESS THAN (值3或日期3),
PARTITION 分区名4 VALUES LESS THAN (MAXVALUE)
);
/* VALUSE LESS THAN 特点 :
VALUES < 值1
VALUES >= 值1 AND VALUES < 值2
VALUES >= 值2 AND VALUES < 值3
VALUES >= 值3 AND VALUES < 值4
....
*/
--新增分区 : 首先表一定要是分区表才可以新增
--新增分区高于最后一个分区界限
新增语法:
ALTER TABLE 表名 ADD PSRTITION 分区名
VALUES LESS THAN (值或日期);
删除语法:
ALTER TABLE 表名 GROP PARTITION 分区名;
查询语法:
SELECT <SELECT_LIST> FROM 表名 PARTITION(分区名);
注意 : 除明确要求,尽量不要使用"MAXVALUE"
2.LIST(列表分区)
列表分区 : 按照列表分区,例如身份证号最后一位等
创建语法:
CREATE TABLE 表名
( 列名 数据类型,
....
)
PARTITION BY LIST (字段)
( PARTITION 分区名1 VALUES (值1),
PARTITION 分区名2 VALUES (值2),
PARTITION 分区名3 VALUES (值3)
);
新增语法:
ALTER TABLE 表名 ADD PSRTITION 分区名 VALUES (值);
删除语法:
ALTER TABLE 表名 GROP PARTITION 分区名;
查询语法:
SELECT <SELECT_LIST> FROM 表名 PARTITION(分区名);
3.HESH(散列分区或哈希分区)
实际结论见下：
-
数据随机插入
Hash分区一般是在分区键值无法确定的情况下，使用的一种分区策略，Oracle按照hash 算法把数据插入用户指定的分区键中，它是随机的插入到某个区中，不受人为的干预。 -
分区大小
分区的初始大小受建表初始化参数的影响（即initial、next的影响），不指定时受建库时表空间参数的影响。实际分配数据后的大小，受分区字段值的影响。分区字段值相同时，所有数据只能插入到一个分区；分区字段值不同时，数据随机插入不同的分区。
根据实验的结果并不是平均分配，也不是每个区都分配。如上面的表，最少的一个区只有0.1M，最大的一个区是33M，严重的不均匀。也许随着时间和数据量的不断增加，数据会趋于均衡，估计实验是很难验证出来的。 -
Local和Global索引
分区索引分为Local和Global索引，Local索引和表分区是一一对应的，Global索引又分为Global非分区索引和Global分区索引。Global非分区索引，可以与表分区对应，也可以不对应；但是当Global分区索引与表分区的表空间对应时，则Global分区索引就是个Local索引。 -
分区定值查询时，Local索引与Global索引没有任何区别，但是范围查询时，Global索引的partition start 和 partition stop是相同的；Local索引的partition start 和 partition stop 就不同了，上面的那个表是从1到8（和范围的大小有关）。根据这个情况，如果实际应用中定值查询多，就用Local索引了，反之，则用Global索引。如果根本没法分，那就要权衡了。
5. 分区与不分区的区别
就我的测试用例来说，两者没有本质区别，时间都相当。 15G的数据，分区的SQL语句COST是不分区时SQL语句COST的几千倍，不分区的COST还更低。但是两者的执行时间没有任何区别。在实际使用分区时，性能只是要考虑的一个方面，易管理性、可用性方面分区还是有很大优势的，不能仅看一面而忽略了整体。
如：
1）.一个分区的丢失或损坏不会影响其余的分区，损坏的分区可以单独恢复。
2）.分区把一个大段分为更多的小片段，可以降低争用。
3）.当把其中一个分区删除时,该分区里的数据也会被删除(表空间没有了)
创建语法:
CREATE TABLE 表名
( 列名 数据类型,
....
)
PARTITION BY HASH (字段)
( PARTITION 分区名1 ,
PARTITION 分区名2 ,
PARTITION 分区名3
...
);
新增语法:
ALTER TABLE 表名 ADD PSRTITION 分区名 ;
删除语法:
ALTER TABLE 表名 GROP PARTITION 分区名;
查询语法:
SELECT <SELECT_LIST> FROM 表名 PARTITION(分区名);
4.复合分区
关键字 : 分区：partition 子分区：subpartition
创建语法:
create table testfuhe
( 列名 数据类型,
.....
)
partition by range (字段) subpartition by list (字段)
(
partition 分区名1 values less than (值1或日期1)
( subpartition 子分区名1 values (值1),
subpartition 子分区名2 values (值2),
subpartition 子分区名3 values (值3)
),
partition p_201801 values less than (值2或日期2)
( subpartition 子分区名4 values (值4),
subpartition 子分区名5 values (值5),
subpartition 子分区名6 values (值6)
)
);
注意:分区名不可以相同
5.自增分区
自增分区 : 会根据数据的增加情况自动添加分区
例子:
create table FACT_YEAR
(
stat_date DATE
)
PARTITION BY RANGE (stat_date)
INTERVAL (NUMTOYMINTERVAL (1,'YEAR')) MONTH,具体的数值
(
PARTITION P1 VALUES LESS THAN (TO_DATE('2014-1-1', 'YYYY-MM-DD'))
);
6.交换分区
语法:
alter table 表1
exchange partition 分区名
with table 表2;
注意：要交换的分区表中不能含有自增列，主键，否则可能无法交换分区
