Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Why does changing != to > slow things down so much?

Why does changing != to > slow things down so much?

From: Roy Smith <roy_at_popmail.med.nyu.edu>
Date: 1997/07/18
Message-ID: <roy-1807971833070001@mchip8.med.nyu.edu>#1/1

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  10016
Received on Fri Jul 18 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US