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
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 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 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 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
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 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 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
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. (I repeated 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
Life without a correlated subquery
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 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 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).