I am going to delete millions of rows in each batch which I cannot delete them together since there are too many rows. I was thinking of deleting rows with no log but I did bot find anything. Is there anybody could help me to write a delete no log query?
My query would be like:
DELETE
FROM MyTable
WHERE TO_CHAR(MyDate, 'YYYYMM') = 202101
even I jus consider one month(It is about 6 million records) I cannot delete them
Delete operations are always logging, and also require a lot of undo, that is why they could be expensive in terms of performance when a huge number of rows are involved. Also, if you have indexes, the rows must be deleted there too.
There are several options, but they strongly depend of your environment. I would try two approaches
I am using parallel with degree 8. If you have less or more CPU in your database server, you could play with the degree. I would not use too much degree in the delete, but I would use as much as possible in the option 2
Option 1 ( use parallel dml )
alter session enable parallel dml ;
delete /*+parallel(a,8) */ from myTable a where TO_CHAR(MyDate, 'YYYYMM') = 202101 ;
commit;
Option 2 ( use CTAS and Truncate/Insert )
alter session enable parallel dml;
alter session enable parallel ddl;
create table MyBkpTable parallel compress nologging pctfree 0
as select /*+parallel(a,8) */ * from MyTable a
where TO_CHAR(MyDate, 'YYYYMM') != 202101 ;
truncate table MyTable reuse storage ;
insert /*+append parallel(a,8) */ into MyTable a
select /*+parallel(b,8) */ * from MyBkpTable b;
commit;
If you have a lot of indexes on the table, mark all indexes as unusable and use alter session set skip_unusable_indexes=true before running the insert append. Then at the end, rebuild all indexes.
Update
As the table is partitioned, to benefit from partition pruning you might use ( as long as the partition key is MyDate )
delete /*+parallel(a,8) */ from myTable a where MyDate >= date '2021-01-01' and MyDate < date '2021-02-01' ;
or even better, just truncate all those partitions
alter table myTable truncate partition <<partition_name>>
where MyDate >= date '2021-01-01' and MyDate < date '2021-02-01' to leverage partitioning. For the second approach partitioning may also be used: exchange partition with rebuild of indexes instead of insert ... select...
to_char in any case.