How about using Minus? (I'm a set operator groupie.) It usually performs
well for me, though I've done no detailed analysis.
delete from theTable
where rowid in
(
select rowid from theTable where X is not null
minus
select min(rowid) from theTable where X is not null group by X
) ;
Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager, [EMAIL PROTECTED])
[EMAIL PROTECTED]
"Bellow, Bambi"
<[EMAIL PROTECTED] To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
ech.com> cc:
Sent by: Subject: RE: Life without a correlated
subquery
[EMAIL PROTECTED]
.com
11/10/2003 12:24
PM
Please respond to
ORACLE-L
Thanks Folks!
The inline query, indeed, beat the correlated subquery.
Bambi.
-----Original Message-----
From: Daniel Fink [mailto:[EMAIL PROTECTED]
Sent: Monday, November 10, 2003 11:34 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: Life without a correlated subquery
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.
--
Author: Bellow, Bambi
INET: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 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: [EMAIL PROTECTED] (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).
