【Oracle】truncate分区表

分区表是生产中常用的一种表，它可以实现数据的按类存放，极大的提高了数据的查询及维护。当我们不需要某一分区的数据时，可以采用truncate来清空分区。实验如下：

SQL> create table t_partition_range(id number,name varchar2(50))
  2  partition by range(id)(
  3  partition t_range_p1 values less than (10), 
  4  partition t_range_p2 values less than (20),
  5  partition t_range_p3 values less than (30),
  6  partition t_range_pmax values less than (maxvalue)
  7  );


Table created.

SQL> col PARTITION_NAME for a20;
SQL> col HIGH_VALUE for a10;
SQL> select PARTITION_NAME,HIGH_VALUE from user_tab_partitions where table_name='T_PARTITION_RANGE';


PARTITION_NAME     HIGH_VALUE
-------------------- ----------
T_RANGE_P1     10
T_RANGE_P2     20
T_RANGE_P3     30
T_RANGE_PMAX     MAXVALUE

SQL> insert into t_partition_range values (1,'zhangsan');


1 row created.


SQL> insert into t_partition_range values (2,'lisi');


1 row created.


SQL> insert into t_partition_range values (3,'wangwu');


1 row created.


SQL> commit;


Commit complete.


SQL> select * from t_partition_range partition (t_range_p1);


ID NAME
---------- --------------------------------------------------
1 zhangsan
2 lisi
3 wangwu


SQL> alter table t_partition_range truncate partition t_range_p1 update indexes;


Table truncated.


SQL> select * from t_partition_range partition (t_range_p1);


no rows selected