Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Why does changing != to > slow things down so much?
I have a table with about 900 rows. Columns id and work are both numbers. All of the id's are unique, but there are 21 rows with duplicate work values. I want to find those rows. First, I try:
select t1.id, t1.work, t2.id, t2.work
from annot t1, annot t2 where t1.work = t2.work and t1.id != t2.id
This works fine, except that it gives me back 42 rows, with each row twice:
ID WORK ID WORK ---------- ---------- ---------- ----------
138 63 137 63 137 63 138 63
OK, I understand why this is happening. I figure the simpliest way to fix the problem is to change the "t1.id != t2.id" to "t1.id < t2.id". And, sure enough, that works. When I do that, I get back exactly the 21 rows I expect to.
The problem is, the first query, with the !=, returns immediately. The 2nd query, takes about 45 seconds! Obviously, I've done something which is not very efficient :-) Unfortunately, I don't have any clue what it is. Why does such a trivial change in the query result in such a monster change in the response time?
-- Roy Smith <roy_at_popmail.med.nyu.edu> New York University School of Medicine 550 First Avenue, New York, NY 10016Received on Fri Jul 18 1997 - 00:00:00 CDT