Skip to Main Content
  • Questions
  • I need to delete 18000 rows from a table but where clause condition varies. How to complete this deletion in simple way

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Chris Saxon

Thanks for the question.

Asked: June 26, 2016 - 8:01 am UTC

Answered by: Chris Saxon - Last updated: July 06, 2016 - 8:14 am UTC

Category: Database - Version: 11.2.0.2.0

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Execute any SQL statement from within a Application Express app? Sure, why not? Um.....

You Asked

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.

and we said...

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!

and you rated our response

  (1 rating)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Merge delete

July 06, 2016 - 6:31 pm UTC

Reviewer: Ghassan

Merge into t tgt
Using
(
YOUR SELECTS .. rowid as rid ....UNION ALL WITH YOUR DIFFERENT PREDICATES. .
) src
On tgt.rowid = src.rid
When matched
Update whatever same col.
Delete ..


More to Explore

DBMS_XPLAN

More on PL/SQL routine DBMS_XPLAN here