成人午夜视频全免费观看高清-秋霞福利视频一区二区三区-国产精品久久久久电影小说-亚洲不卡区三一区三区一区

如何理解ADD和DROP分區(qū)

如何理解ADD和DROP分區(qū)，針對這個(gè)問題，這篇文章詳細(xì)介紹了相對應(yīng)的分析和解答，希望可以幫助更多想解決這個(gè)問題的小伙伴找到更簡單易行的方法。

創(chuàng)新互聯(lián)建站專業(yè)為企業(yè)提供吉州網(wǎng)站建設(shè)、吉州做網(wǎng)站、吉州網(wǎng)站設(shè)計(jì)、吉州網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計(jì)與制作、吉州企業(yè)網(wǎng)站模板建站服務(wù)，10年吉州做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。

add和drop分區(qū)
語法：
ALTER TABLE t_pe_r
      ADD PARTITION p4 VALUES LESS THAN (30 );
ALTER TABLE t_pe_l
      ADD PARTITION p4 VALUES  (30 );
ALTER TABLE t_pe_h
      ADD PARTITION p3;

alter table t_pe_r drop partition p3;

限制：如果范圍分區(qū)使用maxvalue選項(xiàng)則報(bào)錯(cuò)ORA-14074: 分區(qū)界限必須調(diào)整為高于最后一個(gè)分區(qū)界限
      如果LIST分區(qū)使用了default選項(xiàng)則報(bào)錯(cuò)ORA-14323: 在 DEFAULT 分區(qū)已存在時(shí)無法添加分區(qū)
      drop 分區(qū)不能使用在HASH分區(qū)表中，報(bào)錯(cuò)ORA-14255: 未按范圍, 組合范圍或列表方法對表進(jìn)行分區(qū)

我們這里討論HASH、list、range 3方式下add partition和drop partition關(guān)于local索引，global索引和普通索引的狀態(tài)。
使用腳本
drop table t_pe_r ;
drop table t_pe_l;
drop table t_pe_h;
CREATE TABLE t_pe_r (i NUMBER, j NUMBER , f varchar2(20),k varchar2(20))
     PARTITION BY RANGE(j)
        (PARTITION p1 VALUES LESS THAN (10),
         PARTITION p2 VALUES LESS THAN (20));
create index t_pe_r_n on t_pe_r(i);
create index t_pe_r_l on t_pe_r(j) local;
create index t_pe_r_g on t_pe_r(f)
GLOBAL PARTITION BY hash (f)
(partition pg1 ,
 partition pg2);

CREATE TABLE t_pe_l (i NUMBER, j NUMBER , f varchar2(20),k varchar2(20))
     PARTITION BY list (j)
        (PARTITION p1 VALUES (10),
         PARTITION p2 VALUES (20));
create index t_pe_l_n on t_pe_l(i);
create index t_pe_l_l on t_pe_l(j) local;
create index t_pe_l_g on t_pe_l(f)
GLOBAL PARTITION BY hash (f)
(partition pg1 ,
 partition pg2);

CREATE TABLE t_pe_h (i NUMBER, j NUMBER , f varchar2(20),k varchar2(20))
     PARTITION BY hash(j)
        (PARTITION p1 ,
         PARTITION p2 );
create index t_pe_h_n on t_pe_h(i);
create index t_pe_h_l on t_pe_h(j) local;
create index t_pe_h_g on t_pe_h(f)
GLOBAL PARTITION BY hash (f)
(partition pg1 ,
 partition pg2);
 
insert into t_pe_r
values(2,5,'a','A');
insert into t_pe_r
values(1,15,'b','B');
insert into t_pe_l
values(2,10,'a','A');
insert into t_pe_l
values(1,20,'b','B');
insert into t_pe_h
values(2,5,'a','A');
insert into t_pe_h
values(1,15,'b','B');
insert into t_pe_h
values(3,25,'c','C');

select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_r_l';
select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_r_g';
select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_r_n';

select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_l_l';
select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_l_g';
select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_l_n';

select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_h_l';
select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_h_g';
select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_h_n';
下面進(jìn)行添加
ALTER TABLE t_pe_r
      ADD PARTITION p4 VALUES LESS THAN (30 );
ALTER TABLE t_pe_l
      ADD PARTITION p4 VALUES  (30 );
ALTER TABLE t_pe_h
      ADD PARTITION p3;

然后查看索引狀態(tài)
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_r_l';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_R_L                       USABLE   P2
T_PE_R_L                       USABLE   P1
T_PE_R_L                       USABLE   P4
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_r_g';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_R_G                       USABLE   PG2
T_PE_R_G                       USABLE   PG1
SQL> select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_r_n';
 
INDEX_NAME                     STATUS
------------------------------ --------
T_PE_R_N                       VALID
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_l_l';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_L_L                       USABLE   P2
T_PE_L_L                       USABLE   P1
T_PE_L_L                       USABLE   P4
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_l_g';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_L_G                       USABLE   PG2
T_PE_L_G                       USABLE   PG1
SQL> select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_l_n';
 
INDEX_NAME                     STATUS
------------------------------ --------
T_PE_L_N                       VALID
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_h_l';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_H_L                       USABLE   P2
T_PE_H_L                       UNUSABLE P1
T_PE_H_L                       UNUSABLE P3
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_h_g';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_H_G                       UNUSABLE PG2
T_PE_H_G                       UNUSABLE PG1
SQL> select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_h_n';
 
