oracle数据库表分区

布满荆棘的人生 2022-06-11 05:09 219阅读 0赞

**1、构建实验表**

SQL> select \*From wl;  
     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO  
\---------- ---------- --------- ---------- -------------- ---------- ---------- ----------  
      7369 SMITH      CLERK                7902 17-12月-80            800                    20  
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30  
      7934 MILLER     CLERK              7782 23-1月 -82           1300                    10  
      8102 FORD                                             17-4月 -83  
      7654 MARTIN                                          26-1月 -84

SQL> create table t2 partition by range(hiredate)  
  2           (partition p1 values less than(to\_date('1981-01-01','yyyy-mm-dd')) tablespace test,  
  3              partition p2 values less than(to\_date('1982-01-01','yyyy-mm-dd')) tablespace test,  
  4              partition other values less than(maxvalue) tablespace test)  
  5            as select \* from wl;  
表已创建。

SQL>  select table\_name,partition\_name,partition\_position from  user\_tab\_partitions where table\_name='T2';  
TABLE\_NAME                     PARTITION\_NAME                 PARTITION\_POSITION  
\------------------------------ ------------------------------ ------------------  
T2                             P1                                              1  
T2                             P2                                              2  
T2                             P3                                              3

**创建索引：**

SQL> alter table t2 add constraints   pk\_t2 primary key(empno);  
表已更改。  
SQL> create index local\_ename on t2(ename) local;  
索引已创建。

**查看索引的状态：**

**S**QL> select INDEX\_NAME,status,PARTITION\_NAME from DBA\_ind\_partitions WHERE PARTITION\_NAME LIKE 'P%' and index\_owner='SCOTT' ;  
INDEX\_NAME                     STATUS   PARTITION\_NAME  
\------------------------------ -------- ------------------------------  
LOCAL\_ENAME                    USABLE   P1  
LOCAL\_ENAME                    USABLE   P2  
LOCAL\_ENAME                    USABLE   P3  
SQL> select    INDEX\_NAME,TABLE\_NAME,STATUS from dba\_indexes where table\_name='T2';  
INDEX\_NAME                     TABLE\_NAME                     STATUS  
\------------------------------ ------------------------------ --------  
PK\_T2                          T2                             VALID  
LOCAL\_ENAME                    T2                             N/A

**从上面查询结果可以看出 本地索引 usable 可用，主键 valid 也是正常状态**

SQL> select \*From t2 partition(p1);  
     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO  
\---------- ---------- --------- ---------- -------------- ---------- ---------- ----------  
      7369 SMITH      CLERK           7902 17-12月-80            800                    20  
SQL> select \*From t2 partition(p2);  
     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO  
\---------- ---------- --------- ---------- -------------- ---------- ---------- ----------  
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300         30  
SQL> select \*From t2 partition(p3);  
     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO  
\---------- ---------- --------- ---------- -------------- ---------- ---------- ----------  
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10  
      8102 FORD                            17-4月 -83  
      7654 MARTIN                          26-1月 -84

###                                                              ** split partition   对分区表索引的影响  ** ###

**2、 拆分分区对分区表索引的影响**

**S**QL> alter table t2 split partition p3 at(to\_date('1983-01-01','yyyy-mm-dd')) into (partition p3,partition other);

表已更改。

SQL> select \*From t2 partition(p3) ;  
     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO  
\---------- ---------- --------- ---------- -------------- ---------- ---------- ----------  
      7934 MILLER     CLERK           7782 23-1月 -82           1300                    10  
SQL> select \*From t2 partition(other);  
     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO  
\---------- ---------- --------- ---------- -------------- ---------- ---------- ----------  
      8102 FORD                            17-4月 -83  
      7654 MARTIN                          26-1月 -84  
  
**我们可以看到p3 和other 分区都有数据，再来查看索引的状态**  
SQL> select INDEX\_NAME,status,PARTITION\_NAME from DBA\_ind\_partitions WHERE PARTITION\_NAME LIKE 'P%' and index\_owner='SCOTT' ;  
INDEX\_NAME                     STATUS   PARTITION\_NAME  
\------------------------------ -------- ------------------------------  
LOCAL\_ENAME                    USABLE   P1  
LOCAL\_ENAME                    USABLE   P2  
LOCAL\_ENAME                    **UNUSABLE **P3  
SQL>  select    INDEX\_NAME,TABLE\_NAME,STATUS from dba\_indexes where table\_name='T2';  
INDEX\_NAME                     TABLE\_NAME                     STATUS  
\------------------------------ ------------------------------ --------  
PK\_T2                          T2                             **UNUSABLE**  
LOCAL\_ENAME                    T2                             N/A

