How many times have you heard someone say, “The rows always came back in the correct order, but since the *event* it’s now wrong. The *event* may be an upgrade or some maintenance task on the table etc.
Oracle clearly say,
ORDER BYclause to order rows returned by the statement. Without an
ORDER BY clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.”
This also applies for the
GROUP BY clause, that haunted people during their 10g upgrades.
GROUP BYclause groups rows but does not guarantee the order of the result set. To order the groupings, use the
Both these statements can be found in the docs for the SELECT statements.
January 1, 2012 (Back to the Previous Post in the Series) Nearly two years ago I posted the following execution plan in a blog article and asked whether or not there was anything strange about the %CPU column: -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ [...]
November 28, 2011 There are a few articles that can be found on the Internet that describe the cause of indexes with names similar to BIN$ld5VAtb88PngQAB/AQF8hg==$0 and BIN$PESygWW5R0WhbOaDugxqwQ==$0 appearing in execution plans. As is likely known by readers, the Oracle Database documentation describes that these object names are associated with the recycle bin that was introduced in Oracle Database [...]
November 23, 2011 I previously wrote a couple of articles that mention reasons why an index might not be use for a particular query, including an article that was formatted as a True or False quiz with several reference articles. A few days ago I saw an OTN thread that caught my curiosity, where the [...]
November 21, 2011 A recent thread in the comp.databases.oracle.server usenet group asked whether or not a SELECT FOR UPDATE statement locks rows in the order specified by the ORDER BY clause. Why might this be an important question? Possibly if the SKIP LOCKED clause is implemented in the SELECT FOR UPDATE statement? Possibly if a [...]
November 16, 2011 I noticed an interesting request on the comp.databases.oracle.misc usenet group, and I thought that I would make an attempt at solving the request. The original poster (OP) stated that he had a table with data similar to the following: TX ID DEPT LOCATION LOAD 1 99 A NY 12 2 99 A [...]
November 3, 2011 An interesting request came in from an ERP mailing list – how would you write a SQL statement that indicates the next operation in a manufacturing process. Sounds like an easy requirement. Let’s take a look at a graphical view of one example (the graphical view is created using a program that I wrote [...]
October 26, 2011 (Back to the Previous Post in the Series) In the previous article of this series we examined a couple of moderately simple data retrieval requirements, and used inline views and/or analytic functions to solve those data retrieval requirements. I recently saw another opportunity to help a person on the ERP mailing list with [...]
October 21, 2011 (Forward to the Next Post in the Series) Sometimes it is interesting to take a step back from some of the more difficult to construct SQL statements, and just put together something simple. The following request recently arrived through an ERP mailing list: “I want to find the last ship date for all the [...]
September 14, 2011 While taking a look at the OTN threads this morning, I found an interesting test case, where the OP was asking why a query used a full table scan and not an index range scan. The OP would like for the query to use the index without using a hint in the query. [...]