January 20, 2012 A request for assistance came in from an ERP mailing list. The original poster (OP) is running an unspecified version of Oracle Database 9i, and is in need of a solution to generate new part numbers with prefixed characters that describe the type of part, followed by a sequential number that is [...]
January 16, 2012 I put together a test case to demonstrate how the physical reads autotrace statistic could exceed the consistent gets autotrace statistic if a single-pass or multi-pass workarea execution were performed during the execution of the SQL statement. If you are interested, you can see the test case in this recent OTN thread. [...]
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 [...]