I need to delete 18000 rows from a table but where clause condtion varies for each set of records.
A particular where clause condition can delete 3 records.
Another particular where clause condition can delete 1 record.
I combined both delete statements with above WHERE clause using OR condition and deleted 4 records at a time.
Next set of records I can combine and execute more delete statements with OR condition and this is time consuming.
Please suggest me how to delete the remaining large number of rows from the table. Here the challenge is the where clause condition of delete statement varies for each set of records.
Example : 1st Where condtion of delete statement retrieves 4 records. 2nd WHERE condtion of delete statement retrieves 3 records
and so on. I deleted by combining both delete statement using OR condtion. This is time consuming. Please suggest the better method for this.
Once you combine more than 2-3 OR conditions in a single statement, it's very likely the optimizer will choose a full tablescan:
create table t as
with rws as (
select rownum x from dual connect by level <= 10000
)
select rownum w, mod(rownum, 1000) x, date'2015-01-01'+rownum y, 'aa' || rownum z from rws;
create index w on t(w);
create index x on t(x);
create index y on t(y);
create index z on t(z);
delete /*+ gather_plan_statistics */t
where x = 1
or y = date'2016-01-01'
or z = 'aa23'
or w = 0;
select * from table(dbms_xplan.display_cursor(null, null, 'BASIC +PREDICATE'));
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | DELETE STATEMENT | |
| 1 | DELETE | T |
|* 2 | TABLE ACCESS FULL| T |
-----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("X"=1 OR "Y"=TO_DATE(' 2016-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') OR "Z"='aa23' OR "W"=0))
So if you have several deletes that use different columns and each only removes a few rows, you may be better of using separate statements.
But if one of the OR conditions uses unindexed columns, you'll have a full tablescan anyway. So you may as well stick with the single statement.
18,000 is a reasonably large number of rows. If splitting this up means you'll have 1,000s of separate deletes, stick with one.
If you need further help, please post more details about your situation. Include examples of what you're trying to do an execution plans for the deletes!