Oracle deletes data on a large number of conditions

Kingdee BOS system table T_bas_message table is used to store system messages. In practice, the client defaults to 15-minute queries for new messages at a time

The user is basically not going to delete the read message, which makes the table surprisingly large and the system has been running for more than a year, with about 4.5 million data. Plus the index on this table, for the database, once the operation error caused the index in the table to fail, the database will be too many full table scan to die.

After the filter deleted one months ago Data execution SQL statement is as follows

DELETE from T_bas_message t WHERE t.fsendtime > Sysdate-30;

This code was run for two hours in the test library. On the one hand, there is a large amount of data, on the other hand, because there are several columns of indexes in the table. In practice this kind of plan certainly can't.

discussed with the DBA, there are two options.

A through table partitioning operations. partition through the table, and then delete the unrelated partitions directly.

B transfer the useful data to a table and drop the original table. Import data after recreating the original table.

B scenario code is as follows

CREATE table T_bas_message_bak as SELECT * from T_bas_message t WHERE t.fsendtime> sysdate-30;drop TABLE t_bas_message ; CREATE TABLE T_bas_message as SELECT * from T_bas_message_bak;create index ix_message_text on T_bas_message (Freceiver, F ORGID), CREATE index ix_msg_receiver on T_bas_message (freceiver), CREATE index Ix_msg_source on T_bas_message (Fsourceid) ALTER TABLE T_BAS_MESSAGEADD constraint Pk_bas_bmcmessage primary KEY (FID);D ROP table T_bas_message_bak;

PS: Here is the three index, which is the original table, so add. But I actually think that the first composite index completely supersedes the second index in Oracle and should be removed.

Oracle deletes data on a large number of conditions