Re: SELECT Output Default Ordering ?
Thanks, Cary. Should have mentioned that the CBO has not always known _not_to_sort_ when an index-access path is available - Oracle7/8.0 in particular. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 4:29 PM This is not so much a response to Binley's comment as a general note on the whole thread. Not using ORDER BY when you need it is a huge risk... It doesn't matter what hint you have, if someone drops the index, you're hosed. That's what the ORDER BY is for. If you use the ORDER BY clause with the right index, then performance will be spectacular. The Oracle query optimizers are smart enough not to sort if the rows are coming from an index guaranteed to provide the specified ordering. If the index is missing, then of course performance will blow, but at least the application will produce the correct answer. The users screaming about the performance problem will clue you in that you have to fix something. But at least the users won't ever get the wrong answer back from the application. The huge problem with using the index hint without the ORDER BY clause is that situations can easily occur in which users might go on for *months* getting wrong data out of the database without ever knowing there's a functional bug in their application. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Dallas, Washington, Denver, Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Binley Lim Sent: Wednesday, June 25, 2003 6:51 PM To: Multiple recipients of list ORACLE-L In cases where the SQL is an important (ie frequently used) part of the application, you do get significant gains with the index hint technique. The ORDER BY is a guarantee - you have to pay your insurance premiums (ie additional sort). The index hint is a trade-off with the devil - you have to understand what you are doing, or it may take a few years off your life. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 9:27 AM Beware, though, that without explicit ORDER BY clauses, you're not guaranteed to get the results in the order you expect (I think you mentioned this yourself, for example, when an index is missing). It's not just a performance problem. In some applications, you'll get the *wrong answer* if you don't use ORDER BY. Example: SELECT stuff, score FROM tables WHERE conditions AND ROWNUM=10 ORDER BY score DESC Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Dallas, Washington, Denver, Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- DENNIS WILLIAMS Sent: Wednesday, June 25, 2003 3:58 PM To: Multiple recipients of list ORACLE-L Tanel No ORDER BY. It upsets we DBAs, but some sites purchase Oracle and don't hire a DBA. Especially smaller sites. Naturally Oracle doesn't want to make it sound like they must have a DBA or the customer will probably buy MS SQL. So as a vendor you must have your application run reasonably well with no tuning if you want to sell to small sites. Otherwise the customer will blame you for a fussy application. If you add an ORDER BY, sometimes Oracle will decide it needs to sort, or at least that is the fear. The method I describe has worked pretty well over many Oracle versions (I think one of the Oracle 7.1 versions ignored hints). And yes, if an index is missing, it doesn't blow up, it just gets really sloow. But it makes sense to the customer that if they dropped an index that things foul up. Then they don't blame the vendor, just recreate the index and remember to not get so creative next time. I think the lesson here is that as a DBA you need to support certain applications and understand WHY the vendor did certain things a particular way. Right now my learning project is E.piphany so if anyone on the list works with that, please drop me a private note. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 1:05 PM To: Multiple recipients of list ORACLE-L Vivek If you want the data returned in an order, you can create an index with the order you want, and in your query provide a hint for Oracle to use that index. If your query is such that Oracle actually uses that index, the data will be returned in that order. I work with a large application that entirely depends on this principle. Crude but nevertheless effective. Ouch!!! Or did you mean that you still use ORDER BY, but index scan helps to avoid sorting? What if this index becomes invalid
Re: SELECT Output Default Ordering ?
It depends on the access path and any other implicit sorting. Access path - on a full table scan, the data is returned in the physical order it is stored in the object. An index scan will return the data sorted according to the rule of the index. Implicit sorting - distinct will cause an implicit sort, as will group by. VIVEK_SHARMA wrote: When NOT Giving any Order by Clause , How is the Output of the SELECT Clause ordered by Default ? Assuming There exists a Unique index on the Table Is some Rule followed ? NOTE Records may have been INSERTED into the Table in some manner differing from the Order of the data of the Unique index Key fields. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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).begin:vcard n:Fink;Daniel tel;cell:303.808.3282 tel;work:303.272.3225 x-mozilla-html:TRUE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:DB Services Lead x-mozilla-cpt:;-4832 fn:Daniel Fink end:vcard
RE: SELECT Output Default Ordering ?
Vivek If you want the data returned in an order, you can create an index with the order you want, and in your query provide a hint for Oracle to use that index. If your query is such that Oracle actually uses that index, the data will be returned in that order. I work with a large application that entirely depends on this principle. Crude but nevertheless effective. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 8:33 AM To: Multiple recipients of list ORACLE-L When NOT Giving any Order by Clause , How is the Output of the SELECT Clause ordered by Default ? Assuming There exists a Unique index on the Table Is some Rule followed ? NOTE Records may have been INSERTED into the Table in some manner differing from the Order of the data of the Unique index Key fields. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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: SELECT Output Default Ordering ?
Curious--if you can specify hints, why not just specify an ORDER BY clause? Wouldn't that be more readable/maintainable/portable? I don't know where I got it, but I had the impression that row order was explicitly undefined (in one of the SQL standards?) when you don't do an explicit ORDER BY. A given version/brand of rdbms may act consistently, but you shouldn't rely on it, as the next version or brand may do something else. But maybe I'm making that up... Cheers, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, June 25, 2003 8:10 AM To: Multiple recipients of list ORACLE-L Vivek If you want the data returned in an order, you can create an index with the order you want, and in your query provide a hint for Oracle to use that index. If your query is such that Oracle actually uses that index, the data will be returned in that order. I work with a large application that entirely depends on this principle. Crude but nevertheless effective. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 8:33 AM To: Multiple recipients of list ORACLE-L When NOT Giving any Order by Clause , How is the Output of the SELECT Clause ordered by Default ? Assuming There exists a Unique index on the Table Is some Rule followed ? NOTE Records may have been INSERTED into the Table in some manner differing from the Order of the data of the Unique index Key fields. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: SELECT Output Default Ordering ?
Vivek, Oracle has always said that you cannot predict the order that records are retrieved in. If you create a new table and insert records, you can be pretty sure that when you query them, they will come out in the same order. *BUT* - once you delete a record and insert more new records, the order becomes random. this is because Oracle will reuse the space vacated by the deleted records. I don't know of any way to predict how it will use that space. That's why you should always include an order by clause. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, June 25, 2003 9:33 AM To: Multiple recipients of list ORACLE-L When NOT Giving any Order by Clause , How is the Output of the SELECT Clause ordered by Default ? Assuming There exists a Unique index on the Table Is some Rule followed ? NOTE Records may have been INSERTED into the Table in some manner differing from the Order of the data of the Unique index Key fields. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F 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: SELECT Output Default Ordering ?
The official answer is, however the database feels like doing it at the time. In practice, it depends on the access method. If an index is used the output may be sorted by that index. Otherwise it might be by rowid. There are no guarantees and no defaults. Relational data has no default order. -Original Message- From: VIVEK_SHARMA [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 9:33 AM To: Multiple recipients of list ORACLE-L Subject: SELECT Output Default Ordering ? When NOT Giving any Order by Clause , How is the Output of the SELECT Clause ordered by Default ? Assuming There exists a Unique index on the Table Is some Rule followed ? NOTE Records may have been INSERTED into the Table in some manner differing from the Order of the data of the Unique index Key fields. Thanks LEGAL NOTICE: Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this e-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Sarnowski, Chris 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: SELECT Output Default Ordering ?
Vivek If you want the data returned in an order, you can create an index with the order you want, and in your query provide a hint for Oracle to use that index. If your query is such that Oracle actually uses that index, the data will be returned in that order. I work with a large application that entirely depends on this principle. Crude but nevertheless effective. Ouch!!! Or did you mean that you still use ORDER BY, but index scan helps to avoid sorting? What if this index becomes invalid for some reason, your hint won't be used, and if you don't have order by, your application will fail big time! Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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: SELECT Output Default Ordering ?
If you create a new table and insert records, you can be pretty sure that when you query them, they will come out in the same order. No, you can't. For example when parallel query is used, each slave reads it's own set and the returning order isn't guaranteed. The same with indexes, what if fast full scan is used... you cant be sure. Tanel. *BUT* - once you delete a record and insert more new records, the order becomes random. this is because Oracle will reuse the space vacated by the deleted records. I don't know of any way to predict how it will use that space. That's why you should always include an order by clause. Hope this helps. Tom Mercadante Oracle Certified Professional -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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: SELECT Output Default Ordering ?
Tanel No ORDER BY. It upsets we DBAs, but some sites purchase Oracle and don't hire a DBA. Especially smaller sites. Naturally Oracle doesn't want to make it sound like they must have a DBA or the customer will probably buy MS SQL. So as a vendor you must have your application run reasonably well with no tuning if you want to sell to small sites. Otherwise the customer will blame you for a fussy application. If you add an ORDER BY, sometimes Oracle will decide it needs to sort, or at least that is the fear. The method I describe has worked pretty well over many Oracle versions (I think one of the Oracle 7.1 versions ignored hints). And yes, if an index is missing, it doesn't blow up, it just gets really sloow. But it makes sense to the customer that if they dropped an index that things foul up. Then they don't blame the vendor, just recreate the index and remember to not get so creative next time. I think the lesson here is that as a DBA you need to support certain applications and understand WHY the vendor did certain things a particular way. Right now my learning project is E.piphany so if anyone on the list works with that, please drop me a private note. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 1:05 PM To: Multiple recipients of list ORACLE-L Vivek If you want the data returned in an order, you can create an index with the order you want, and in your query provide a hint for Oracle to use that index. If your query is such that Oracle actually uses that index, the data will be returned in that order. I work with a large application that entirely depends on this principle. Crude but nevertheless effective. Ouch!!! Or did you mean that you still use ORDER BY, but index scan helps to avoid sorting? What if this index becomes invalid for some reason, your hint won't be used, and if you don't have order by, your application will fail big time! Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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: SELECT Output Default Ordering ?
Beware, though, that without explicit ORDER BY clauses, you're not guaranteed to get the results in the order you expect (I think you mentioned this yourself, for example, when an index is missing). It's not just a performance problem. In some applications, you'll get the *wrong answer* if you don't use ORDER BY. Example: SELECT stuff, score FROM tables WHERE conditions AND ROWNUM=10 ORDER BY score DESC Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Dallas, Washington, Denver, Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- DENNIS WILLIAMS Sent: Wednesday, June 25, 2003 3:58 PM To: Multiple recipients of list ORACLE-L Tanel No ORDER BY. It upsets we DBAs, but some sites purchase Oracle and don't hire a DBA. Especially smaller sites. Naturally Oracle doesn't want to make it sound like they must have a DBA or the customer will probably buy MS SQL. So as a vendor you must have your application run reasonably well with no tuning if you want to sell to small sites. Otherwise the customer will blame you for a fussy application. If you add an ORDER BY, sometimes Oracle will decide it needs to sort, or at least that is the fear. The method I describe has worked pretty well over many Oracle versions (I think one of the Oracle 7.1 versions ignored hints). And yes, if an index is missing, it doesn't blow up, it just gets really sloow. But it makes sense to the customer that if they dropped an index that things foul up. Then they don't blame the vendor, just recreate the index and remember to not get so creative next time. I think the lesson here is that as a DBA you need to support certain applications and understand WHY the vendor did certain things a particular way. Right now my learning project is E.piphany so if anyone on the list works with that, please drop me a private note. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 1:05 PM To: Multiple recipients of list ORACLE-L Vivek If you want the data returned in an order, you can create an index with the order you want, and in your query provide a hint for Oracle to use that index. If your query is such that Oracle actually uses that index, the data will be returned in that order. I work with a large application that entirely depends on this principle. Crude but nevertheless effective. Ouch!!! Or did you mean that you still use ORDER BY, but index scan helps to avoid sorting? What if this index becomes invalid for some reason, your hint won't be used, and if you don't have order by, your application will fail big time! Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap 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: SELECT Output Default Ordering ?
In cases where the SQL is an important (ie frequently used) part of the application, you do get significant gains with the index hint technique. The ORDER BY is a guarantee - you have to pay your insurance premiums (ie additional sort). The index hint is a trade-off with the devil - you have to understand what you are doing, or it may take a few years off your life. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 9:27 AM Beware, though, that without explicit ORDER BY clauses, you're not guaranteed to get the results in the order you expect (I think you mentioned this yourself, for example, when an index is missing). It's not just a performance problem. In some applications, you'll get the *wrong answer* if you don't use ORDER BY. Example: SELECT stuff, score FROM tables WHERE conditions AND ROWNUM=10 ORDER BY score DESC Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Dallas, Washington, Denver, Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- DENNIS WILLIAMS Sent: Wednesday, June 25, 2003 3:58 PM To: Multiple recipients of list ORACLE-L Tanel No ORDER BY. It upsets we DBAs, but some sites purchase Oracle and don't hire a DBA. Especially smaller sites. Naturally Oracle doesn't want to make it sound like they must have a DBA or the customer will probably buy MS SQL. So as a vendor you must have your application run reasonably well with no tuning if you want to sell to small sites. Otherwise the customer will blame you for a fussy application. If you add an ORDER BY, sometimes Oracle will decide it needs to sort, or at least that is the fear. The method I describe has worked pretty well over many Oracle versions (I think one of the Oracle 7.1 versions ignored hints). And yes, if an index is missing, it doesn't blow up, it just gets really sloow. But it makes sense to the customer that if they dropped an index that things foul up. Then they don't blame the vendor, just recreate the index and remember to not get so creative next time. I think the lesson here is that as a DBA you need to support certain applications and understand WHY the vendor did certain things a particular way. Right now my learning project is E.piphany so if anyone on the list works with that, please drop me a private note. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 1:05 PM To: Multiple recipients of list ORACLE-L Vivek If you want the data returned in an order, you can create an index with the order you want, and in your query provide a hint for Oracle to use that index. If your query is such that Oracle actually uses that index, the data will be returned in that order. I work with a large application that entirely depends on this principle. Crude but nevertheless effective. Ouch!!! Or did you mean that you still use ORDER BY, but index scan helps to avoid sorting? What if this index becomes invalid for some reason, your hint won't be used, and if you don't have order by, your application will fail big time! Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap 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
Re: SELECT Output Default Ordering ?
One place where we use index hints is to effect an give me the 100 oldest rows type of queries. On a table containing millions of rows performing a sort would be very expensive compared to a quick index scan. This risk would not be worth taking if the data volumes were lower. Yes, we are aware of the risk though and it wouldn't break the application if the query failed. We like to process things in order, but as long as they meet all criteria we could process out of order quite safely. Binley Lim [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] co.nz cc: Sent by: Subject: Re: SELECT Output Default Ordering ? [EMAIL PROTECTED] .com 26/06/2003 09:51 Please respond to ORACLE-L In cases where the SQL is an important (ie frequently used) part of the application, you do get significant gains with the index hint technique. The ORDER BY is a guarantee - you have to pay your insurance premiums (ie additional sort). The index hint is a trade-off with the devil - you have to understand what you are doing, or it may take a few years off your life. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 9:27 AM Beware, though, that without explicit ORDER BY clauses, you're not guaranteed to get the results in the order you expect (I think you mentioned this yourself, for example, when an index is missing). It's not just a performance problem. In some applications, you'll get the *wrong answer* if you don't use ORDER BY. Example: SELECT stuff, score FROM tables WHERE conditions AND ROWNUM=10 ORDER BY score DESC Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Dallas, Washington, Denver, Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- DENNIS WILLIAMS Sent: Wednesday, June 25, 2003 3:58 PM To: Multiple recipients of list ORACLE-L Tanel No ORDER BY. It upsets we DBAs, but some sites purchase Oracle and don't hire a DBA. Especially smaller sites. Naturally Oracle doesn't want to make it sound like they must have a DBA or the customer will probably buy MS SQL. So as a vendor you must have your application run reasonably well with no tuning if you want to sell to small sites. Otherwise the customer will blame you for a fussy application. If you add an ORDER BY, sometimes Oracle will decide it needs to sort, or at least that is the fear. The method I describe has worked pretty well over many Oracle versions (I think one of the Oracle 7.1 versions ignored hints). And yes, if an index is missing, it doesn't blow up, it just gets really sloow. But it makes sense to the customer that if they dropped an index that things foul up. Then they don't blame the vendor, just recreate the index and remember to not get so creative next time. I think the lesson here is that as a DBA you need to support certain applications and understand WHY the vendor did certain things a particular way. Right now my learning project is E.piphany so if anyone on the list works with that, please drop me a private note. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent
RE: SELECT Output Default Ordering ?
This is not so much a response to Binley's comment as a general note on the whole thread. Not using ORDER BY when you need it is a huge risk... It doesn't matter what hint you have, if someone drops the index, you're hosed. That's what the ORDER BY is for. If you use the ORDER BY clause with the right index, then performance will be spectacular. The Oracle query optimizers are smart enough not to sort if the rows are coming from an index guaranteed to provide the specified ordering. If the index is missing, then of course performance will blow, but at least the application will produce the correct answer. The users screaming about the performance problem will clue you in that you have to fix something. But at least the users won't ever get the wrong answer back from the application. The huge problem with using the index hint without the ORDER BY clause is that situations can easily occur in which users might go on for *months* getting wrong data out of the database without ever knowing there's a functional bug in their application. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Dallas, Washington, Denver, Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Binley Lim Sent: Wednesday, June 25, 2003 6:51 PM To: Multiple recipients of list ORACLE-L In cases where the SQL is an important (ie frequently used) part of the application, you do get significant gains with the index hint technique. The ORDER BY is a guarantee - you have to pay your insurance premiums (ie additional sort). The index hint is a trade-off with the devil - you have to understand what you are doing, or it may take a few years off your life. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, June 26, 2003 9:27 AM Beware, though, that without explicit ORDER BY clauses, you're not guaranteed to get the results in the order you expect (I think you mentioned this yourself, for example, when an index is missing). It's not just a performance problem. In some applications, you'll get the *wrong answer* if you don't use ORDER BY. Example: SELECT stuff, score FROM tables WHERE conditions AND ROWNUM=10 ORDER BY score DESC Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Dallas, Washington, Denver, Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- DENNIS WILLIAMS Sent: Wednesday, June 25, 2003 3:58 PM To: Multiple recipients of list ORACLE-L Tanel No ORDER BY. It upsets we DBAs, but some sites purchase Oracle and don't hire a DBA. Especially smaller sites. Naturally Oracle doesn't want to make it sound like they must have a DBA or the customer will probably buy MS SQL. So as a vendor you must have your application run reasonably well with no tuning if you want to sell to small sites. Otherwise the customer will blame you for a fussy application. If you add an ORDER BY, sometimes Oracle will decide it needs to sort, or at least that is the fear. The method I describe has worked pretty well over many Oracle versions (I think one of the Oracle 7.1 versions ignored hints). And yes, if an index is missing, it doesn't blow up, it just gets really sloow. But it makes sense to the customer that if they dropped an index that things foul up. Then they don't blame the vendor, just recreate the index and remember to not get so creative next time. I think the lesson here is that as a DBA you need to support certain applications and understand WHY the vendor did certain things a particular way. Right now my learning project is E.piphany so if anyone on the list works with that, please drop me a private note. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 1:05 PM To: Multiple recipients of list ORACLE-L Vivek If you want the data returned in an order, you can create an index with the order you want, and in your query provide a hint for Oracle to use that index. If your query is such that Oracle actually uses that index, the data will be returned in that order. I work with a large application that entirely depends on this principle. Crude but nevertheless effective. Ouch!!! Or did you mean that you still use ORDER BY, but index scan helps to avoid sorting? What if this index becomes invalid for some reason, your hint won't be used, and if you don't have order by, your application will fail big time! Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
