Exists clause caused performance problem???
646075Oct 29 2010 — edited Nov 1 2010Hi,
We recently upgraded from 10g to 11g r1 (on redhat 64 bit linux platform).
We ran some performance tests. We were surprise to find out the following query performed faster on 10g than on 11g.
We ran through the process of elimination. We were quite sure the query ran slower on 11g but could not explain why.
delete from TEMP_DATA_ID t1 where t1.FLAG=0
and exists (select 0 from TEMP_DATA_ID t2 where t1.DATASET_ID=t2.DATASET_ID and t1.ID=t2.ID)
The exists clause always evaluated to true.
After we removed the exists clause, the remaining delete statement ran faster on 11g.
Both databases had the same Oracle configurations, hardware, same schema, and similar physical layout. Can anyone think of a reason why the unnecessary exists clause
would cause Oracle to run slower?
Thanks,
Andrew K