Re: Life without a correlated subquery
I did a quick test with autotrace and here are the results. The bottom line is that the inline query used 6 i/os, the subquery used 46. (Irepeated this test multiple times, same result). 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 Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN 2 1 VIEW 3 2 SORT (GROUP BY) 4 3 TABLE ACCESS (FULL) OF 'EMP' 5 1 FILTER 6 5 SORT (JOIN) 7 6 TABLE ACCESS (FULL) OF 'EMP' Statistics -- 0 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 1096 bytes sent via SQL*Net to client 651 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 14 rows processed SQL> l 1 select e1.rowid, 2 e1.empno, 3 e1.ename 4 from emp e1 5 where e1.rowid not in (select min(e2.rowid) min_rowid 6 from emp e2 7* where e1.empno = e2.empno) SQL> / 14 rows selected. Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 FILTER 2 1 TABLE ACCESS (FULL) OF 'EMP' 3 1 SORT (AGGREGATE) 4 3 TABLE ACCESS (FULL) OF 'EMP' Statistics -- 0 recursive calls 0 db block gets 46 consistent gets 0 physical reads 0 redo size 1096 bytes sent via SQL*Net to client 651 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed
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
Ipopulated 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
--
Author: Bellow, Bambi
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).
RE: Life without a correlated subquery
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
AMTo: Multiple recipients of list ORACLE-LSubject: Re:
Life without a correlated subqueryIt 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
Ipopulated 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 -- Author:
Bellow, Bambi 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).
RE: Life without a correlated subquery
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 6from emp 7group 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
