分区表的测试与维护

reference:http://blog.oracle.com.cn/index.php/85074/viewspace-7167.html

 

http://www.itpub.net/viewthread.php?tid=895604

http://www.psoug.org/reference/partitions.html

 

--建立测试分区表
CREATE TABLE T_PartTest
(
  Hsday   DATE,
  HsValue VARCHAR2(15)
)
TABLESPACE DAT_ANAL
PARTITION BY RANGE (hsday)
(PARTITION T_PartTest_P0701 VALUES LESS THAN (TO_DATE('2007-01-01','YYYY-MM-DD')) TABLESPACE DAT_ANAL_01,
 PARTITION T_PartTest_P0702 VALUES LESS THAN (TO_DATE('2007-02-01','YYYY-MM-DD')) TABLESPACE DAT_ANAL_02,
 PARTITION T_PartTest_OTHRE VALUES LESS THAN (MAXVALUE) TABLESPACE DAT_ANAL);

--插入数据到分区
INSERT INTO T_PartTest
VALUES(TO_DATE('2006-12-01','YYYY-MM-DD'),'第一条记录');
INSERT INTO T_PartTest
VALUES(TO_DATE('2007-01-01','YYYY-MM-DD'),'第二条记录');
INSERT INTO T_PartTest
VALUES(TO_DATE('2007-02-01','YYYY-MM-DD'),'第三条记录');
INSERT INTO T_PartTest
VALUES(TO_DATE('2007-03-01','YYYY-MM-DD'),'第四条记录');
INSERT INTO T_PartTest
VALUES(TO_DATE('2007-04-01','YYYY-MM-DD'),'第五条记录');
INSERT INTO T_PartTest
VALUES(TO_DATE('2007-05-01','YYYY-MM-DD'),'第六条记录');
INSERT INTO T_PartTest
VALUES(TO_DATE('2007-06-01','YYYY-MM-DD'),'第七条记录');
INSERT INTO T_PartTest
VALUES(TO_DATE('2007-07-01','YYYY-MM-DD'),'第八条记录');
INSERT INTO T_PartTest
VALUES(TO_DATE('2007-08-01','YYYY-MM-DD'),'第九条记录');
INSERT INTO T_PartTest
VALUES(TO_DATE('2007-09-01','YYYY-MM-DD'),'第十条记录');
INSERT INTO T_PartTest
VALUES(TO_DATE('2007-10-01','YYYY-MM-DD'),'第11条记录');
INSERT INTO T_PartTest
VALUES(TO_DATE('2007-11-01','YYYY-MM-DD'),'第12条记录');
INSERT INTO T_PartTest
VALUES(TO_DATE('2007-12-01','YYYY-MM-DD'),'第13条记录');

--查询分区记录
SELECT * FROM T_PartTest PARTITION(T_PartTest_P0701);
SELECT * FROM T_PartTest PARTITION(T_PartTest_OTHRE);
SELECT * FROM T_PartTest;

--移动分区
ALTER TABLE T_PartTest MOVE PARTITION T_PartTest_OTHRE TABLESPACE DAT_ANAL_06;
SELECT segment_name,partition_name,tablespace_name
  FROM dba_segments
 WHERE wner=USER
   AND segment_name='T_PARTTEST'
   AND partition_name='T_PARTTEST_OTHRE';
ALTER TABLE T_PartTest MOVE PARTITION T_PartTest_OTHRE TABLESPACE DAT_ANAL;

--增加一个分区(增加的分区必须大于最后最大的分区)
ALTER TABLE T_PartTest ADD PARTITION T_PartTest_P0702
VALUES LESS THAN (TO_DATE('2007-02-01','YYYY-MM-DD')) TABLESPACE DAT_ANAL_02;
--ORA-14074: partition bound must collate higher than that of the last partition
--先删除最大分区，再增加分区(执行成功)
ALTER TABLE T_PartTest DROP PARTITION T_PartTest_OTHRE;
--增加分区
ALTER TABLE T_PartTest ADD PARTITION T_PartTest_P0702
VALUES LESS THAN (TO_DATE('2007-02-01','YYYY-MM-DD')) TABLESPACE DAT_ANAL_02;
--增加最大分区
ALTER TABLE T_PartTest ADD PARTITION T_PartTest_OTHRE
VALUES LESS THAN (MAXVALUE) TABLESPACE DAT_ANAL;

-->联结分区(必须是HASH分区才可用)
ALTER TABLE T_PartTest COALESCE PARTITION;
--ORA-14259: table is not partitioned by Hash method

--删除分区(将导致LOCAL INDEX中删除相应的索引分区，同时GLOBAL索引将被置为UNUSABLE)
ALTER TABLE T_PartTest DROP PARTITION T_PartTest_P0701;
SELECT * FROM T_PartTest PARTITION(T_PartTest_P0701);
SELECT * FROM T_PartTest PARTITION(T_PartTest_OTHRE);
SELECT * FROM T_PartTest;

