Converting Delete qry

General
Enthusiast

Converting Delete qry

Hi,

I have a delete statement which is consuming more time. 

DELETE FROM TABLE A

WHERE A.COL1   =  TABLE B.COL1

AND     A.COL2   =  TABLE B.COL2

AND     A.COL3   =  TABLE B.COL3

i have converted this into like the below using exists

DELETE FROM TABLE A

WHERE EXISTS ( SEL 1  FROM TABLE B

                       WHERE B.COL1  = A.COL1

                       AND     B.COL2  = A.COL2

                       AND     B.COL3  = A.COL3)

but i am getting record count mismatch. but this qry is faster than the original qry.

can anybody pls help me solving this.

Thanks,

Harshita.

3 REPLIES
Enthusiast

Re: Converting Delete qry

Not sure why would you get the count mismatch. The joining conditions for the two tables are same and EXISTS kind of serve as the inner join so its not making sense to have count mismatch.

Did you try the SELECT statement to check the row counts in both of the cases? Do they differ as well?

Enthusiast

Re: Converting Delete qry

Hi,

Ya i was mistaken . i got the same count. but original qry have better performance than EXISTS. 

Can u pls suggest how can we tune this.Table A is a large table having huge volume of data. and Table B is small Table.

All the columns mentioned in the where condition are Indexed.

Thanks,

Harshita.

Senior Supporter

Re: Converting Delete qry

you need to share the explain and ddls of your tables