本文描述了truncate在partition table中应用情况.
环境oracle版本:11g Enterprise Edition Release 11.2.0.1.0
表:create table “rootdba”.”t_p”( “id” number,“name” char(10))partition by range (”id”)(partition “p10″ values less than (10)partition “p20″ values less than (20)partition “p30″ values less than (30)partition “p40″ values less than (40))
索引:create index idx_t_p on t_p(id);
1.在分区表上truncate整个表数据.
ROOTDBA@ora11g>select segment_name,sum(bytes)/1024/1024 from user_segments where segment_name in (’IDX_T_P’,'T_P’) group by segment_name;
SEGMENT_NAME SUM(BYTES)/1024/1024—————————— ——————–T_P 8IDX_T_P 7
ROOTDBA@ora11g>select index_name,status from user_indexes where index_name=’IDX_T_P’;
INDEX_NAME STATUS—————————— ——–IDX_T_P VALID
ROOTDBA@ora11g>truncate table t_p ;
Table truncated.
ROOTDBA@ora11g>select segment_name,sum(bytes)/1024/1024 from user_segments where segment_name in (’IDX_T_P’,'T_P’) group by segment_name;
SEGMENT_NAME SUM(BYTES)/1024/1024—————————— ——————–T_P .25IDX_T_P .0625
Elapsed: 00:00:00.01ROOTDBA@ora11g>ROOTDBA@ora11g>ROOTDBA@ora11g>select index_name,status from user_indexes where index_name=’IDX_T_P’;
INDEX_NAME STATUS—————————— ——–IDX_T_P VALID
2.在分区表上truncate某个分区中数据.
ROOTDBA@ora11g>select segment_name,sum(bytes)/1024/1024 from user_segments where segment_name in (’IDX_T_P’,'T_P’) group by segment_name;
SEGMENT_NAME SUM(BYTES)/1024/1024—————————— ——————–T_P 8IDX_T_P 9
Elapsed: 00:00:00.01ROOTDBA@ora11g>select index_name,status from user_indexes where index_name=’IDX_T_P’;
INDEX_NAME STATUS—————————— ——–IDX_T_P VALID
ROOTDBA@ora11g>Alter table t_p truncate partition p10;
Table truncated.
ROOTDBA@ora11g>select segment_name,sum(bytes)/1024/1024 from user_segments where segment_name in (’IDX_T_P’,'T_P’) group by segment_name;
SEGMENT_NAME SUM(BYTES)/1024/1024—————————— ——————–T_P 6.0625IDX_T_P 9
ROOTDBA@ora11g>select index_name,status ,PARTITIONED from user_indexes where index_name=’IDX_T_P’;
INDEX_NAME STATUS PAR—————————— ——– —IDX_T_P UNUSABLE NO
3.truncate时 reuse storage的作用
reuse storage作用是可以保留已分配的空间(普通表和分区表都一样),但索引会失效.
ROOTDBA@ora11g>select segment_name,sum(bytes)/1024/1024 from user_segments where segment_name in (’IDX_T_P’,'T_P’) group by segment_name;
SEGMENT_NAME SUM(BYTES)/1024/1024—————————— ——————–T_P 6.0625IDX_T_P 5
Elapsed: 00:00:00.02ROOTDBA@ora11g>select index_name,status ,PARTITIONED from user_indexes where index_name=’IDX_T_P’;
INDEX_NAME STATUS PAR—————————— ——– —IDX_T_P VALID NO
ROOTDBA@ora11g> alter table t_p truncate partition p20 reuse storage ;
Table truncated.
Elapsed: 00:00:00.29ROOTDBA@ora11g>select segment_name,sum(bytes)/1024/1024 from user_segments where segment_name in (’IDX_T_P’,'T_P’) group by segment_name;
SEGMENT_NAME SUM(BYTES)/1024/1024—————————— ——————–T_P 6.0625IDX_T_P 5
Elapsed: 00:00:00.01ROOTDBA@ora11g>select index_name,status ,PARTITIONED from user_indexes where index_name=’IDX_T_P’;
INDEX_NAME STATUS PAR—————————— ——– —IDX_T_P UNUSABLE NO
4. truncate分区后失效索引处理
ROOTDBA@ora11g>select id from t_p where id=1;Elapsed: 00:00:00.00
Execution Plan———————————————————-Plan hash value: 4247270103
—————————————————————————-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |—————————————————————————-| 0 | SELECT STATEMENT | | 500K| 1467K| 3943 (1)| 00:00:48 ||* 1 | INDEX RANGE SCAN| IDX_T_P | 500K| 1467K| 3943 (1)| 00:00:48 |—————————————————————————-
Predicate Information (identified by operation id):—————————————————
1 – access(”ID”=1)
ROOTDBA@ora11g>ROOTDBA@ora11g>alter table t_p truncate partition p10;
Table truncated.
Elapsed: 00:00:00.31ROOTDBA@ora11g>select id from t_p where id=1;Elapsed: 00:00:00.01
Execution Plan———————————————————-Plan hash value: 551878766
———————————————————————————————–| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |———————————————————————————————–| 0 | SELECT STATEMENT | | 500K| 1467K| 6104 (3)| 00:01:14 | | || 1 | PARTITION RANGE SINGLE| | 500K| 1467K| 6104 (3)| 00:01:14 | 1 | 1 ||* 2 | TABLE ACCESS FULL | T_P | 500K| 1467K| 6104 (3)| 00:01:14 | 1 | 1 |———————————————————————————————–
Predicate Information (identified by operation id):—————————————————
2 – filter(”ID”=1)
ROOTDBA@ora11g>alter index idx_t_p rebuild;
Index altered.Elapsed: 00:01:25.59
ROOTDBA@ora11g>ROOTDBA@ora11g>select id from t_p where id=1;Elapsed: 00:00:00.00
Execution Plan———————————————————-Plan hash value: 4247270103
—————————————————————————-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |—————————————————————————-| 0 | SELECT STATEMENT | | 500K| 1467K| 1271 (1)| 00:00:16 ||* 1 | INDEX RANGE SCAN| IDX_T_P | 500K| 1467K| 1271 (1)| 00:00:16 |—————————————————————————-
Predicate Information (identified by operation id):—————————————————
1 – access(”ID”=1)
ROOTDBA@ora11g>
转载于:https://www.cnblogs.com/fengyuwuzu1980/archive/2010/06/23/1763321.html
相关资源：数据结构—成绩单生成器