注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle、MySQL资料及经验

.

 
 
 

日志

 
 

验证分区表中添加或删除空分区对全局索引没影响  

2012-11-26 09:05:19|  分类: 分区表 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

验证分区表中添加或删除空分区对全局索引没影响

建立分区表及主键和一个全局索引:
SQL> create table t (id number,sale_date date)
  2  partition by range(sale_date)(
  3  partition p_2012_10 values less than(to_date('2012-11-01','yyyy-mm-dd')));
 
Table created
 
SQL> alter table t add constraint pk_t primary key (id);
 
Table altered
 
SQL> create index idx_t on t (sale_date);
 
Index created

插入数据:
SQL> insert into t values (1,sysdate-30);
 
1 row inserted
 
SQL> commit;
 
Commit complete

检查分区表及索引状态情况:
SQL> select segment_name,partition_name,blocks,bytes from user_segments where segment_name='T';
 
SEGMENT_NAME               PARTITION_NAME                     BLOCKS      BYTES
-------------------------- ------------------------------ ---------- ----------
T                          P_2012_10                               8      65536
SQL> select * from t;
 
        ID SALE_DATE
---------- -----------
         1 2012-10-25
SQL> select index_name,status from user_indexes where index_name in ('PK_T','IDX_T');
 
INDEX_NAME                     STATUS
------------------------------ --------
IDX_T                          VALID
PK_T                           VALID
SQL> select object_name,status from user_objects where object_name in ('PK_T','IDX_T');
 
OBJECT_NAME                                                                      STATUS
-------------------------------------------------------------------------------- -------
IDX_T                                                                            VALID
PK_T                                                                             VALID

添加一个空分区,然后查看索引状态:
SQL> alter table t add partition p_2012_11 values less than(to_date('2012-12-01','yyyy-mm-dd'));
 
Table altered
SQL> select segment_name,partition_name,blocks,bytes from user_segments where segment_name='T';
 
SEGMENT_NAME               PARTITION_NAME                     BLOCKS      BYTES
-------------------------- ------------------------------ ---------- ----------
T                          P_2012_10                               8      65536
T                          P_2012_11                               8      65536
SQL> select index_name,status from user_indexes where index_name in ('PK_T','IDX_T');
 
INDEX_NAME                     STATUS
------------------------------ --------
IDX_T                          VALID
PK_T                           VALID
SQL> select object_name,status from user_objects where object_name in ('PK_T','IDX_T');
 
OBJECT_NAME                                                                      STATUS
-------------------------------------------------------------------------------- -------
IDX_T                                                                            VALID
PK_T                                                                             VALID

删除一个空分区,然后查看索引状态:
SQL> alter table t drop partition p_2012_11;
 
Table altered
SQL> select segment_name,partition_name,blocks,bytes from user_segments where segment_name='T';
 
SEGMENT_NAME                PARTITION_NAME                     BLOCKS      BYTES
--------------------------- ------------------------------ ---------- ----------
T                           P_2012_10                               8      65536
SQL> select index_name,status from user_indexes where index_name in ('PK_T','IDX_T');
 
INDEX_NAME                     STATUS
------------------------------ --------
IDX_T                          VALID
PK_T                           VALID
SQL> select object_name,status from user_objects where object_name in ('PK_T','IDX_T');
 
OBJECT_NAME                                                                      STATUS
-------------------------------------------------------------------------------- -------
IDX_T                                                                            VALID
PK_T                                                                             VALID

模拟一个有数据的分区,删除分区内数据后,再删除这个分区,最后查看索引状态:
SQL> alter table t add partition p_2012_11 values less than(to_date('2012-12-01','yyyy-mm-dd'));
 
Table altered
SQL> insert into t values (2,sysdate);
 
1 row inserted
SQL> commit;
 
Commit complete

SQL> delete t where id=2;
 
1 row deleted
SQL> commit;
 
Commit complete

SQL> alter table t drop partition p_2012_11;
 
Table altered
SQL> select segment_name,partition_name,blocks,bytes from user_segments where segment_name='T';
 
SEGMENT_NAME                   PARTITION_NAME                     BLOCKS      BYTES
------------------------------ ------------------------------ ---------- ----------
T                              P_2012_10                             8      65536
SQL> select index_name,status from user_indexes where index_name in ('PK_T','IDX_T');
 
INDEX_NAME                     STATUS
------------------------------ --------
IDX_T                          VALID
PK_T                           VALID
SQL> select object_name,status from user_objects where object_name in ('PK_T','IDX_T');
 
OBJECT_NAME                                                                      STATUS
-------------------------------------------------------------------------------- -------
IDX_T                                                                            VALID
PK_T                                                                             VALID

查看sql的执行计划,看全局索引是否仍能用:
SQL> set autot on exp
SQL> select id from t where id=1;

        ID
----------
         1


Execution Plan
----------------------------------------------------------
Plan hash value: 1517170033

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     0   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| PK_T |     1 |    13 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("ID"=1)

以上实验充分验证了分区表中添加或删除空分区对全局索引没影响。

  评论这张
 
阅读(499)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018