SQL> alter table t2 split partition p3 at(to\_date('1983-01-01','yyyy-mm-dd')) into (partition p3,partition other);  
表已更改。

**从上面我们可以看出拆分成p3，other分区都有数据的情况下，分区的索引变成unusable，主键也是unusable**

**我们把索引rebuild好后，继续对other 分区拆分，拆分成 无数据的p4和有数据的other分区，看看索引的状态**

SQL> alter index pk\_t2 rebuild online;  
索引已更改。

SQL> alter index local\_ename  rebuild partition p3  online ;  
索引已更改。

**在此索引已经恢复好了，进行拆分拆分成 无数据的p4和有数据的other分区，看看索引的状态**

SQL> alter table t2 split partition other at(to\_date('1983-04-01','yyyy-mm-dd')) into (partition p4,partition other);  
表已更改。

SQL> select \*from t2 partition(p4);  
未选定行  
SQL> select \*from t2 partition(other);  
     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO  
\---------- ---------- --------- ---------- -------------- ---------- ---------- ----------  
      8102 FORD                            17-4月 -83  
      7654 MARTIN                          26-1月 -84

SQL> select INDEX\_NAME,status,PARTITION\_NAME from DBA\_ind\_partitions WHERE PARTITION\_NAME LIKE 'P%' and index\_owner='SCOTT' ;  
INDEX\_NAME                     STATUS   PARTITION\_NAME  
\------------------------------ -------- ------------------------------  
LOCAL\_ENAME                    USABLE   P1  
LOCAL\_ENAME                    USABLE   P2  
LOCAL\_ENAME                    USABLE   P3  
LOCAL\_ENAME                    USABLE   P4  
SQL>  select    INDEX\_NAME,TABLE\_NAME,STATUS from dba\_indexes where table\_name='T2';  
INDEX\_NAME                     TABLE\_NAME                     STATUS  
\------------------------------ ------------------------------ --------  
PK\_T2                          T2                             VALID  
LOCAL\_ENAME                    T2                             N/A

**我们可以发现当p4分区没有数据而other分区有数据的时候，本地索引和主键是好的可用状态**

继续拆分，拆分成有数据的p5和无数据的other分区的时候，索引的状态

SQL> alter table t2 split partition other at(to\_date('1984-04-01','yyyy-mm-dd')) into (partition p5,partition other);  
表已更改。  
  
SQL> select \*from t2 partition(other);  
未选定行  
SQL> select \*from t2 partition(p5);  
     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO  
\---------- ---------- --------- ---------- -------------- ---------- ---------- ----------  
      8102 FORD                            17-4月 -83  
      7654 MARTIN                          26-1月 -84  
SQL>  select    INDEX\_NAME,TABLE\_NAME,STATUS from dba\_indexes where table\_name='T2';  
INDEX\_NAME                     TABLE\_NAME                     STATUS  
\------------------------------ ------------------------------ --------  
PK\_T2                          T2                             VALID  
LOCAL\_ENAME                    T2                             N/A  
SQL> select INDEX\_NAME,status,PARTITION\_NAME from DBA\_ind\_partitions WHERE PARTITION\_NAME LIKE 'P%' and index\_owner='SCOTT' ;  
INDEX\_NAME                     STATUS   PARTITION\_NAME  
\------------------------------ -------- ------------------------------  
LOCAL\_ENAME                    USABLE   P1  
LOCAL\_ENAME                    USABLE   P2  
LOCAL\_ENAME                    USABLE   P3  
LOCAL\_ENAME                    USABLE   P4  
LOCAL\_ENAME                    USABLE   P5

**我们可以发现当p5分区有数据而other分区没有数据的时候，本地索引和主键是好的可用状态**

**总结：**

<table style="color:rgb(0,0,0); font-family:Arial; font-size:14px; line-height:26px">  split partition ( split partition a into a and b )  
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td>a,b分区均无数据</td> 
   <td> <p style="">a分区有数据</p> <p style="">b分区无数据</p> </td> 
   <td> <p style="">a分区无数据</p> <p style="">b分区有数据</p> </td> 
   <td>a,b分区均有数 据</td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>global index</td> 
   <td>VALID</td> 
   <td>VALID</td> 
   <td>VALID</td> 
   <td><span style="color:rgb(255,0,0)">UNUSABLE</span></td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td>local index</td> 
   <td>USABLE</td> 
   <td>USABLE</td> 
   <td>USABLE</td> 
   <td><span style="color:rgb(255,0,0)">UNUSABLE</span></td> 
   <td>&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

**                                                        add partition  对索引的影响**

