Oracle分區(qū)表管理的一些筆記

Oracle分區(qū)表的管理筆記(僅限于對(duì)普通表，即堆表的分區(qū)管理，IOT跟CLUSTER TABLE不再討論范圍內(nèi))

1. 增加分區(qū)(add partition)
語(yǔ)法是：alter table xxx add partition…
需要注意的是如果分區(qū)中存在maxvalue或default分區(qū)add partition會(huì)報(bào)錯(cuò)，應(yīng)使用split
-
 

如：

Alter table t_range add partition p5 values less than (50) [tablespace users];
--50 要大于之前分區(qū)的所有值
Alter table t_list add partition p5 values (7,8,9) [tablespace users];
--7,8,9均不能在之前分區(qū)中出現(xiàn)
Alter table t_hash add partition [p5] [tablespace users];

增加子分區(qū)：
Alter table xxx modify partition p1 add subpartition …
如：增加RANGE-HASH子分區(qū)
ALTER TABLE diving MODIFY PARTITION locations_us
      ADD SUBPARTITION us_locs5 TABLESPACE us1;

Range,list增加分區(qū)不會(huì)影響索引(包括global 跟local)，HASH增加分區(qū)會(huì)讓數(shù)據(jù)重新分配，產(chǎn)生IO，如果不指定update indexes 選項(xiàng)則會(huì)導(dǎo)致有數(shù)據(jù)移動(dòng)的索引unusable，需要重新編譯。
當(dāng)然，我們說(shuō)的對(duì)索引的影響都是在表中有數(shù)據(jù)的情況下，沒(méi)數(shù)據(jù)當(dāng)然影響不到索引了。

2. 合并分區(qū)(coalesce partition)
   Alter table xxx coalesce partion [update indexes];
   Alter table xxx modify partition p1 coalesce subpartition;
   僅適用于HASH分區(qū)或子分區(qū)，合并一次會(huì)減少一個(gè)分區(qū)(最少能減少到1個(gè))，數(shù)據(jù)重新分配，產(chǎn)生IO，有數(shù)據(jù)移動(dòng)的索引失效(如果不指定update indexes的話).

3. 刪除分區(qū)(drop partition)
Alter table xxx drop partition ppp;
刪除子分區(qū)：
Alter table xxx drop subpartition ppp;
此功能hash不支持。同時(shí)要注意，刪除分區(qū)會(huì)同時(shí)刪除該分區(qū)內(nèi)數(shù)據(jù)。
同樣，如果不指定update indexes的話該操作會(huì)導(dǎo)致GLOBAL索引失效，而LOCAL不會(huì)，因?yàn)閷?duì)應(yīng)的LOCAL索引分區(qū)也被刪除了嘛，其他分區(qū)的LOCAL不會(huì)受到影響。

4. 交換分區(qū)(exchange partition)
Alter table tb1 exchange partition/subpartition p1 with table tb2;
據(jù)說(shuō)是采用了更改數(shù)據(jù)字典的方式，所以速度比較快。
可以是分區(qū)跟非分區(qū)表交換，子分區(qū)跟非分區(qū)表交換，組合分區(qū)跟分區(qū)表交換。
例如：
組合分區(qū)跟分區(qū)表交換：
CREATE TABLE t1 (i NUMBER, j NUMBER)
     PARTITION BY HASH(i)
       (PARTITION p1, PARTITION p2);

CREATE TABLE t2 (i NUMBER, j NUMBER)
     PARTITION BY RANGE(j)
     SUBPARTITION BY HASH(i)
        (PARTITION p1 VALUES LESS THAN (10)
            SUBPARTITION t2_pls1
            SUBPARTITION t2_pls2,
         PARTITION p2 VALUES LESS THAN (20)
            SUBPARTITION t2_p2s1
            SUBPARTITION t2_p2s2));

ALTER TABLE t2 EXCHANGE PARTITION p1 WITH TABLE t1
     WITH VALIDATION;

如果指定WITH VALIDATION(默認(rèn)) 會(huì)對(duì)交換進(jìn)來(lái)的數(shù)據(jù)進(jìn)行合法檢查，看是否符合該分區(qū)的規(guī)則，WITHOUT VALIDATION 會(huì)忽略合法檢查(比如ID=12的記錄此時(shí)可以交換到ID VALUES LESS THAN (10)的分區(qū)里)，但如果表上有primary key 或unique 約束的話，指定without validation會(huì)被忽略。
同樣，如果不指定UPDATE INDEXES ，GLOBAL 索引會(huì)失效，需要重新編譯。

