Search

OakieTags

Who's online

There are currently 0 users and 47 guests online.

Recent comments

Affiliations

SQL

Finding the Next Primary Key Value, a Pre-fixed Solution

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 [...]

What Number Immediately Follows 3,999? Oracle Database Refuses to Answer

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.  [...]

Should SQL have a compulsory ORDER BY clause?

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,

“Use the ORDER BY clause 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.

“The GROUP BY clause groups rows but does not guarantee the order of the result set. To order the groupings, use the ORDER BY clause.”

Both these statements can be found in the docs for the SELECT statements.

What is the Meaning of the %CPU Column in an Explain Plan? 2

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  [...]

BIN$ Index Found in the Execution Plan – Digging through the Recycle Bin

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 [...]

Why Isn’t My Index Used… When USER2 Executes this Query?

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 [...]

Select For Update – In What Order are the Rows Locked?

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 [...]

Matching the Expected Output – Analytic RANK, ROW_NUMBER, DENSE_RANK, or Something Different?

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        [...]

Simple SQL – Finding the Next Operation

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 [...]

Simple SQL with and without Inline Views 2

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 [...]