分区表是生产中常用的一种表,它可以实现数据的按类存放,极大的提高了数据的查询及维护。当我们不需要某一分区的数据时,可以采用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