INDEX_NAME                     STATUS
------------------------------ --------
T_PE_H_N                       UNUSABLE
可以看到實(shí)際上LIST和HASH的所有索引都沒有受到影響，而HASH分區(qū)則不同，所有的索引均失效，添加分區(qū)后通過HASH算法重新分布了行，那么應(yīng)該ROWID也受到了影響，可以DUMP出來看看。
索引進(jìn)行rebuild
alter index T_PE_H_L rebuild  partition p1;
在進(jìn)行HASH分區(qū)的加入分區(qū)時(shí)候最好如下：
ALTER TABLE t_pe_h
      ADD PARTITION p3 update indexes; 加上UPDATE INDEXES，同時(shí)實(shí)際上HASH的分區(qū)個(gè)數(shù)應(yīng)該是2的N次方，不然會(huì)分布不均勻。
然后我們測試下DROP partition，drop partition只能用于RANGE 和LIST分區(qū)方式，HASH分區(qū)不能使用：
You can drop partitions from range, list, or composite range-list partitioned tables. For hash-partitioned tables, or hash subpartitions of range-hash partitioned

tables, you must perform. a coalesce operation instead.
使用腳本：
alter table t_pe_r drop partition p1;
alter table t_pe_l drop partition p1;
然后觀察：
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_r_l';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_R_L                       USABLE   P2
T_PE_R_L                       USABLE   P4
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_r_g';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_R_G                       UNUSABLE PG2
T_PE_R_G                       UNUSABLE PG1
SQL> select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_r_n';
 
INDEX_NAME                     STATUS
------------------------------ --------
T_PE_R_N                       UNUSABLE
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_l_l';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_L_L                       USABLE   P2
T_PE_L_L                       USABLE   P4
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_l_g';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_L_G                       UNUSABLE PG2
T_PE_L_G                       UNUSABLE PG1
SQL> select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_l_n';
 
INDEX_NAME                     STATUS
------------------------------ --------
T_PE_L_N                       UNUSABLE
可以看到普通索引，全局索引均已經(jīng)失效，但是本地索引卻不受影響。
如果我們帶上UPDATE INDEXES會(huì)怎么樣？
SQL> alter table t_pe_r drop partition p1 update indexes;
 
Table altered
SQL> alter table t_pe_l drop partition p1 update indexes;
 
Table altered
 
SQL>
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_r_l';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_R_L                       USABLE   P2
T_PE_R_L                       USABLE   P4
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_r_g';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_R_G                       USABLE   PG2
T_PE_R_G                       USABLE   PG1
SQL> select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_r_n';
 
INDEX_NAME                     STATUS
------------------------------ --------
T_PE_R_N                       VALID
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_l_l';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_L_L                       USABLE   P2
T_PE_L_L                       USABLE   P4
SQL> select INDEX_NAME ,STATUS,a.partition_name from dba_ind_partitions a where lower(index_name)='t_pe_l_g';
 
INDEX_NAME                     STATUS   PARTITION_NAME
------------------------------ -------- ------------------------------
T_PE_L_G                       USABLE   PG2
T_PE_L_G                       USABLE   PG1
SQL> select INDEX_NAME ,STATUS from dba_indexes where lower(index_name)='t_pe_l_n';
 
INDEX_NAME                     STATUS
------------------------------ --------
T_PE_L_N                       VALID
可以看到加上UPDATE INDEXES 就會(huì)自動(dòng)重建失效的索引。
結(jié)論：
1、如果范圍分區(qū)使用maxvalue選項(xiàng)則報(bào)錯(cuò)ORA-14074: 分區(qū)界限必須調(diào)整為高于最后一個(gè)分區(qū)界限
2、如果LIST分區(qū)使用了default選項(xiàng)則報(bào)錯(cuò)ORA-14323: 在 DEFAULT 分區(qū)已存在時(shí)無法添加分區(qū)
3、drop 分區(qū)不能使用在HASH分區(qū)表中，報(bào)錯(cuò)ORA-14255: 未按范圍, 組合范圍或列表方法對表進(jìn)行分區(qū)，如果要減少一個(gè)HASH分區(qū)表中的分區(qū)需要用ALTER TABLE ... COALESCE PARTITION
4、HASH分區(qū)進(jìn)行ADD PARTITION操作，普通索引，本地索引，全局索引都會(huì)失效，除非使用UPDATE INDEXES，但是LIST、RANGE分區(qū)不受影響
5、LIST,RANGE分區(qū)進(jìn)行DROP PARTITION操作全局索引及普通索引會(huì)失效，但是LOCAL索引不受影響。除非使用UPDATE INDEXES.
6、如果想要為全局索引增加分區(qū)，那這個(gè)操作只能對HASH分區(qū)的全局有效，ORA-14640: 添加/合并索引分區(qū)操作只對散列分區(qū)的全局索引有效，但是DROP全局索引的分區(qū)對HASH\LIST\RANGE均有效。

關(guān)于如何理解ADD和DROP分區(qū)問題的解答就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道了解更多相關(guān)知識(shí)。

本文名稱：如何理解ADD和DROP分區(qū)
文章起源：http://jinyejixie.com/article24/poscje.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供、定制網(wǎng)站、網(wǎng)站維護(hù)、標(biāo)簽優(yōu)化、App開發(fā)、外貿(mào)網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)

網(wǎng)站托管運(yùn)營
阳城县| 崇文区| 寻甸| 绍兴县| 景谷| 宝丰县| 新闻| 静海县| 靖宇县| 渝北区| 冀州市| 开化县| 无棣县| 西藏| 新晃| 九江市| 郑州市| 石景山区| 民权县| 滁州市| 民和| 大庆市| 宽城| 长泰县| 共和县| 双鸭山市| 额尔古纳市| 通河县| 红安县| 中宁县| 杨浦区| 德庆县| 九寨沟县| 涿鹿县| 奉化市| 来宾市| 张家口市| 丹巴县| 双江| 板桥市| 旬阳县|