About Oracle 分区(Partitions)

最新推荐文章于 2021-04-03 13:05:51 发布

ddttoop

最新推荐文章于 2021-04-03 13:05:51 发布

阅读量249

点赞数

分类专栏： Oracle DataWarehouse

本文链接：https://blog.csdn.net/weixin_45883933/article/details/105046623

版权

Oracle 同时被 2 个专栏收录

32 篇文章 0 订阅

订阅专栏

DataWarehouse

22 篇文章 0 订阅

订阅专栏

Oracle 分区相关：

一、Oracle分区简介
ORACLE的分区是一种处理超大型表、索引等的技术。分区是一种“分而治之”的技术，通过将大表和索引分成可以管理的小块，从而避免了对每个表作为一个大的、单独的对象进行管理，为大量数据提供了可伸缩的性能。分区通过将操作分配给更小的存储单元，减少了需要进行管理操作的时间，并通过增强的并行处理提高了性能，通过屏蔽故障数据的分区，还增加了可用性。
二、Oracle分区优缺点

优点：
增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能；
改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
缺点：
分区表相关：已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能。
三、Oracle分区方法
Range分区（范围分区）：
范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。如根据序号分区，根据业务记录的创建日期进行分区等。
Hash分区（散列分区）：
散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。
List分区（列表分区）：
当你需要明确地控制如何将行映射到分区时，就使用列表分区方法。与范围分区和散列分区所不同，列表分区不支持多列分区。如果要将表按列分区，那么分区键就只能由表的一个单独的列组成，然而可以用范围分区或散列分区方法进行分区的所有的列,都可以用列表分区方法进行分区。
范围-散列分区（复合分区）：
有时候我们需要根据范围分区后，每个分区内的数据再散列地分布在几个表空间中，这样我们就要使用复合分区。复合分区是先使用范围分区，然后在每个分区内再使用散列分区的一种分区方法（注意：先一定要进行范围分区）
范围-列表分区（复合分区）：
范围和列表技术的组合，首先对表进行范围分区，然后用列表技术对每个范围分区再次分区。与组合范围-散列分区不同的是，每个子分区的所有内容表示数据的逻辑子集，由适当的范围和列表分区设置来描述。（注意：先一定要进行范围分区）
四、Oracle表分区表操作
–Partitioning 是否为true
select * from v$option s where s.PARAMETER=‘Partitioning’ order by s.PARAMETER desc
–创建表空间
CREATE TABLESPACE “PARTION_03”
LOGGING
DATAFILE ‘D:ORACLEORADATAJZHUAPARTION_03.dbf’ SIZE 50M
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
–删除表空间
drop tablespace partion_01
–范围分区技术语句
create table Partition_Test
(
PID number not null,
PITEM varchar2(200),
PDATA date not null
)
partition by range(PID)
(
partition part_01 values less than(50000) tablespace dinya_space01,
partition part_02 values less than(100000) tablespace dinya_space02,
partition part_03 values less than(maxvalue) tablespace dinya_space03
)
create table Partition_TTest
(
PID number not null,
PITEM varchar2(200),
PDATA date not null
)
partition by range(PDATA)
(
partition part_t01 values less than(to_date(‘2004-01-01’,‘yyyy-mm-dd’)) tablespace dinya_space01,
partition part_t02 values less than(to_date(‘2008-01-01’,‘yyyy-mm-dd’)) tablespace dinya_space02,
partition part_t03 values less than(maxvalue) tablespace dinya_space03
)
insert into Partition_Test(PID,PITEM,PDATA) select h.id,h.userid,h.rectime from st_handle h
select * from Partition_Test partition(part_01) t where t.pid = ‘1961’
–hash 分区技术
create table Partition_HashTest
(
PID number not null,
PITEM varchar2(200),
PDATA date not null
)
partition by hash(PID)
(
partition part_h01 tablespace dinya_space01,
partition part_h02 tablespace dinya_space02,
partition part_h03 tablespace dinya_space03
)
insert into Partition_HashTest(PID,PITEM,PDATA) select h.id,h.userid,h.rectime from st_handle h
select * from Partition_HashTest partition(part_h03) t where t.pid = ‘1961’
–复合分区技术
create table Partition_FHTest
(
PID number not null,
PITEM varchar2(200),
PDATA date not null
)
partition by range(PDATA) subpartition by hash(PID) subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)
(
partition part_fh01 values less than(to_date(‘2004-01-01’,‘yyyy-mm-dd’)) tablespace dinya_space01,
partition part_fh02 values less than(to_date(‘2008-01-01’,‘yyyy-mm-dd’)) tablespace dinya_space02,
partition part_fh03 values less than(maxvalue) tablespace dinya_space03
)
insert into Partition_FHTest(PID,PITEM,PDATA) select h.id,h.userid,h.rectime from st_handle h
select * from Partition_FHTest partition(part_fh02) t where t.pid = ‘1961’
select * from Partition_FHTest partition(part_fh03) t
–速度比较
select * from st_handle h where h.rectime > to_date(‘2008-01-01’,‘yyyy-mm-dd’);
select * from Partition_FHTest partition(part_fh03) t where t.pdata > to_date(‘2008-01-01’,‘yyyy-mm-dd’);
–分区表操作 --增加一个分区
alter table Partition_Test add partition part_05 values less than (10020) tablespace dinya_space03
–查询分区数据
select * from Partition_FHTest partition(part_fh02) t
–修改分区里的数据
update Partition_FHTest partition(part_fh02) t set t.PITEM = ‘JZHUA’ where t.pid = ‘1961’
–删除分区里的数据
delete from Partition_FHTest partition(part_fh02) t where t.pid = ‘1961’
–合并分区
create table Partition_HB
(
PID number not null,
PITEM varchar2(200),
PDATA date not null
)
partition by range(PID)
(
partition part_01 values less than(50000) tablespace dinya_space01,
partition part_02 values less than(100000) tablespace dinya_space02,
partition part_03 values less than(maxvalue) tablespace dinya_space03
)
insert into Partition_HB(PID,PITEM,PDATA) select h.id,h.userid,h.rectime from st_handle h
select * from Partition_HB partition(part_03) t where t.pid = ‘100001’
alter table Partition_HB merge partitions part_01,part_02 into partition part_02;
–拆分分区
– spilt partition 分区名 at(这里是一个临界区，比如：50000就是说小于50000的放在part_01，而大于50000的放在part_02中)
alter table Partition_HB split Partition part_02 at (50000) into (Partition part_01 tablespace dinya_space01, Partition part_02 tablespace dinya_space02);
–更改分区名
alter table Partition_HB rename Partition part_01_test to part_02;
五、Oracle索引分区表操作
分区表和一般表一样可以建立索引，分区表可以创建局部索引和全局索引。当分区中出现许多事务并且要保证所有分区中的数据记录的唯一性时采用全局索引。全局索引建立时 global 子句允许指定索引的范围值，这个范围值为索引字段的范围值。其实理论上有3中分区索引。
索引分区是在您建立了表分区后，要建索引就必须是建立索引分区。分2大类：一类是把索引信息建立在各个分区上，这叫局部索引分区（或叫本地索引分区）。另一类是把索引集中起来，叫全局索引。
Global索引（全局索引）：
对于 global 索引，可以选择是否分区，而且索引的分区可以不与表分区相对应。当对分区进行维护操作时，通常会导致全局索引的 Invalid，必须在执行完操作后 Rebuild。Oracle9i 提供了 Update Global Indexes 语句，可以在进行分区维护的同时重建全局索引。
1：索引信息的存放位置与父表的Partition（分区）信息完全不相干。甚至父表是不是分区表都无所谓的。
create index dinya_idx_t on dinya_test(item_id) global partition by range(item_id) (
partition idx_1 values less than (1000) tablespace dinya_space01,
partition idx_2 values less than (10000) tablespace dinya_space02,
partition idx_3 values less than (maxvalue) tablespace dinya_space03
);
2：但是在这种情况下，如果父表是分区表，要删除父表的一个分区都必须要更新Global Index ,否则索引信息不正确
ALTER TABLE TableName DROP PARTITION PartitionName Update Global Indexes
建立方法：
create index ind_2 on sales (amount_sold)
global partition by range (amount_sold)
(partition d1 ,
partition d2);
因为全局索引的首个字段必须是range字段，所以就无所谓前缀和非前缀了，都是前缀。
Local索引（局部索引）：
对于 local 索引，每一个表分区对应一个索引分区（就是说一个分区表一个字段只可以建一个局部索引），当表的分区发生变化时，索引的维护由 Oracle 自动进行；
1：索引信息的存放位置依赖于父表的Partition（分区）信息，换句话说创建这样的索引必须保证父表是Partition（分区），索引信息存放在父表的分区所在的表空间。
2：但是仅可以创建在父表为HashTable或者composite分区表的。
3：仅可以创建在父表为HashTable或者composite分区表的。并且指定的分区数目要与父表的分区数目要一致。
create index dinya_idx_t on dinya_test(item_id) local (
partition idx_1 tablespace dinya_space01,
partition idx_2 tablespace dinya_space02,
partition idx_3 tablespace dinya_space03
);
不指定索引分区名直接对整个表建立索引
create index dinya_idx_t on dinya_test(item_id);
建立方法：
create index ind_1 on dept (deptno)
local
(partition d1 ,
partition d2);
（1）局部前缀索引分区和局部非前缀分区。如果您拟建立的索引的首个字段，和进行分区时的range列一样，那就是局部前缀索引分区。
优点是：理论上（我认为的），比方说您以年代为range分区，2007年一个分区、2008年一个分区，然后您又在这个时间列上建立了局部前缀索引分区，那么ORACLE就会直接利用这个区上的索引仅进行这个分区上的搜索，所以效率会很高。
在我建立的2000万的表中进行查询，实践是，这个局部前缀复合索引的花销cost是5，而没有分区前是4。当然这也无所谓了。又进行了其他几个查询，其cost都相差无几。
（2）局部非前缀索引。如果您建立索引的列的首个字段不是range列，那么就叫局部非前缀索引。
优点是：如果您查一个电话号码，它在每年都会出现，当您要count汇总时，这种索引就会同时把这几个分区进行并行处理查询，速度理论上要快。
但我的试验比较令我失望：我建了一个2000万的无分区的表，然后把这个表又复制了一遍，进行了6个分区。但结果在对某列进行查询统计时，如果在一个分区，两者速度相差不大，分区的查询速度是：0.25m，无分区的查询速度是：0.065m。但在我期望的跨区统计时，分区的第一次统计时间是：61.875m，第二次是：10m；而无分区的表仅为：3.703m。

