Oracle-L: RE: Life without a correlated subquery

From: Bellow, Bambi <bbellow_at_chi.navtech.com>
Date: Mon, 10 Nov 2003 10:24:27 -0800
Message-ID: <F001.005D63B1.20031110102427@fatcity.com>

Thanks Folks!

The inline query, indeed, beat the correlated subquery.

Bambi.

-----Original Message-----
Sent: Monday, November 10, 2003 11:34 AM To: Multiple recipients of list ORACLE-L

It is a little convoluted, but you can use an inline query. It is not a correlated subquery, it may be more efficient, your mileage may vary, contents under pressure...

Here is an example of the select using the old, reliable emp table that I populated with duplicates.

SQL> l

  1  select e1.rowid, 
  2         e1.empno, 
  3         e1.ename

4* from emp e1
SQL> /

ROWID                   EMPNO ENAME 
------------------ ---------- ---------- 
AAABb5AAEAAAUIiAAA       7369 SMITH 
AAABb5AAEAAAUIiAAB       7499 ALLEN 
AAABb5AAEAAAUIiAAC       7521 WARD 
AAABb5AAEAAAUIiAAD       7566 JONES 
AAABb5AAEAAAUIiAAE       7654 MARTIN 
AAABb5AAEAAAUIiAAF       7698 BLAKE 
AAABb5AAEAAAUIiAAG       7782 CLARK 
AAABb5AAEAAAUIiAAH       7788 SCOTT 
AAABb5AAEAAAUIiAAI       7839 KING 
AAABb5AAEAAAUIiAAJ       7844 TURNER 
AAABb5AAEAAAUIiAAK       7876 ADAMS 
AAABb5AAEAAAUIiAAL       7900 JAMES 
AAABb5AAEAAAUIiAAM       7902 FORD 
AAABb5AAEAAAUIiAAN       7934 MILLER 
AAABb5AAEAAAUIiAAO       7369 SMITH 
AAABb5AAEAAAUIiAAP       7499 ALLEN 
AAABb5AAEAAAUIiAAQ       7521 WARD 
AAABb5AAEAAAUIiAAR       7566 JONES 
AAABb5AAEAAAUIiAAS       7654 MARTIN 
AAABb5AAEAAAUIiAAT       7698 BLAKE 
AAABb5AAEAAAUIiAAU       7782 CLARK 
AAABb5AAEAAAUIiAAV       7788 SCOTT 
AAABb5AAEAAAUIiAAW       7839 KING 
AAABb5AAEAAAUIiAAX       7844 TURNER 
AAABb5AAEAAAUIiAAY       7876 ADAMS 
AAABb5AAEAAAUIiAAZ       7900 JAMES 
AAABb5AAEAAAUIiAAa       7902 FORD 
AAABb5AAEAAAUIiAAb       7934 MILLER 
  


 1  select e1.rowid, 
  2         e1.empno, 
  3         e1.ename 
  4  from emp e1, 
  5       (select empno, min(rowid) min_rowid 
  6        from emp 
  7        group by empno) e2

8 where e1.empno = e2.empno
9* and e1.rowid != e2.min_rowid
SQL> /

ROWID                   EMPNO ENAME 
------------------ ---------- ---------- 
AAABb5AAEAAAUIiAAO       7369 SMITH 
AAABb5AAEAAAUIiAAP       7499 ALLEN 
AAABb5AAEAAAUIiAAQ       7521 WARD 
AAABb5AAEAAAUIiAAR       7566 JONES 
AAABb5AAEAAAUIiAAS       7654 MARTIN 
AAABb5AAEAAAUIiAAT       7698 BLAKE 
AAABb5AAEAAAUIiAAU       7782 CLARK 
AAABb5AAEAAAUIiAAV       7788 SCOTT 
AAABb5AAEAAAUIiAAW       7839 KING 
AAABb5AAEAAAUIiAAX       7844 TURNER 
AAABb5AAEAAAUIiAAY       7876 ADAMS 
AAABb5AAEAAAUIiAAZ       7900 JAMES 
AAABb5AAEAAAUIiAAa       7902 FORD 
AAABb5AAEAAAUIiAAb       7934 MILLER

"Bellow, Bambi" wrote:

Friends --

One of my associates came up to me Friday with a question. It seemed easy enough. I mean, I've been doing stuff like this for years. The question was, "I have duplicate ids here, some with X field null, some without. I want to get rid of all the duplicates where X field is not null." Fine. Standard correlated subquery.

delete from <tablename> a
where rowid not in (select min(rowid)
where pid=a.pid
and X is not null)
and X is not null

Right?

So, my associate says "what are you doing? you're going to go through the table every single time for each record?" Why, yes, that is indeed what I'm

doing here. "Why don't you just open a cursor and delete that way?" says my

associate...

select pid
from <tablename>
where X is not null
group by pid
having count(*) > 1
{

        skip one 
        delete the rest

}

"But," I tell my associate, "you still have to go to the table to get information on which records to delete. You can't do this without a correlated subquery."

Yes, he assures me, there *has* to be a way.

OK. Maybe I've just been doing things the same way for too long. I'm willing to cop to that. Can anybody out there come up with a way to do this

relatively normal operation without a correlated subquery?

Bambi.
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net <http://www.orafaq.net>
--

Author: Bellow, Bambi
INET: bbellow_at_chi.navtech.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com <http://www.fatcity.com>
San Diego, California -- Mailing list and web hosting services

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Bellow, Bambi
INET: bbellow_at_chi.navtech.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------