Try using an anti-join instead of the minus

-----Original Message-----
From: Benny Pei [mailto:[Email Address Removed] Wednesday, July 31, 2002 5:33 PM
To: LazyDBA.com Discussion
Subject: a simple query tuning question


Hi,

I have three tables and I have a big batch of delete
job going to run:

create table temp (col1 number, co12 number, col3
number)
unique index on temp1(col1,col2)

create table temp2 (col1 number, co12 number, col3
number)
index on temp2(col1)

create table temp3 (col1 number, co12 number, col3
number)
index on temp3(col1)

query:
(A)
delete from temp where (col1, col2) in
(select col1, 1 from temp2 minus
select col1, 1 from temp3)

vs
(B)
delete from temp where col1 in (
select col1 from temp1 minus
select col1 from temp2) and col2 = 1


Which query will be more efficient? Is (B) more
efficient than (A)?

benny

__________________________________________________
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com

--------
Oracle documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to oracledba-[Email Address Removed] subscribe: send a blank email to oracledba-[Email Address Removed] the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page