表分区操作

1、查看分区

--查询表的分区情况和相关信息
SELECT * FROM User_Tab_Partitions

2、手动分区

--手动分区 (考虑月及以上时间间隔分区)
--1、创建表时就创建分区
-- Create table
create table TABLE_NAME
(
  ...,
  interface_time    DATE
)
partition by range (PERIOD_DATE)
(
  partition PART_1811 values less than (TO_DATE(' 2018-12-01 08:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace EDW_COM_DAT --指定TABLESPACE_NAME
    pctfree 10
    initrans 1
    maxtrans 255 
    storage
    (
      initial 8M
      next 1M
      minextents 1
      maxextents unlimited
    ),
  partition PART_1812 values less than (TO_DATE(' 2019-01-01 08:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace EDW_COM_DAT
    ,
  partition PART_1901 values less than (TO_DATE(' 2019-02-01 08:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace EDW_COM_DAT
    ,
  partition PART_1902 values less than (TO_DATE(' 2019-03-01 08:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace EDW_COM_DAT
    ,
  partition PART_1903 values less than (TO_DATE(' 2019-04-01 08:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace EDW_COM_DAT
    ,
  partition PART_1904 values less than (TO_DATE(' 2019-05-01 08:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace EDW_COM_DAT
   ,
  partition PART_1905 values less than (TO_DATE(' 2019-06-01 08:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace EDW_COM_DAT
    ,
  partition PART_1906 values less than (TO_DATE(' 2019-07-01 08:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace EDW_COM_DAT
    ,
  partition PART_1907 values less than (TO_DATE(' 2019-08-01 08:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace EDW_COM_DAT
);

--2、手动Add分区
ALTER TABLE TABLE_NAME ADD PARTITION PART_1811 values less than (TO_DATE(' 2018-12-01 08:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace EDW_COM_DAT --指定TABLESPACE_NAME,其他可以选择默认。

3、自动分区

--3、使用存储过程自动增加分区
CREATE OR REPLACE PROCEDURE ADD_TABLE_PARTITIONS_MANUL --表分区名称为：PART_当前月（YYMM） 分区条件为  小于每个月第一天8点
AS
V_CURRENT_DATE VARCHAR2(20);
V_PARTITION_DATE VARCHAR2(20);
V_EXECUTE_SQL VARCHAR2(200);
CURSOR CUR_STR IS 
SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,PARTITION_DATE,SYSDATE_YYYYMM FROM (
SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,'20'||SUBSTR(PARTITION_NAME,6,4) AS PARTITION_DATE,TO_CHAR(ADD_MONTHS(SYSDATE,5),'yyyymm') AS SYSDATE_YYYYMM FROM ( --TO_CHAR(ADD_MONTHS(SYSDATE,2) 对应数字实现创建更多分区
SELECT P.TABLE_NAME,MAX(PARTITION_NAME) PARTITION_NAME,MAX(P.TABLESPACE_NAME) TABLESPACE_NAME FROM USER_TAB_PARTITIONS P
WHERE PARTITION_NAME LIKE 'PART%' AND LENGTH(PARTITION_NAME) = 9 --AND TABLE_NAME ='DWT_EQP_STATE_A'
GROUP BY P.TABLE_NAME
)) WHERE SYSDATE_YYYYMM >= PARTITION_DATE ;
BEGIN 
   FOR CUR_RESULT IN CUR_STR LOOP 
       BEGIN 
             V_CURRENT_DATE:= CUR_RESULT.SYSDATE_YYYYMM;
             V_PARTITION_DATE:= CUR_RESULT.PARTITION_DATE;
                WHILE V_PARTITION_DATE <= V_CURRENT_DATE
                    LOOP
                          V_PARTITION_DATE:= TO_CHAR(ADD_MONTHS(TO_DATE(V_PARTITION_DATE,'yyyymm'),1),'yyyymm');
                          V_EXECUTE_SQL:='ALTER TABLE '||CUR_RESULT.TABLE_NAME||' add partition PART_'||TO_CHAR(TO_DATE(V_PARTITION_DATE,'yyyymm'),'yymm')|| ' values less than(to_date('''||TO_CHAR(ADD_MONTHS(TO_DATE(V_PARTITION_DATE,'YYYY-MM'),1),'YYYY-MM')||'-01 08:00:00'''||','||'''YYYY-MM-DD HH24:MI:SS'''||')) TABLESPACE '||CUR_RESULT.TABLESPACE_NAME;
                         -- INSERT INTO TMP1 VALUES(V_EXECUTE_SQL);
                          EXECUTE IMMEDIATE V_EXECUTE_SQL;
                          --COMMIT;
                    END LOOP;
       END;
   
END LOOP;

END ADD_TABLE_PARTITIONS_MANUL;

--自动分区 （天、周等分区一般都考虑自动创建分区，如果存储时间周期固定，需考虑删除分区并回收分区空间）
--4、自动增加分区
create table TABLE_NAME
(
  ...,
  interface_time       DATE default SYSDATE
)
TABLESPACE TABLESPACE_NAME  --Tablespace指明时和下面的自动分区语句之间不能有分割
PARTITION BY RANGE (period_date) --比较自动分区的时间字段
INTERVAL (NUMTODSINTERVAL(1,'day')) --1天增长分区 --INTERVAL (NUMTODSINTERVAL(0.5,'day')) 12小时增长分区
(
partition PART_180101 values less than (TO_DATE(' 2018-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS')) --分区基准时间
);

----------
TABLESPACE TABLESPACE_NAME
PARTITION BY RANGE (period_date)
INTERVAL (NUMTOYMINTERVAL(1,'month')) --一月增长分区
(
PARTITION PART_1812 VALUES LESS THAN (TO_DATE('2019-01-01', 'YYYY-MM-DD')) --分区基准时间
);

4、实践案例

-- CREATE TABLE
CREATE TABLE DWR_GLS_UNIT_HIS_AF_TMP
(
  SITE                 VARCHAR2(32) NOT NULL,
  FACTORY              VARCHAR2(32) NOT NULL,
  PERIOD_DATE          DATE NOT NULL,
  PERIOD               VARCHAR2(1) NOT NULL,
  SHIFT_NAME           VARCHAR2(32) NOT NULL,
  OBJECT_RRN           NUMBER(19) NOT NULL,
  ORG_RRN              NUMBER(19),
  IS_ACTIVE            VARCHAR2(1),
  UPDATED_BY           VARCHAR2(32),
  TRANS_TYPE           VARCHAR2(32),
  TRANS_TIME           TIMESTAMP(6),
  HISTORY_SEQ          VARCHAR2(32),
  HISTORY_SEQ_NO       NUMBER(3),
  COMPONENT_RRN        NUMBER(19),
  COMPONENT_ID         VARCHAR2(32),
  EQUIPMENT_ID         VARCHAR2(32),
  PROCESS_NAME         VARCHAR2(32),
  PROCEDURE_NAME       VARCHAR2(32),
  STEP_NAME            VARCHAR2(32),
  PART_NAME            VARCHAR2(32),
  JUDGE1               VARCHAR2(32),
  UNIT_IN_TIME         DATE,
  UNIT_OUT_TIME        DATE,
  CHAMBER_IN_TIME      DATE,
  CHAMBER_OUT_TIME     DATE,
  LOT_ID               VARCHAR2(32),
  INTERFACE_TIME       DATE DEFAULT SYSDATE,
  MANUFACTURE_TYPE     VARCHAR2(64),
  VCR_READ_COMPONENTID VARCHAR2(64),
  LOT_COMMENT          VARCHAR2(64)
)TABLESPACE EDW_GLS_DAT
PARTITION BY RANGE (PERIOD_DATE) --比较自动分区的时间字段
INTERVAL (NUMTODSINTERVAL(1,'day')) --1天增长分区
(
PARTITION SYS_P180101 VALUES LESS THAN (TO_DATE(' 2018-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS')) --分区基准时间
TABLESPACE EDW_GLS_DAT
    PCTFREE 10
    INITRANS 1
    MAXTRANS 255
    storage
    (
      initial 64K
      next 1M
      minextents 1
      maxextents unlimited
    )
);
--分区表本地索引建立
ALTER TABLE DWR_GLS_UNIT_HIS_AF_TMP
  ADD CONSTRAINT DWR_GLS_UNIT_HIS_AF_TMP_PK PRIMARY KEY (SITE, FACTORY, PERIOD_DATE, PERIOD, SHIFT_NAME, OBJECT_RRN)
  USING INDEX 
  LOCAL;
ALTER INDEX DWR_GLS_UNIT_HIS_AF_TMP_PK NOLOGGING;
CREATE INDEX DWR_GLS_UNIT_HIS_AF_01 ON DWR_GLS_UNIT_HIS_AF_TMP(PERIOD_DATE) NOLOGGING LOCAL ;
CREATE INDEX DWR_GLS_UNIT_HIS_AF_02 ON DWR_GLS_UNIT_HIS_AF_TMP(FACTORY) NOLOGGING LOCAL;

--查看分区
select table_name,partition_name from (
select TABLE_NAME,
       PARTITION_NAME,
       row_number() over(partition by table_name order by partition_position desc) rk
  from user_tab_partitions
  WHERE TABLE_NAME ='DWT_CT_OUTPUT_A_GLS'
  ;
--删除分区
/*ALTER TABLE DWT_CT_OUTPUT_A_GLS truncate PARTITION (SYS_P28075) drop STORAGE;
ALTER TABLE DWT_CT_OUTPUT_A_GLS DROP partition (SYS_P28075) ;*/

ddttoop

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
About Oracle 分区(Partitions)

Oracle 分区相关：一、Oracle分区简介ORACLE的分区是一种处理超大型表、索引等的技术。分区是一种“分而治之”的技术，通过将大表和索引分成可以管理的小块，从而避免了对每个表作为一个大的、单独的对象进行管理，为大量数据提供了可伸缩的性能。分区通过将操作分配给更小的存储单元，减少了需要进行管理操作的时间，并通过增强的并行处理提高了性能，通过屏蔽故障数据的分区，还增加了可用性。二、O...
复制链接

扫一扫