验证分区表中添加或删除空分区对全局索引没影响
来源:互联网 发布:瓷砖铺贴软件 编辑:程序博客网 时间:2024/06/07 21:02
验证分区表中添加或删除空分区对全局索引没影响
建立分区表及主键和一个全局索引:
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)
以上实验充分验证了分区表中添加或删除空分区对全局索引没影响。
来源:http://pandarabbit.blog.163.com/blog/static/209284144201210269519867/
- 验证分区表中添加或删除空分区对全局索引没影响
- 分区表中主键索引是该是全局还是分区索引?
- 分区表、分区索引和全局索引
- Atitit.分区对索引的影响 分区索引和全局索引 attilax总结
- oracle定时添加或删除分区表的分区
- 定时添加或删除分区表分区的方法
- oracle定时添加或删除分区表的分区
- 分区表、分区索引和全局索引部分总结
- 分区表、分区索引和全局索引部分总结
- 分区表、分区索引和全局索引部分总结
- 分区表、分区索引和全局索引部分总结
- 分区表、分区索引和全局索引部分总结
- oracle的分区表、分区索引和全局索引部分总结
- 分区表、分区索引和全局索引部分总结
- 分区表、分区索引和全局索引部分总结
- 分区表、分区索引和全局索引部分总结
- oracle的分区表、分区索引和全局索引部分总结
- 分区表、分区索引和全局索引部分总结
- 记录ASPX页面的加载及卸载顺序
- DUI入门
- android实现按两次返回键退出程序
- Oracle闪回作用_hanCSDN_20130603
- 禁止手机浏览器放大缩小页面
- 验证分区表中添加或删除空分区对全局索引没影响
- 接口介绍
- Android官网新例详解-------Adding Animations讲解
- Google CodeJam Round 2 2013
- Agile Web Development with Rails第十章笔记——任务E:更智能的购物车
- OpenCV的CvMat与cvSolve函数
- oracle中一些策略规划设置
- HDU 2602 最朴实的背包问题
- hdu 1181 变形课