Search

OakieTags

Who's online

There are currently 0 users and 26 guests online.

Recent comments

SQL

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

Simple SQL with and without Inline Views

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

Brain Teaser: Why is this Query Performing a Full Table Scan

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

The New Order Oracle Coding Challenge 4 – Tic Tac Toe

(Back to the Previous Post in the Series) Tic Tac Toe, the game of X’s and O’s, was an oddly popular game in elementary school.  When playing the game you quickly learn a couple of rules: Because X always places his mark first (alternating between X and O), there is an unfair advantage for the player placing [...]

The New Order Oracle Coding Challenge 3 – Mind Boggle

August 5, 2011 (Modified August 7, 2011) (Back to the Previous Post in the Series) (Forward to the Next Post in the Series) In part 1 of this series the challenge was to simply reverse the order of digits in the numbers from 1 to 1,000,000 to find that cases where the numbers formed by the reverse ordered digits [...]