RE: SQL DISTINCT alternate
There are also a lot of cases where you can get rid of a subquery. Consider the following SELECT DISTINCT emplid FROMemp_history eh1 WHERE salary 10; SELECT emplid FROM emp_history eh1 WHERE rowid = (SELECT MAX(rowid) FROM emp_history eh2 WHERE eh1.emplid = eh2.emplid ANDeh2.salary 10) Kevin -Original Message- Sent: Wednesday, June 20, 2001 4:17 PM To: Multiple recipients of list ORACLE-L Toepke, Kevin M wrote: As I see it, there are several questions here: 1) Can I use DISTINCT to do a sort? Yes and no. In Oracle 7, a DISTINCT does an implicit sort In Oracle 8, it does a SORT NOSORT operation. Dupicates are removed but the output is NOT guaranteed to be in sorted order. 2) How do I sort the output? Use the ORDER BY clause as in: SELECT a, b, c FROM tab_1 ORDER BY a, b, c 3) How do I get around the performance hit of a SORT operation? In Oracle 8.0 through 8.1.6 (fixed in 9i and 8.1.7) there is a bug that causes the optimizer to choose a horrendous execution path if you are ordering by a column that is referenced in the WHERE clause. Workaround From: SELECT a, b, c FROM tab_1 WHERE a 33 ORDER BY a, b, c: to: SELECT /*+ NO_MERGE(d) */ a, b, c FROM ( SELECT a, b, c FROM tab_1 WHERE a 33 ) ORDRE BY a, b, c; Kevin -Original Message- Sent: Wednesday, June 20, 2001 1:50 PM To: Multiple recipients of list ORACLE-L Hi In a sql stmt what will be the impact if we used DISTINCT clause and how we can overcomes on the performance bottleneck caused by DISTINCT. like ... SELECT DISTINCT A,B,C FROM TAB_1 I want to get same output without using distinct?Is this possible?If, YES then how? Thanks in advance -Seema I can't see any way of getting rid of a DISTINCT if a single table is involved - unless you are querying the wrong table and in fact you'd rather query a table for which what you are looking for is the primary key. When I see a DISTINCT and a join, it usually smells of lazy programming and in most cases you can get rid of it with a correlated subquery. -- Regards, Stephane Faroult Oriole Corporation Voice: +44 (0) 7050-696-269 Fax:+44 (0) 7050-696-449 Performance Tools Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
SQL DISTINCT alternate
Hi In a sql stmt what will be the impact if we used DISTINCT clause and how we can overcomes on the performance bottleneck caused by DISTINCT. like ... SELECT DISTINCT A,B,C FROM TAB_1 I want to get same output without using distinct?Is this possible?If, YES then how? Thanks in advance -Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: SQL DISTINCT alternate
What about using UNIQUE Select UNIQUE. Seema Singh wrote: Hi In a sql stmt what will be the impact if we used DISTINCT clause and how we can overcomes on the performance bottleneck caused by DISTINCT. like ... SELECT DISTINCT A,B,C FROM TAB_1 I want to get same output without using distinct?Is this possible?If, YES then how? Thanks in advance -Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). begin:vcard n:de Urioste;Luis Octavio tel;fax:850.455.0673 tel;work:850.457.3218 x-mozilla-html:FALSE org:Raytheon Aerospace;IT version:2.1 email;internet:[EMAIL PROTECTED] title:Systems Analyst adr;quoted-printable:;;250 San Carlos Road=0D=0ABuilding 1853 - UNFO;Pensacola;Florida;32534;U.S.A. fn:Luis Octavio de Urioste end:vcard
Re: SQL DISTINCT alternate
UNIQUE is just another way of saying DISTINCT. They do the same thing. [EMAIL PROTECTED] 06/20/01 02:26PM What about using UNIQUE Select UNIQUE. Seema Singh wrote: Hi In a sql stmt what will be the impact if we used DISTINCT clause and how we can overcomes on the performance bottleneck caused by DISTINCT. like ... SELECT DISTINCT A,B,C FROM TAB_1 I want to get same output without using distinct?Is this possible?If, YES then how? Thanks in advance -Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: William Beilstein INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: SQL DISTINCT alternate
As I see it, there are several questions here: 1) Can I use DISTINCT to do a sort? Yes and no. In Oracle 7, a DISTINCT does an implicit sort In Oracle 8, it does a SORT NOSORT operation. Dupicates are removed but the output is NOT guaranteed to be in sorted order. 2) How do I sort the output? Use the ORDER BY clause as in: SELECT a, b, c FROM tab_1 ORDER BY a, b, c 3) How do I get around the performance hit of a SORT operation? In Oracle 8.0 through 8.1.6 (fixed in 9i and 8.1.7) there is a bug that causes the optimizer to choose a horrendous execution path if you are ordering by a column that is referenced in the WHERE clause. Workaround From: SELECT a, b, c FROM tab_1 WHERE a 33 ORDER BY a, b, c: to: SELECT /*+ NO_MERGE(d) */ a, b, c FROM ( SELECT a, b, c FROM tab_1 WHERE a 33 ) ORDRE BY a, b, c; Kevin -Original Message- Sent: Wednesday, June 20, 2001 1:50 PM To: Multiple recipients of list ORACLE-L Hi In a sql stmt what will be the impact if we used DISTINCT clause and how we can overcomes on the performance bottleneck caused by DISTINCT. like ... SELECT DISTINCT A,B,C FROM TAB_1 I want to get same output without using distinct?Is this possible?If, YES then how? Thanks in advance -Seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: SQL DISTINCT alternate
Toepke, Kevin M wrote: As I see it, there are several questions here: 1) Can I use DISTINCT to do a sort? Yes and no. In Oracle 7, a DISTINCT does an implicit sort In Oracle 8, it does a SORT NOSORT operation. Dupicates are removed but the output is NOT guaranteed to be in sorted order. 2) How do I sort the output? Use the ORDER BY clause as in: SELECT a, b, c FROM tab_1 ORDER BY a, b, c 3) How do I get around the performance hit of a SORT operation? In Oracle 8.0 through 8.1.6 (fixed in 9i and 8.1.7) there is a bug that causes the optimizer to choose a horrendous execution path if you are ordering by a column that is referenced in the WHERE clause. Workaround From: SELECT a, b, c FROM tab_1 WHERE a 33 ORDER BY a, b, c: to: SELECT /*+ NO_MERGE(d) */ a, b, c FROM ( SELECT a, b, c FROM tab_1 WHERE a 33 ) ORDRE BY a, b, c; Kevin -Original Message- Sent: Wednesday, June 20, 2001 1:50 PM To: Multiple recipients of list ORACLE-L Hi In a sql stmt what will be the impact if we used DISTINCT clause and how we can overcomes on the performance bottleneck caused by DISTINCT. like ... SELECT DISTINCT A,B,C FROM TAB_1 I want to get same output without using distinct?Is this possible?If, YES then how? Thanks in advance -Seema I can't see any way of getting rid of a DISTINCT if a single table is involved - unless you are querying the wrong table and in fact you'd rather query a table for which what you are looking for is the primary key. When I see a DISTINCT and a join, it usually smells of lazy programming and in most cases you can get rid of it with a correlated subquery. -- Regards, Stephane Faroult Oriole Corporation Voice: +44 (0) 7050-696-269 Fax:+44 (0) 7050-696-449 Performance Tools Free Scripts -- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