--有三种方法将GLOBAL置为可用状态：
--方法 1：
--    ALTER TABLE T_PartTest DROP PARTITION T_PartTest_P0701;
--    ALTER INDEX Idx_T_PartTest REBUILD;
--如果sale_area_ix是全局range-partitioned索引，则每个分区都需要rebuild
--    Alter INDEX Idx_T_PartTest REBUILD PARTITION T_PartTest_P0701_Ix;
--方法 2:
--  DELETE FROM T_PartTest WHERE hsday < TO_DATE(???);
--  ALTER TABLE T_PartTest DROP PARTITION T_PartTest_P0701;
--方法 3:
--  ALTER TABLE T_PartTest DROP PARTITION T_PartTest_P0701 UPDATE GLOBAL INDEXES;
--当DROP一个PARTITION时，如果有CONSTRAINT在表上，可能会导致DROP失败，可使用以下方法：
--方法 1:
--  ALTER TABLE T_PartTest DISABLE CONSTRANT Con_T_PartTest;
--  ALTER TABLE T_PartTest DROP PARTITION T_PartTest_P0701;
--  ALTER TABLE ENABLE CONSTRAINT Con_T_PartTest;
--方法 2:
--  DELETE FROM T_PartTest hsday < TO_DATE(???);
--  ALTER TABLE T_PartTest DROP PARTITION T_PartTest_P0701;

--重命名分区
SELECT segment_name,partition_name
  FROM dba_segments
 WHERE wner=USER
   AND segment_name='T_PARTTEST';
ALTER TABLE T_PartTest RENAME PARTITION T_PartTest_OTHRE TO T_PartTest_OTHRE1;
SELECT segment_name,partition_name
  FROM dba_segments
 WHERE wner=USER
   AND segment_name='T_PARTTEST';
ALTER TABLE T_PartTest RENAME PARTITION T_PartTest_OTHRE1 TO T_PartTest_OTHRE;

--清理分区(索引维护同DROP分区)
ALTER TABLE T_PartTest TRUNCATE PARTITION T_PartTest_OTHRE;
SELECT * FROM T_PartTest PARTITION(T_PartTest_OTHRE);

--将分区的数据转换到相应分区中去(T_PartTest_P0703必须是一个不存在的分区)
/*
--辅助脚本
SELECT 'ALTER TABLE T_PartTest SPLIT PARTITION T_PartTest_OTHRE AT (TO_DATE('''||TO_CHAR(Hsday, 'YYYYMM') || ''',''YYYYMM'')) '||Chr(10)||
       'INTO (PARTITION T_PartTest_P' ||TO_CHAR(Hsday,'YYYYMM') || ', PARTITION T_PartTest_OTHRE);'
  FROM T_PartTest
 GROUP BY TO_CHAR(Hsday, 'YYYYMM')
 ORDER BY TO_CHAR(Hsday, 'YYYYMM');
*/
ALTER TABLE T_PartTest SPLIT PARTITION T_PartTest_OTHRE AT (TO_DATE('200703','YYYYMM'))
 INTO (PARTITION T_PartTest_P0703,PARTITION T_PartTest_OTHRE);

--合并分区(INTO后的分区必须是一个不存在的分区)
ALTER TABLE T_PartTest MERGE PARTITIONS T_PARTTEST_P0702,T_PartTest_P0703 INTO PARTITION T_PartTest_Merge;

--将表转换(交换)到分区表
CREATE TABLE T_PartTest1
(
  Hsday   DATE,
  HsValue VARCHAR2(15)
);
INSERT INTO T_PartTest1
VALUES(TO_DATE('2007-03-01','YYYY-MM-DD'),'第4条记录');

--转换表到分区(数据值必须符合分区定义，T_PartTest_OTHRE改成T_PartTest_P0701就不行)
--不符合分区规则，则会报错，则需要指定在后面增加WITHOUT VALIDATION 来禁止检查，使用INCLUDING INDEXES将索引一起转移
SELECT * FROM T_PartTest1;
SELECT * FROM T_PartTest PARTITION(T_PartTest_OTHRE);
ALTER TABLE T_PartTest EXCHANGE PARTITION T_PartTest_OTHRE WITH TABLE T_PartTest1;
SELECT * FROM T_PartTest1;
SELECT * FROM T_PartTest PARTITION(T_PartTest_OTHRE);

--查如下记录，发现分区T_PartTest_OTHRE的内容和T_PartTest1表的内容进行了交换
SELECT * FROM T_PartTest;
SELECT * FROM T_PartTest PARTITION(T_PartTest_OTHRE);
SELECT * FROM T_PartTest1;

 

 

 

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

 

