truncate表的效率有目共睹,那如何truncate分区表呢?
请看一下简单的演示:
1.语法
alter table Partition_Table_Name truncate partition/subpartition Partition_Name update indexes;
2.创建分区表并插入数据
sec@ora10g> create table t_partition_range (id number,name varchar2(50))
2 partition by range(id)(
3 partition t_range_p1 values less than (10) tablespace tbs_part01,
4 partition t_range_p2 values less than (20) tablespace tbs_part02,
5 partition t_range_p3 values less than (30) tablespace tbs_part03,
6 partition t_range_pmax values less than (maxvalue) tablespace tbs_part04);
Table created.
sec@ora10g> col TABLE_NAME for a20
sec@ora10g> col partition_name for a20
sec@ora10g> col HIGH_VALUE for a10
sec@ora10g> col TABLESPACE_NAME for a15
sec@ora10g> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name='T_PARTITION_RANGE' order by partition_position;
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------- ---------- ---------------
T_PARTITION_RANGE T_RANGE_P1 10 TBS_PART01
T_PARTITION_RANGE T_RANGE_P2 20 TBS_PART02
T_PARTITION_RANGE T_RANGE_P3 30 TBS_PART03
T_PARTITION_RANGE T_RANGE_PMAX MAXVALUE TBS_PART04
sec@ora10g> insert into T_PARTITION_RANGE values (2,'secooler02');
1 row created.
sec@ora10g> insert into T_PARTITION_RANGE values (3,'secooler03');
1 row created.
sec@ora10g> insert into T_PARTITION_RANGE values (4,'secooler04');
1 row created.
sec@ora10g> commit;
Commit complete.
sec@ora10g> select *from t_partition_range partition(t_range_p1);
ID NAME
---------- --------------------------------------------------
2 secooler02
3 secooler03
4 secooler04
3.就是这样的truncate分区
sec@ora10g> alter table t_partition_range truncate partition t_range_p1 update indexes;
Table truncated.
sec@ora10g> select * from t_partition_range partition(t_range_p1);
no rows selected
-- The End --
请看一下简单的演示:
1.语法
alter table Partition_Table_Name truncate partition/subpartition Partition_Name update indexes;
2.创建分区表并插入数据
sec@ora10g> create table t_partition_range (id number,name varchar2(50))
2 partition by range(id)(
3 partition t_range_p1 values less than (10) tablespace tbs_part01,
4 partition t_range_p2 values less than (20) tablespace tbs_part02,
5 partition t_range_p3 values less than (30) tablespace tbs_part03,
6 partition t_range_pmax values less than (maxvalue) tablespace tbs_part04);
Table created.
sec@ora10g> col TABLE_NAME for a20
sec@ora10g> col partition_name for a20
sec@ora10g> col HIGH_VALUE for a10
sec@ora10g> col TABLESPACE_NAME for a15
sec@ora10g> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name='T_PARTITION_RANGE' order by partition_position;
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------- ---------- ---------------
T_PARTITION_RANGE T_RANGE_P1 10 TBS_PART01
T_PARTITION_RANGE T_RANGE_P2 20 TBS_PART02
T_PARTITION_RANGE T_RANGE_P3 30 TBS_PART03
T_PARTITION_RANGE T_RANGE_PMAX MAXVALUE TBS_PART04
sec@ora10g> insert into T_PARTITION_RANGE values (2,'secooler02');
1 row created.
sec@ora10g> insert into T_PARTITION_RANGE values (3,'secooler03');
1 row created.
sec@ora10g> insert into T_PARTITION_RANGE values (4,'secooler04');
1 row created.
sec@ora10g> commit;
Commit complete.
sec@ora10g> select *from t_partition_range partition(t_range_p1);
ID NAME
---------- --------------------------------------------------
2 secooler02
3 secooler03
4 secooler04
3.就是这样的truncate分区
sec@ora10g> alter table t_partition_range truncate partition t_range_p1 update indexes;
Table truncated.
sec@ora10g> select * from t_partition_range partition(t_range_p1);
no rows selected
-- The End --
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/519536/viewspace-609290/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/519536/viewspace-609290/