Oracle Delete Table partition

Delete Table partition (drop partition)
Deleting a table partition consists of two operations, namely:
Ø Delete partition: ALTER TABLE [tbname] Drop partition [ptname];

Ø Delete sub-partition: ALTER TABLE [tbname] Drop subpartition [ptname];
In addition to the hash partition and hash sub-partition, other partition formats can support this operation. For example, delete a partition:

Jssweb> Select Table_name,partition_name
2 from User_tab_partitions where table_name= ' t_partition_list ';
TABLE_NAME Partition_name

------------------------------ ------------------------------

T_partition_list T_LIST_P1

T_partition_list T_LIST_P2

T_partition_list T_LIST_P3

T_partition_list T_LIST_PD
jssweb> ALTER TABLE t_partition_list drop partition t_list_p2;

The table has changed.
Tip, when you drop partition, the data stored in the partition is also deleted, for example:

ssweb> INSERT INTO t_partition_list values (1, ' a '); ..........
--Insert a batch of records, distributed across the current partitions ....
Jssweb> commit;
Submit complete.

jssweb> INSERT INTO t_partition_list values (1, ' a '); ..........
--Insert a batch of records, distributed across the current partitions ....
Jssweb> commit;
Submit complete.
jssweb> Select *from t_partition_list;
ID NAME
---------- --------------------------------------------------

1 a 2 b a B
--Query T_LIST_P3 partition separately, currently have data
jssweb> Select *from t_partition_list partition (T_LIST_P3);

ID NAME
---------- --------------------------------------------------

A

b

-- Delete T_LIST_P3 partition, data will be deleted simultaneously
jssweb> ALTER TABLE t_partition_list drop partition t_list_p3;
The table has changed.
jssweb> Select *from t_partition_list partition (T_LIST_P3); Select *from t_partition_list partition (T_LIST_P3)

* Error on line 1th:
ORA-02149: The specified partition does not exist

--delete T_LIST_P3 partition, data will be deleted simultaneously
jssweb> ALTER TABLE t_partition_list drop partition t_list_p3;
The table has changed.
jssweb> Select *from t_partition_list partition (T_LIST_P3); Select *from t_partition_list partition (T_LIST_P3)

* Error on line 1th:
ORA-02149: The specified partition does not exist
jssweb> Select *from t_partition_list;
ID NAME
---------- --------------------------------------------------

1 A

2 b

Because it is a DDL operation, this deletion can also be very rapid, so if you confirm that the data of a partition is to be deleted, using drop partition is more efficient than delete. If you want to delete the specified partition but keep the data, you should use the merge partition, which is also discussed later.
Similarly, if you do not specify an update indexes clause when executing the statement, it will also invalidate the glocal index , and as for the local index, the corresponding index partition will be deleted when the partition is deleted. However, the local index of other partitions is not affected.

Appendix:

Drop partition is to clear the partition and its data and indexes
Truncated is to clear the data and index first, but the partition is still
Performance I think there should be no difference ~ see if you want to keep the partition

Oracle Delete Table partition