Oracle之move操作导致索引失效
来源:互联网 发布:秒赞秒评大师软件 编辑:程序博客网 时间:2024/05/22 03:44
/*
结论:很多人想用ALTER TABLE MOVE的方式来降低高水平,结果经常忽略了这个操作会导致索引失效,
请大家通过下列的试验的回顾,以后多留意这点。
另外alter table t shrink space; 是否能十全十美呢,请看后面的案例分析
*/
drop table t purge;
create table t as select * from dba_objects where object_id is not null;
alter table t modify object_id not null;
set autotrace off
insert into t select * from t;
insert into t select * from t;
commit;
create index idx_object_id on t(object_id);
select index_name,status from user_indexes where index_name='IDX_OBJECT_ID';
INDEX_NAME STATUS
------------------------------ ------
IDX_OBJECT_ID VALID
set linesize 1000
set autotrace on
select count(*) from t;
COUNT(*)
----------
292740
执行计划
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 185 (2)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 398K| 185 (2)| 00:00:03 |
-------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
659 consistent gets
0 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
set autotrace off
delete from t where rownum<=292000;
commit;
set autotrace on
select count(*) from t;
COUNT(*)
----------
740
执行计划
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 185 (2)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 1 | 185 (2)| 00:00:03 |
-------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
659 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
alter table t move;
select count(*) from t;
执行计划
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 740 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
set autotrace off
select index_name,status from user_indexes where index_name='IDX_OBJECT_ID';
INDEX_NAME STATUS
------------------------------ --------
IDX_OBJECT_ID UNUSABLE
alter index idx_object_id rebuild;
set autotrace on
select count(*) from t;
执行计划
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 740 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
----------------------------------------------------------------------------------------------------------------------------------------------
结论:很多人想用ALTER TABLE MOVE的方式来降低高水平,结果经常忽略了这个操作会导致索引失效,
请大家通过下列的试验的回顾,以后多留意这点。
另外alter table t shrink space; 是否能十全十美呢,请看后面的案例分析
*/
drop table t purge;
create table t as select * from dba_objects where object_id is not null;
alter table t modify object_id not null;
set autotrace off
insert into t select * from t;
insert into t select * from t;
commit;
create index idx_object_id on t(object_id);
select index_name,status from user_indexes where index_name='IDX_OBJECT_ID';
INDEX_NAME STATUS
------------------------------ ------
IDX_OBJECT_ID VALID
set linesize 1000
set autotrace on
select count(*) from t;
COUNT(*)
----------
292740
执行计划
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 185 (2)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 398K| 185 (2)| 00:00:03 |
-------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
659 consistent gets
0 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
set autotrace off
delete from t where rownum<=292000;
commit;
set autotrace on
select count(*) from t;
COUNT(*)
----------
740
执行计划
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 185 (2)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 1 | 185 (2)| 00:00:03 |
-------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
659 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
alter table t move;
select count(*) from t;
执行计划
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 740 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
set autotrace off
select index_name,status from user_indexes where index_name='IDX_OBJECT_ID';
INDEX_NAME STATUS
------------------------------ --------
IDX_OBJECT_ID UNUSABLE
alter index idx_object_id rebuild;
set autotrace on
select count(*) from t;
执行计划
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 740 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
424 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
----------------------------------------------------------------------------------------------------------------------------------------------
阅读全文
0 0
- Oracle之move操作导致索引失效
- oracle ALTER TABLE MOVE 索引失效
- move 表索引失效
- 导致索引失效的情况
- Oracle的Move操作
- ORACLE索引失效解决方案
- ORACLE数据库索引失效
- ORACLE数据库索引失效
- oracle索引失效解决
- Oracle数据库索引失效
- oracle索引失效原因
- oracle索引失效
- oracle sql调优的经历(隐士转换导致索引失效)
- Where语句设置不当导致索引失效
- 隐式转换导致索引失效
- 导致索引失效的一些情况
- Where语句设置不当导致索引失效
- 截断分区数据导致全局索引失效
- 谈谈你对模块化开发的理解?
- Codevs2488绿豆蛙的归宿
- HTML 练习题
- 我创建了一个软件测试交流群
- 质因数分解
- Oracle之move操作导致索引失效
- 前段异常集锦
- leetcode题目例题解析(六)
- Linux下安装jdk8步骤详述
- 南宁市第二届网络与信息安全技术竞赛——web200
- android微信登录的app签名 和 项目正式签名替换默认签名
- UVA 10815
- 递归栈(hanoi问题)
- 算法练习---跳跃游戏二(动态规划)