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;

注意：要交换的分区表中不能含有自增列，主键，否则可能无法交换分区

posted @ 2020-03-24 17:28 ZHAIQ 阅读(1081) 评论(0) 编辑收藏举报

刷新页面返回顶部

ZHAIQ