Oracle 8.0.5 on HP-UX - optimizer problem

From: Marek Kawczy?ski <marek.kawczynski_at_polkomtel.com.pl>
Date: 7 Jan 2002 04:48:14 -0800
Message-ID: <94843f6a.0201070448.f4c055@posting.google.com>

I have some problems with performance of delete query. My query is like :
delete from table1
where id in (select id from table1

              minus 
             select id from table2)
      or field1 = ...
      or field2 = ...;

In this case oracle making in very long time (about 30 minutes) if i reorder this in:
delete from table1
where field1 = ...

      or field2 = ...
      or id in (select id from table1
              minus 
             select id from table2);

time of doing this is about 5 secs.
Whats going on, is that a bug?
My optimiser is in mode choose. There was ok, about 2 weeks ago. Now is like in that case.
Please help me... Received on Mon Jan 07 2002 - 06:48:14 CST