below from :http://www.itpub.net/viewthread.php?tid=895604

 

PARTITION表SPLIT操作的内部过程和注意事项

PARTIOTION表对于处理巨量数据来讲是个非常重要的方法
    我对于系统中的大表和超大型表，主要的处理方式是根据公司前台应用的操作特性，对于一定周期后，只进行查询的数据，进行分区压缩，只是保留近期内可能还涉及DML操作的表在“最近”(我的分区名称：recent)中，用普通的存储方式进行保存。
    而对于PARTITION的表来讲，之后的管理工作还是比较多的。
    其中涉及到的工作中比较多的就是SPLIT(分裂)和DROP PARTITION（11g中应该就不会这样痛苦了）。
    通过我对SPLIT过程的分析，发现SPLIT内部操作的基本过程：
    1.发出命令后，ORACLE在我制定的新分区所在表空间中建立一个TEMPERORARY类型的表，其实这个表就是未来的新分区。将符合条件的数据导入。
    2.在完成第一步后，ORACLE在原被分区的分区所在表空间上，又建立了一个TEMPRORARY类型的临时表，这时ORACLE开始将不属于新分区的数据在导入到这张临时表中。如果使用了UPDATE INDEXES的话，ORACLE最后还要维护本地索引。
    3.完成后，改变表名（更改数据字典）
    知道了这样的过程，我们在做SPLIT操作时，需要注意：
    在被分区的分区所在表空间上，需要有足够的剩余表空间。当然，如果使用UPDATE INDEXES的话，索引所在的表空间也需要必要的剩余表空间。
    如果说有LOCAL INDEX，在系统资源允许的情况下，带上 update indexes子句是不错的选择。
    另外，这个分裂过程也解除了我的一个忧虑，被分裂的分类数据是否要重组？现在看是没有必要的，因为ORACLE已经为你重组了这个分区中的数据了。
-------
   ORACLE版本：10.2.0.1
   OS        ：HP-UX B.11.23 U ia64

           When spliting a partition table ,I found a temporary table was created in the target tablespace and increasing it's size continually. When the temporary table stoped increasing  size, I found a new temporary table was created in the tablespace where the partition of the table was splited and increasing it's size too.
          I found the temporay tables was disappeared when the oracle prompted the "split" command was executed over. and I computed the sum size of two partitons, discovered the sum is smaller than one paratition which was splitd (Maybe you can compute a large different if the paration which was splited was often executed DMLs ). This process very like  you execute a command insert /*+apped*/  into xxx select * from xxx_resource. you will discover the target table's size is increasing during this proces.

 

下面一段话说明分开的两个新的分区已经重组了：
“Oracle implements a SPLIT PARTITION operation by creating two new partitions and redistributing the rows from the partition being split into the two new partitions. This is an expensive operation because it is necessary to scan all the rows of the partition being split and then insert them one-by-one into the new partitions.”

Thanks Larry. I rewrote your words as follows so others can read better. Let me know if I misunderstood any part of it, especially the sentence "you'll get a larger difference".

Larry yue_01 says:

When splitting a partitioned table, I found that a global temporary table was created in the target tablespace and increasing in size continually. When the temporary table stopped increasing its size, I found a new temporary table was created in the tablespace where the partition of the table was split and this new temporary table was increasing its size too.

I found the temporay tables disappeared when the "split" command finished. I computed the sum of the sizes of two partitions, and discovered the sum is smaller than the original partition which was split (Maybe you'll get a larger difference if the split partition had frequent DMLs running against it). This process is very much like insert /*+ apped* / into xxx select * from xxx_resource. You will discover the target table's size is increasing during this process.

 

If 重组 or reorganization or reorg means re-storing the rows sorted by some order, obviously no. Splitting a partition will not do that. If saying this reorg is like alter table move or move partition or CTAS, yes. That's exactly what it does. Empty holes in the middle of a segment will be eliminated.

We need Larry Yue's clarification on "被分裂的分类数据是否要重组". In what sense does Larry think this reorg is not needed or not done by Oracle?

Yong Huang

 

 

Hello
   First of all ,I want to say many thanks !
   Thank you for writing back this.
   Thank you for rewrting my post. -
   And I really found that there is a error characeter in the statement "被分裂的分类数据是否要重组" .In fact , I want to express that "被分裂的分区数据是否要重组".
   "重组" what I wrote is meanings that empty holes in the middle of a segment will be eliminated,in my opinion.In dba's job, the space management is very much important.Sometimes, We need use like 'move' command or 'exp/imp' tools or etc. to dispose some tables space problem.But, it is  not needed when we split a partition table.

     Glad to discuss anything with you.

 

回复 #6 Yong Huang 的帖子

Didn't you see "two new partitions"? Who told you only "alter table move" can reorg tables? If you copy the data from a table to a newly created table, isn't a reorg?