partition table中truncate应用 (转)

本文描述了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 8
IDX_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 .25
IDX_T_P .0625

Elapsed: 00:00:00.01
ROOTDBA@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 8
IDX_T_P 9

Elapsed: 00:00:00.01
ROOTDBA@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.0625
IDX_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.0625
IDX_T_P 5

Elapsed: 00:00:00.02
ROOTDBA@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.29
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.0625
IDX_T_P 5

Elapsed: 00:00:00.01
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

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.31
ROOTDBA@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>