SQL> create table t2 partition by range(hiredate)  
  2          (partition p1 values less than(to\_date('1981-01-01','yyyy-mm-dd')) tablespace test,  
  3             partition p2 values less than(to\_date('1985-01-01','yyyy-mm-dd')) tablespace test  
  4            )  
  5           as select \* from wl;  
表已创建。  
SQL> alter table t2 add constraints   pk\_t2 primary key(empno);  
表已更改。

SQL> create index local\_ename on t2(ename) local;  
索引已创建。

SQL> select INDEX\_NAME,status,PARTITION\_NAME from DBA\_ind\_partitions WHERE PARTITION\_NAME LIKE 'P%' and index\_owner='SCOTT' ;  
INDEX\_NAME                     STATUS   PARTITION\_NAME  
\------------------------------ -------- ------------------------------  
LOCAL\_ENAME                    USABLE   P1  
LOCAL\_ENAME                    USABLE   P2  
SQL> select    INDEX\_NAME,TABLE\_NAME,STATUS from dba\_indexes where table\_name='T2';  
INDEX\_NAME                     TABLE\_NAME                     STATUS  
\------------------------------ ------------------------------ --------  
PK\_T2                          T2                             VALID  
LOCAL\_ENAME                    T2                             N/A

SQL> alter table t2 add partition p3 values less than(to\_date('1986-01-01','yyyy-mm-dd')) tablespace test ;  
表已更改。

**从此处可以看出添加分区对分区表索引没有任何影响**

**                                                       ****drop partition**  **对索引的影响**

刚才新添加的分区，目前p3分区里面没有任何数据，我们删除p3分区

SQL> select  \*from t2 partition(p3);  
未选定行

删除分区  
SQL> alter table t2 drop partition p3;  
表已更改。  
  
SQL> select    INDEX\_NAME,TABLE\_NAME,STATUS from dba\_indexes where table\_name='T2';  
INDEX\_NAME                     TABLE\_NAME                     STATUS  
\------------------------------ ------------------------------ --------  
PK\_T2                          T2                             VALID  
LOCAL\_ENAME                    T2                             N/A  
  
SQL> select INDEX\_NAME,status,PARTITION\_NAME from DBA\_ind\_partitions WHERE PARTITION\_NAME LIKE 'P%' and index\_owner='SCOTT' ;  
INDEX\_NAME                     STATUS   PARTITION\_NAME  
\------------------------------ -------- ------------------------------  
LOCAL\_ENAME                    USABLE   P1  
LOCAL\_ENAME                    USABLE   P2

删除空的分区对分区表索引没有任何影响

SQL>  alter table t2 add partition p3 values less than(to\_date('1986-01-01','yyyy-mm-dd')) tablespace test ;  
表已更改。  
SQL> insert into t2 (empno,ename,hiredate) values(7777,'aaaa',to\_date('1985-07-01','yyyy-mm-dd'));  
已创建 1 行。  
SQL> commit;  
提交完成。  
  
SQL> select  \*from t2 partition(p3);  
     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO  
\---------- ---------- --------- ---------- -------------- ---------- ---------- ----------  
      7777 aaaa                            01-7月 -85  
  
SQL>  alter table t2 drop partition p3;  
表已更改。  
  
SQL> select    INDEX\_NAME,TABLE\_NAME,STATUS from dba\_indexes where table\_name='T2';  
INDEX\_NAME                     TABLE\_NAME                     STATUS  
\------------------------------ ------------------------------ --------  
PK\_T2                          T2                             UNUSABLE  
LOCAL\_ENAME                    T2                             N/A  
  
SQL>  select INDEX\_NAME,status,PARTITION\_NAME from DBA\_ind\_partitions WHERE PARTITION\_NAME LIKE 'P%' and index\_owner='SCOTT' ;  
INDEX\_NAME                     STATUS   PARTITION\_NAME  
\------------------------------ -------- ------------------------------  
LOCAL\_ENAME                    USABLE   P1  
LOCAL\_ENAME                    USABLE   P2

可以发现当删除有数据的分区时候，全局索引不可以用

truncate partition  对索引的影响

SQL>  create table t2 partition by range(hiredate)  
  2           (partition p1 values less than(to\_date('1981-1-1','yyyy-mm-dd')) tablespace test,  
  3              partition p2 values less than(to\_date('1982-1-1','yyyy-mm-dd')) tablespace test,  
  4              partition p3 values less than(maxvalue) tablespace test)  
  5            as select \* from wl;  
表已创建。

SQL> alter table t2 add constraints   pk\_t2 primary key(empno);  
表已更改。  
SQL> create index local\_ename on t2(ename) local;  
索引已创建。  
SQL> select count(\*)from t2 partition(p2);  
  COUNT(\*)  
