RE: Help - Tuning SQL - ISSUE CLOSED
Thanks for the help guys. Michael Armstead Database Administrator, OCP-Certified Corporate & Finance Information Systems Glaxo SmithKline > -Original Message- > From: Armstead, Michael A [SMTP:[EMAIL PROTECTED]] > Sent: Wednesday, February 28, 2001 2:51 PM > To: Multiple recipients of list ORACLE-L > Subject: Help - Tuning SQL > > I've got some simple questions on tuning SQL. Please reply with > references, > I'm not looking for guesses. > > * If you have a SELECT statement with a WHERE clause and 2 AND > clauses, which one is processed first, the WHERE clause, the first AND > clause, or the second AND clause? > * Is there any performance loss in using "<>" over "!="? > > Michael Armstead > Database Administrator, OCP-Certified > Corporate & Finance Information Systems > Glaxo SmithKline > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Armstead, Michael A > 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: Armstead, Michael A 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: Help - Tuning SQL
Regarding the first point the easiest way to check is to run explain plan fro the 2 statements and compare the results On the second point the != issue Guy Harrisons's excellent book Oracle SQL - High Performance Tuning states "Oracle will not employ an index if the NOT EQUALS operator is employed." Using > and < will use an index (other conditions permitting) HTH John -Original Message- Michael A Sent: 28 February 2001 19:51 To: Multiple recipients of list ORACLE-L I've got some simple questions on tuning SQL. Please reply with references, I'm not looking for guesses. * If you have a SELECT statement with a WHERE clause and 2 AND clauses, which one is processed first, the WHERE clause, the first AND clause, or the second AND clause? * Is there any performance loss in using "<>" over "!="? Michael Armstead Database Administrator, OCP-Certified Corporate & Finance Information Systems Glaxo SmithKline -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Armstead, Michael A 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: John Hallas 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: Help - Tuning SQL
The Rule-based optimizer is influenced by the order of your WHERE statements ... it won't re-arrange them to improve performance. If you're using the cost-based optimizer, I believe it will decide what order to process the WHERE statement clauses, based on what it decides is the most efficient access method. Dave > -Original Message- > From: Armstead, Michael A [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, February 28, 2001 1:51 PM > To: Multiple recipients of list ORACLE-L > Subject: Help - Tuning SQL > > > I've got some simple questions on tuning SQL. Please reply > with references, > I'm not looking for guesses. > > * If you have a SELECT statement with a WHERE clause and 2 AND > clauses, which one is processed first, the WHERE clause, the first AND > clause, or the second AND clause? > * Is there any performance loss in using "<>" over "!="? > > Michael Armstead > Database Administrator, OCP-Certified > Corporate & Finance Information Systems > Glaxo SmithKline > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Armstead, Michael A > 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: 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: Help - Tuning SQL
Since you insisted on no guessing the only true test is for you to do it. Since we dont have your database to test it out on, its time for you to bust out the documentation, look at explain plan, tkprof,. Happy testing. joe I've got some simple questions on tuning SQL. Please reply with references, I'm not looking for guesses. * If you have a SELECT statement with a WHERE clause and 2 AND clauses, which one is processed first, the WHERE clause, the first AND clause, or the second AND clause? * Is there any performance loss in using "<>" over "!="? Michael Armstead Database Administrator, OCP-Certified Corporate & Finance Information Systems Glaxo SmithKline -- Joe Testa http://www.oracle-dba.com Performing Remote DBA Services, need some backup DBA support? For Sale: Oracle-dba.com domain, its not going cheap but feel free to ask :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joseph S. Testa 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).