5. 合并分區(qū)(merge partitions)
Alter table xxx merge partitions/subpartitions p1,p2 into partiton/subpartition p3 [TABLESPACE tablespace_name];
HASH不適用，因?yàn)樗蠧OALESCE了嘛。
表分區(qū)必須是相鄰的。
跟COALESCE一樣，會(huì)產(chǎn)生IO,數(shù)據(jù)量大的話，IO也是相當(dāng)大的。
同樣可以用UPDATE INDEXES 避免索引失效

6. 修改LIST分區(qū)—ADD VALUES
Alter table xxx modify partition/subpartition p1 add values(7,9);
要注意的是，增加的VALUES不能在其他分區(qū)列的VALUES值中存在，也不能在DEFAULT分區(qū)(如果有的話)中有相應(yīng)VALUES.
不會(huì)影響索引

7. 修改LIST 分區(qū)—DROP VALUES
Alter table xxx modify partition/subpartition p1 drop values(8,9);
同樣,刪除的values 不能存在記錄.
不會(huì)影響索引

8. 拆分分區(qū)(split partitions)
功能與MERGE PARTITIONS相反。通常我們會(huì)用來(lái)拆分MAXVALUE/DEFAULT分區(qū)。
Range partition:
Alter table xxx split partition/subpartition p1 at (15) into (partition/subpartition p1_new1,partition/subpartition p1_new2);
List partition:
Alter table xxx split partition/subpartition p1 values(15,16) into (partition/subpartition p1_new1,partition/subpartition p1_new2);
原分區(qū)中符合新值定義的記錄會(huì)存入第一個(gè)分區(qū),其他存入第二個(gè)分區(qū),當(dāng)然,在新分區(qū)后面可以指定屬性,比如TABLESPACE。
HASH分區(qū)不適用。
會(huì)產(chǎn)生IO
同樣，可用update indexes 來(lái)避免索引失效

9. 截?cái)喾謪^(qū)(truncate partition)
跟TRUNCATE TABLE一樣，截?cái)嘣摲謪^(qū)內(nèi)的數(shù)據(jù)。
Alter table xxx truncate partition/subpartition p1;
同樣，可用update indexes 來(lái)避免索引失效

10. 移動(dòng)分區(qū)(move partition)
Alter table xxx move partition/subpartition p1 …;
有些功能比如改變分區(qū)表空間,modify partition就做不到，此時(shí)就可以用move partition來(lái)做。
Use the MOVE PARTITION clause of the ALTER TABLE statement to:
• Re-cluster data and reduce fragmentation
• Move a partition to another tablespace
• Modify create-time attributes
• Store the data in compressed format using table compression

如：
ALTER TABLE parts MOVE PARTITION depot2
     TABLESPACE ts094 NOLOGGING COMPRESS;
(如果指定compress，affects only future storage, but not existing data.)

同樣，可用update indexes 來(lái)避免索引失效

11. 重命名分區(qū)(rename partition)
Alter table xxx rename partition/subpartition p1 to p1_new;
跟重命名表差不多。

12. 修改分區(qū)默認(rèn)屬性(modify default attributes)
修改表屬性：alter table xxx modify default attributes …
修改分區(qū)屬性(適用于組合分區(qū))：alter table xxx modify default attributes for partition p1 …
只對(duì)以后添加的分區(qū)產(chǎn)生影響，適用于所有分區(qū)，其中hash分區(qū)只能修改表空間屬性。
如：
Alter table xxx modify default attributes tablespace users;

13. 修改子分區(qū)模板屬性(set subpartition template)
Alter table xxx set subpartition template (…);
僅影響以后的子分區(qū)，當(dāng)前的子分區(qū)屬性不會(huì)改變
如：
Alter table xxx set subpartition template
(partition p1 tablespace tbs_1,
Partition p2 tablespace tbs_2);
如果要取消掉子分區(qū)模板：
Alter table xxx set subpartition template ();

服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)！虛擬主機(jī)域名注冊(cè)頂級(jí)提供商！15年品質(zhì)保障！--億恩科技[ENKJ.COM]