\----------  
         0  
SQL> select count(\*)from t2 partition(p3);  
  COUNT(\*)  
\----------  
         3  
SQL> select count(\*)from t2 partition(p1);  
  COUNT(\*)  
\----------  
         1

SQL>  select    INDEX\_NAME,TABLE\_NAME,STATUS from dba\_indexes where table\_name='T2';  
INDEX\_NAME                     TABLE\_NAME                     STATUS  
\------------------------------ ------------------------------ --------  
PK\_T2                          T2                             VALID  
LOCAL\_ENAME                    T2                             N/A  
  
SQL> select INDEX\_NAME,status,PARTITION\_NAME from DBA\_ind\_partitions WHERE PARTITION\_NAME LIKE 'P%' and index\_owner='SCOTT' ;  
INDEX\_NAME                     STATUS   PARTITION\_NAME  
\------------------------------ -------- ------------------------------  
LOCAL\_ENAME                    USABLE   P1  
LOCAL\_ENAME                    USABLE   P2  
LOCAL\_ENAME                    USABLE   P3

**我们可以看到p1分区里面无数据，其他分区里面都有数据，我们对p1分区truncate 一下**

SQL> alter table t2 truncate partition(p1);  
表被截断。  
SQL>  select    INDEX\_NAME,TABLE\_NAME,STATUS from dba\_indexes where table\_name='T2';  
INDEX\_NAME                     TABLE\_NAME                     STATUS  
\------------------------------ ------------------------------ --------  
PK\_T2                          T2                             VALID  
LOCAL\_ENAME                    T2                             N/A  
  
SQL> select INDEX\_NAME,status,PARTITION\_NAME from DBA\_ind\_partitions WHERE PARTITION\_NAME LIKE 'P%' and index\_owner='SCOTT' ;  
INDEX\_NAME                     STATUS   PARTITION\_NAME  
\------------------------------ -------- ------------------------------  
LOCAL\_ENAME                    USABLE   P1  
LOCAL\_ENAME                    USABLE   P2  
LOCAL\_ENAME                    USABLE   P3  
  
可以看出truncate一个空的分区，对于分区表的索引没有影响  
  
SQL> alter table t2 truncate partition(p2);  
表被截断。  
  
SQL>  select    INDEX\_NAME,TABLE\_NAME,STATUS from dba\_indexes where table\_name='T2';  
INDEX\_NAME                     TABLE\_NAME                     STATUS  
\------------------------------ ------------------------------ --------  
PK\_T2                          T2                             UNUSABLE  
LOCAL\_ENAME                    T2                             N/A  
SQL> select INDEX\_NAME,status,PARTITION\_NAME from DBA\_ind\_partitions WHERE PARTITION\_NAME LIKE 'P%' and index\_owner='SCOTT' ;  
INDEX\_NAME                     STATUS   PARTITION\_NAME  
\------------------------------ -------- ------------------------------  
LOCAL\_ENAME                    USABLE   P1  
LOCAL\_ENAME                    USABLE   P2  
LOCAL\_ENAME                    USABLE   P3

可以看出truncate一个非空的分区，对于分区表的全局索引有影响

<table style="color:rgb(0,0,0); font-family:Arial; font-size:14px; line-height:26px"> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td><span style="font-weight:bold">&nbsp;add partition&nbsp;</span></td> 
   <td><span style="font-weight:bold">drop partition</span></td> 
   <td><span style="font-weight:bold">drop partition</span></td> 
   <td><span style="font-weight:bold">truncate partition</span></td> 
   <td><span style="font-weight:bold">truncate &nbsp;partition</span></td> 
  </tr> 
  <tr> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td><span style="font-weight:bold">分区表中无数据</span></td> 
   <td><span style="font-weight:bold">分区表中有数据</span></td> 
   <td><span style="font-weight:bold">分区表中无数据</span></td> 
   <td><span style="font-weight:bold">分区表中有数据</span></td> 
  </tr> 
  <tr> 
   <td><span style="font-weight:bold">global index</span></td> 
   <td>VALID</td> 
   <td>VALID</td> 
   <td><span style="color:rgb(255,0,0)"><strong>UNUSABLE</strong></span></td> 
   <td>VALID</td> 
   <td><span style="color:rgb(255,0,0)"><strong>UNUSABLE</strong></span></td> 
  </tr> 
  <tr> 
   <td><span style="font-weight:bold">local index</span></td> 
   <td>USABLE</td> 
   <td>USABLE</td> 
   <td>USABLE</td> 
   <td>USABLE</td> 
   <td>USABLE</td> 
  </tr> 
 </tbody> 
</table>