WHERE MOD(ROWNUM,100) = 0

June 30, 2011

I saw an interesting search keyword the other day that generated several hits on my blog articles.  The search keyword was simply:

WHERE MOD(ROWNUM,100) = 0

Just what is interesting about the above?  Had someone seen that syntax and wondered how it worked, or was someone trying to use that syntax and found that it did not work quite as expected?

The MOD function returns the remainder value when dividing the number (ROWNUM in this case) by the specified denominator value (100 in this case).  The result of the MOD function where 100 is specified as the second parameter, with the sequential numbers 1-201 fed in as the first parameter, will appear as follows:

SELECT
  MOD(ROWNUM,100) C1
FROM
  DUAL
CONNECT BY
  LEVEL<=201;

C1
--
 1
 2
 3
 4
 5
 6
...
98
99
 0
 1
 2
 3
 4
...
98
99
 0
 1

As shown above, there is a sequence of 1-99, and then repeating  sequences of 0-99.  Note, however, that it appears the person performing the search is intending to place the MOD function in the WHERE clause, likely to retrieve every 100th row in the resultset.  The problem, of course, is this approach does not work with the ROWNUM pseudo-column.

SELECT
  *
FROM
  (SELECT
     ROWNUM C1
   FROM
     DUAL
   CONNECT BY
     LEVEL<=1000)
WHERE
  MOD(ROWNUM,100) = 0;

no rows selected 

However, if we create a table and populate the table with the same 1000 rows that would be produced by the inline view found in the above SQL statement:

DROP TABLE T1 PURGE;

CREATE TABLE T1 AS
SELECT
  ROWNUM C1
FROM
  DUAL
CONNECT BY
  LEVEL<=1000;  

Then, select from that table, switching out the ROWNUM keyword with the C1 column found in the table T1:

SELECT
  *
FROM
  T1
WHERE
  MOD(C1,100) = 0;

    C1
------
   100
   200
   300
   400
   500
   600
   700
   800
   900
  1000

10 rows selected. 

We see that 10 rows were returned – every 100th row.  At this point, you might be wondering if the following will work:

SELECT
  *
FROM
  (SELECT
     ROWNUM C1
   FROM
     DUAL
   CONNECT BY
     LEVEL<=1000)
WHERE
  MOD(C1,100) = 0; 

Here is the output of the above SQL statement:

    C1
------
   100
   200
   300
   400
   500
   600
   700
   800
   900
  1000

10 rows selected. 

So, the query works as expected when we use the alias of the ROWNUM column from the inline view when the MOD function appears in the WHERE clause, but the query does not work as (probably) intended when the ROWNUM pseudo-column is used directly in the MOD function in the WHERE clause.  Why?  Think about when the ROWNUM value is assigned – not until after the WHERE clause is applied.  See the comments attached in this related blog article.

Find Objects with the Greatest Wait Time – What is Wrong with this Quote?

June 28, 2011

While reading the alpha edition of the “Oracle Database 11g Performance Tuning Recipes” book, I noticed a couple of interesting SQL statements in Recipe 5-17 that just did not look right.  One of those SQL statements, slightly reformatted, appears below:

SELECT
  A.CURRENT_OBJ#,
  O.OBJECT_NAME,
  O.OBJECT_TYPE,
  A.EVENT,
  SUM(A.WAIT_TIME + A.TIME_WAITED) TOTAL_WAIT_TIME
FROM
  V$ACTIVE_SESSION_HISTORY A,
  DBA_OBJECTS D
WHERE
  A.SAMPLE_TIME BETWEEN SYSDATE – 30/2880 AND SYSDATE
  AND A.CURRENT_OBJ# = D.OBJECT_ID
GROUP BY
  A.CURRENT_OBJ#,
  D.OBJECT_NAME,
  D.OBJECT_TYPE,
  A.EVENT
ORDER BY
  TOTAL_WAIT_TIME; 

The above SQL statement is supposed to retrieve a list of object names that experienced the most wait time in the last 15 minutes.  What is wrong with the query?  Consider that a slight variation of the above query is found in the following locations:

• Page 1,170 of the book “Expert Oracle Database 11g Administration“
• An OTN thread where the original poster claimed that the query required too much time to execute.
• An undated news article.
• Page 213 of the book “Oracle Tuning: The Definitive Reference, Second Edition“
• Page 160 of the book “Oracle Tuning Power Scripts: With 100+ High Performance SQL Scripts”

So, what is wrong with the query?  OK, the table aliases are inconsistent, but that is too easy.  Let’s refer to the Oracle Database 11.2 documentation for the V$ACTIVE_SESSION_HISTORY view.

“V$ACTIVE_SESSION_HISTORY displays sampled session activity in the database. It contains snapshots of active database sessions taken once a second. A database session is considered active if it was on the CPU or was waiting for an event that didn’t belong to the Idle wait class. Refer to the V$EVENT_NAME view for more information on wait classes.

This view contains one row for each active session per sample and returns the latest session sample rows first. A majority of the columns describing the session in the active session history are present in the V$SESSION view.”

Still not seeing the problem?  If we believe the documentation, assume that we are only interested in a 10 second time interval, and one of the sessions started waiting on a wait event at the start of the time interval, and was still waiting in the same wait event at the end of the 10 second time interval.  SUM(A.WAIT_TIME + A.TIME_WAITED) for the session (assuming that rounding does not happen due to view update frequency) would show 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 = 55 seconds of total wait time for the result of the SUM function.  Not too bad, right?  Now, assume that the session had already waited 240 seconds and continued to wait for an additional 10 seconds – the SUM(A.WAIT_TIME + A.TIME_WAITED) for the session would show that the session waited 240 + 241 + 242 + 243 + 244 + 245 + 246 + 247 + 248 + 249 + 250 = 2695 seconds (44.92 minutes) in that 10 second time interval.

—

A fun little test that will require two sessions.  In Session 1:

CREATE TABLE T1 (
  C1 NUMBER,
  PRIMARY KEY(C1));

INSERT INTO
  T1
VALUES (
  1); 

Now, in Session 1, determine the SID:

COLUMN SID FORMAT 999

SELECT
  SID
FROM
  V$MYSTAT
WHERE
  ROWNUM=1;

 SID
----
 189 

In Session 2, determine the SID:

COLUMN SID FORMAT 999

SELECT
  SID
FROM
  V$MYSTAT
WHERE
  ROWNUM=1;

 SID
----
   4 

Back in Session 1, insert a row into the table:

INSERT INTO
  T1
VALUES (
  1); 

In Session 2, attempt to insert the same row into the table (a primary key violation if the first session commits):

INSERT INTO
  T1
VALUES (
  1); 

Session 2 is now hung.  Back in Session 1, wait 20 minutes and check AWR ASH using a slightly fixed version of the query (mainly the table aliases are corrected, and adjusted to examine only the last 10 seconds of entries in V$ACTIVE_SESSION_HISTORY for the two sessions of interest):

SELECT
  A.CURRENT_OBJ#,
  D.OBJECT_NAME,
  D.OBJECT_TYPE,
  A.EVENT,
  SUM(A.WAIT_TIME + A.TIME_WAITED) TOTAL_WAIT_TIME
FROM
  V$ACTIVE_SESSION_HISTORY A,
  DBA_OBJECTS D
WHERE
  A.SAMPLE_TIME BETWEEN SYSDATE – 10/(24*60*60) AND SYSDATE
  AND A.CURRENT_OBJ# = D.OBJECT_ID
  AND A.SESSION_ID IN (4,189)
GROUP BY
  A.CURRENT_OBJ#,
  D.OBJECT_NAME,
  D.OBJECT_TYPE,
  A.EVENT
ORDER BY
  TOTAL_WAIT_TIME; 

no rows selected

That can’t be the expected result.  Let’s try running the following query in Microsoft Excel:

SELECT
  SESSION_ID,
  EVENT,
  CURRENT_OBJ#,
  SAMPLE_TIME,
  WAIT_TIME,
  TIME_WAITED
FROM
  V$ACTIVE_SESSION_HISTORY
WHERE
  SESSION_ID IN (4,189)
ORDER BY
  SESSION_ID,
  SAMPLE_TIME; 

After scrolling down a couple of thousand lines in the spreadsheet:

Well, that throws off the results of the query found in the book a bit.  The CURRENT_OBJ# is -1 and the session has not waited any time (SUM(A.WAIT_TIME + A.TIME_WAITED) = 0 for SID 4, but would be 116,972,415 for SID 189 for the same CURRENT_OBJ# = -1). 

Maybe the query found in the book just does not work for potential primary key violations?  What about a case where one session updates a row and another session attempts to update the same row?  Issue a COMMIT in Session 1 and Session 2.  In Session 1:

COMMIT;

UPDATE
  T1
SET
  C1=C1; 

In Session 2, attempt to update the same row:

UPDATE
  T1
SET
  C1=C1; 

Session 2 is hung while waiting for Session 1 to COMMIT or ROLLBACK.  Now wait for a while.  While you are waiting, let’s take a quick look at the documentation for the V$ACTIVE_SESSION_HISTORY view from the 11.2 Oracle Documentation library:

“TIME_WAITED: If SESSION_STATE = WAITING, then the time that the session actually spent waiting for that event (in hundredths of a second). This column is set for waits that were in progress at the time the sample was taken.

If a wait event lasted for more than a second and was caught waiting in more than one session sample row, then the actual time spent waiting for that wait event will be populated in the last of those session sample rows.”

OK, must check the SESSION_STATE column, only the last of those sample rows in a string of rows for the same wait event, and the time is in 1/100th (0.01) of a second.  Let’s re-run the query in Excel to see the progress:

That 2,471,728,088 number in the TIME_WAITED column appears to be in conflict with the description in the Oracle Documentation library – the time appears to be reported in 1/1,000,000 (0.000001) of a second, not 1/100 of a second.

—

Back to the original topic of this article.  I have yet to determine why the query subtracts 30/2880 from the SYSDATE to retrieve the activity in the last 15 minutes – wouldn’t it be more clear to subtract 15 / (24 * 60) or 15 / 1440?  Considering that the SAMPLE_TIME column in V$ACTIVE_SESSION_HISTORY is a TIMESTAMP datatype, would it make more sense to use SYSTIMESTAMP rather than SYSDATE?

Finally, What is Wrong with the Quote Found in the Book?

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true. It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past). If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.

Give Me a Hint – How were These Autotrace Execution Statistics Achieved?

June 27, 2011

I recently received an email asking why different performance is achieved when a FIRST_ROWS hint, FIRST_ROWS(100) hint, and an unhinted version of the query are executed.  This seems to be a simple problem, yet it might also be an interesting problem.  I thought that it might be helpful to transform my response into a blog article (allowing the input of readers of this blog), rather than provide an email response (sorry, I typically do not respond to email requests).  What if (flushing the buffer cache between executions with a single execution of ALTER SYSTEM FLUSH BUFFER_CACHE;):

• The unhinted version of the query completes 2 seconds faster than the FIRST_ROWS(100) hinted version of the query?
• The FIRST_ROWS hinted version of the query completes 22 seconds faster than the unhinted version of the query (thus the FIRST_ROWS hinted version completes 24 seconds faster than then FIRST_ROWS(100) hinted version of the query).
• According to the AUTOTRACE output, the execution plans for the FIRST_ROWS hinted version and the FIRST_ROWS(100) hinted version of the query both perform a descending index range scan on the primary key column for the one table (the only data source) in the query.
• According to the AUTOTRACE output, the unhinted version of the query shows that a full table scan was performed.
• While the FIRST_ROWS(100) hinted execution required longer to complete than the FIRST_ROWS hinted execution, the FIRST_ROWS(100) hinted execution performed 69% as many physical block reads as the FIRST_ROWS hinted execution, while exactly the same number of consistent gets were performed in both cases.
• The queries retrieved 1,042,684 rows with an array fetch size of 15.
• For the FIRST_ROWS(100) and FIRST_ROWS hinted versions of the query, roughly 263.97MB was returned to the client, while only about 163.62MB (62% of the hinted version) was returned in the unhinted version of the query.

The question is how would one explain the results?  Here is a slightly modified version of the query, with just the table and column names replaced:

SELECT
  C1,
  C2,
  C3,
  C4,
  C5,
  C6,
  C7
FROM
  T1      
WHERE
  C1 < 1042685
ORDER BY
  C1 DESC; 

In the above, note that C1 must be the primary key column… unless of course there are multiple columns in the primary key (or the index displayed in the execution plans really is not the primary key index).

The order of the query executions was not specified, but let’s assume that the OP listed the executions in the same order as executed.

The unhinted execution:

Elapsed: 00:00:53.00

----------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |  1042K|   255M|       |   120K  (1)| 00:24:03 |
|   1 |  SORT ORDER BY     |           |  1042K|   255M|   313M|   120K  (1)| 00:24:03 |
|*  2 |   TABLE ACCESS FULL| T1        |  1042K|   255M|       | 62490   (1)| 00:12:30 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("C1"<1042685)

Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
     244502  consistent gets
     244458  physical reads
       2836  redo size
  171565915  bytes sent via SQL*Net to client
     765155  bytes received via SQL*Net from client
      69514  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    1042684  rows processed 

The FIRST_ROWS hinted execution:

Elapsed: 00:00:31.11

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |  1042K|   255M|   189K  (1)| 00:37:51 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T1           |  1042K|   255M|   189K  (1)| 00:37:51 |
|*  2 |   INDEX RANGE SCAN DESCENDING| PK_T1        |  1042K|       |  2136   (1)| 00:00:26 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"<1042685)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     345027  consistent gets
      38750  physical reads
          0  redo size
  276788545  bytes sent via SQL*Net to client
     765156  bytes received via SQL*Net from client
      69514  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1042684  rows processed

The FIRST_ROWS(100) hinted execution:

Elapsed: 00:00:55.69

Execution Plan
----------------------------------------------------------
Plan hash value: 2367722674
---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |   101 | 25957 |    22   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T1           |  1042K|   255M|    22   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN DESCENDING| PK_T1        |   101 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C1"<1042685)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     345027  consistent gets
      26788  physical reads
          0  redo size
  276788545  bytes sent via SQL*Net to client
     765156  bytes received via SQL*Net from client
      69514  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1042684  rows processed 

There are a number of items that we do not know about the OP’s configuration.  Just a couple of random thoughts:

• How might we explain why only 62% as many bytes were transferred over the network in the unhinted version of the query?  Could it be a case where SQL*Net compression has helped to strip the repeating column values from adjacent rows in the result set prior to transmission over the network.  Could it be the case that the rows were ordered differently if column C1 were the only column in the primary key?  What if there were multiple columns in the primary key – could that make a difference (note that the C1 < 1042685 predicate in the WHERE clause combined with the 1,042,684 rows returned suggests that column C1 might be the only column in the primary key)?  Does SQL*Net compression not work when rows are retrieved by an index access path to avoid a sort operation?  This portion of the result is a bit interesting.
• How might we explain the number 2836 next to redo size in the unhinted execution?  Might this be a case of Delayed Block Cleanout?  Could that number also partially explain the execution time for that version of the query?
• How might we explain the decrease in the number of physical block reads in the FIRST_ROWS(100) hinted execution compared to the FIRST_ROWS hinted execution?  Assuming that the executions were performed in the above order, what if the ALTER SYSTEM FLUSH BUFFER_CACHE; command did not actually flush all of the blocks from the buffer cache – is that possible?  Could undo blocks for a consistent read have an impact?
• The operations displayed in the execution plans for the two hinted queries are identical, yet the execution times are significantly different.  How might we explain the time difference?
• — Do we know for certain that the runtime execution engine used the execution plan that was displayed?
• — Is it possible that one of the queries was executed with SET AUTOTRACE ON specified in SQL*Plus while the fast execution was performed with SET AUTOTRACE ON STATISTICS specified in SQL*Plus (thus entirely avoiding the client-side formatting of the output)?
• — Is it possible that SET AUTOTRACE ON was specified in both cases, yet the SQL*Plus window was minimized during the FIRST_ROWS hinted execution (thus avoiding the client-side screen refreshes)?
• — Is it possible that there was less network congestion during the FIRST_ROWS hinted execution?
• — Is it possible that the client computer’s CPU (or the server’s CPU) was less busy with other tasks during the FIRST_ROWS hinted execution?
• — Is it possible that operating system caching is having an effect – if the entire execution time was spent in single block read wait events, the average single block read time for the FIRST_ROWS hinted execution was roughly 0.00080 seconds, while the average single block read time for the FIRST_ROWS(100) hinted execution was roughly 0.00208 seconds.
• — Note that the AUTOTRACE output indicates that one recursive call was performed per execution – is it possible that the time difference and the difference in the number of physical block reads could be attributed to the recursive call?

What other items might explain the time difference in the three executions?  What advice would you provide to the OP?  Should we answer the original question using 10 more questions?

Addressing TM Enqueue Contention – What is Wrong with this Quote?

June 19, 2011

Currently, the most viewed article in the past 90 days on this blog demonstrates a deadlock situation that is present in Oracle Database 11.1.0.6 and above that is not present in Oracle Database 10.2.0.1 through 10.2.0.5 (someone else might be able to check the earlier releases).  This particular deadlock was the result of a missing foreign key index, which resulted in TM enqueues and an eventual deadlock when the first of three sessions issued a COMMIT.  From this bit of information, hopefully it is clear that foreign key columns should usually be indexed (especially if the primary/unique column in in the parent table is subject to UPDATE statements).

While reading the alpha edition of the “Oracle Database 11g Performance Tuning Recipes” book, I noticed a test case in Recipe 5-15 that is intended to demonstrate how an index on a foreign key’s column(s) helps to resolve the most common cause of TM enqueues.  While the test case included the DDL to create a parent and child table, it did not include the DML to create test data.  The DDL for the two tables looks something like the following:

CREATE TABLE STORES (
  STORE_ID NUMBER(10) NOT NULL,
  SUPPLIER_NAME VARCHAR2(40) NOT NULL,
  CONSTRAINT STORES_PK PRIMARY KEY (STORE_ID));

CREATE TABLE PRODUCTS (
  PRODUCT_ID NUMBER(10) NOT NULL,
  PRODUCT_NAME VARCHAR2(30) NOT NULL,
  SUPPLIER_ID NUMBER(10) NOT NULL,
  STORE_ID NUMBER(10) NOT NULL,
  CONSTRAINT FK_STORES FOREIGN KEY (STORE_ID) REFERENCES STORES(STORE_ID) ON DELETE CASCADE); 

Immediately after the above DDL statements appears the following claim is made by the alpha copy of the book:

“If you now delete any rows in the STORES table, you’ll notice waits due to locking.”

Interesting observation… I hope that the final copy of the book includes the DML to populate those tables.  In the mean time, let’s create our own test data to see if the claim made in the quote is correct:

INSERT INTO
  STORES
SELECT
  ROWNUM STORE_ID,
  'SUPPLIER NAME '||TO_CHAR(ROWNUM) SUPPLIER_NAME
FROM
  DUAL
CONNECT BY
  LEVEL<=10;

INSERT INTO
  PRODUCTS
SELECT
  ROWNUM PRODUCT_ID,
  'PRODUCT NAME '||TO_CHAR(ROWNUM) PRODUCT_NAME,
  TRUNC((ROWNUM-1)/10)+100 SUPPLIER_ID,
  TRUNC((ROWNUM-1)/10)+1 STORE_ID
FROM
  DUAL
CONNECT BY
  LEVEL<=100;

COMMIT; 

We now have 10 rows in the parent table and 100 rows in the child table (side note: is there something strange about the names of the columns in the tables?).  Let’s connect to the database using two sessions.  In Session 1, execute the following to delete a row from the STORES table:

DELETE FROM
  STORES
WHERE
  STORE_ID=1; 

Here is the challenge, what happens if we attempt to execute the following script in Session 2 (the values to be inserted should not conflict with existing rows in the tables):

INSERT INTO
  STORES
SELECT
  ROWNUM+10 STORE_ID,
  'SUPPLIER NAME '||TO_CHAR(ROWNUM+10) SUPPLIER_NAME
FROM
  DUAL
CONNECT BY
  LEVEL<=10;

INSERT INTO
  PRODUCTS
SELECT
  ROWNUM PRODUCT_ID,
  'PRODUCT NAME '||TO_CHAR(ROWNUM+100) PRODUCT_NAME,
  TRUNC((ROWNUM+100-1)/10)+100 SUPPLIER_ID,
  TRUNC((ROWNUM+100-1)/10)+1 STORE_ID
FROM
  DUAL
CONNECT BY
  LEVEL<=100; 

Which of the following will happen?

a. Session 2 will be blocked by session 1, with session 2 waiting in the “enq: TM – contention” wait event when it attempts to execute the first of the two SQL statements.

b. Session 2 will be blocked by session 1, with session 2 waiting in the “enq: TM – contention” wait event when it attempts to execute the second of the two SQL statements.

c. Session 2 will receive the following error message “ERROR at line 1: ORA-02291: integrity constraint (TESTUSER.FK_STORES) violated – parent key not found”.

d. Session 2 will corrupt the database due to the missed TM enqueue.

e. Session 2 will be waiting in the “SQL*Net message from client” wait event after it attempts to execute the second of the two SQL statements.

—–

Think about the above for a moment before scrolling down.  Does the Oracle release version make a difference?

–

–

–

–

–

–

–

–

–

–

–

–

–

–

–

–

–

–

–

–

–

Now that you have thought about the question for a minute or two, take a look at the output I received on Oracle Database 11.2.0.2:

SQL> INSERT INTO
  2    STORES
  3  SELECT
  4    ROWNUM+10 STORE_ID,
  5    'SUPPLIER NAME '||TO_CHAR(ROWNUM+10) SUPPLIER_NAME
  6  FROM
  7    DUAL
  8  CONNECT BY
  9    LEVEL<=10;

10 rows created.

SQL> INSERT INTO
  2    PRODUCTS
  3  SELECT
  4    ROWNUM PRODUCT_ID,
  5    'PRODUCT NAME '||TO_CHAR(ROWNUM+100) PRODUCT_NAME,
  6    TRUNC((ROWNUM+100-1)/10)+100 SUPPLIER_ID,
  7    TRUNC((ROWNUM+100-1)/10)+1 STORE_ID
  8  FROM
  9    DUAL
 10  CONNECT BY
 11    LEVEL<=100;

100 rows created. 

So, the answer to the multiple choice question is…

Wait… we are not done yet.  Back in Session 2 execute the following which will delete a previously committed row (Session 1 previously deleted the row with STORE_ID=1, but has yet to COMMIT):

DELETE FROM
  STORES
WHERE
  STORE_ID=10; 

The output I received on Oracle Database 11.2.0.2:

SQL> DELETE FROM
  2    STORES
  3  WHERE
  4    STORE_ID=10;

(session is hung - reporting wait event "enq: TM - contention") 

So, the answer to the multiple choice question is…  Wait, does the Oracle Database release version matter?

—

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true. It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past). If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.

Calculate the Distance Between Two Latitude/Longitude Points using Plain SQL

June 13, 2011 (Modified June 14, 2011)

A question recently appeared on the comp.databases.oracle.server Usenet group that oddly made me recall a lesson from a mathematics class that I taught in the early 1990s.  A bit strange how a question related to Oracle Database would trigger such a memory, but it happened.  The question posed in the thread initially asked how to calculate the distance between the points:

 item  latitude  longitude
---- ---------  ---------
P1   50.716667  -1.883333
P2   51.023332  -1.872231

Let’s see, the earth is flat, so…

= ((51.023332 - 50.716667)^2 + (1.883333 - 1.872231)^2)^(1/2) * 3960 * 3.141592/180 miles
= (0.094043422225 + 0.000123254404)^(1/2) * 3960 * 3.141592/180 miles
= 0.30686589355775594241245361976998 * 3960 * 3.141592/180 miles
= 1215.18894 * 3.141592/180 miles
= 21.209 miles

So, if the earth is flat, the Pythagorean Theorem seems to give a sensible answer… maybe.  My recollection of the formulas used for the correct calculation is a bit fuzzy.  Good news, I found the three page lesson plan that I put together in the early 1990s to solve a problem similar to the one posed by the OP in the Usenet thread.  The lesson plan begins by stating that part of the point of the lesson was to destroy Euclid’s Elements, which were introduced 23 centuries ago… a bit ambitious, looking back.  Even though the lesson plan is not too terribly clear given the amount of mathematics knowledge that I have forgotten, I managed to find the following formula in the lesson plan:

c0 = cos^(-1)(cos(90-alpha1)*cos(90-alpha2) + sin(90-alpha1)*sin(90-alpha2)*cos(theta1-theta2))
distance = c0 * (2pi/360) * 3960 miles

That looks complicated, even without the help of a computer… what is alpha, and what is theta – it’s Greek to me?

A Google search found another helpful page that describes how to calculate distances between pairs of latitude and longitude coordinates.  Included in that page is the following formula that is compatible with Microsoft Excel:

=ACOS(SIN(lat1)*SIN(lat2)+COS(lat1)*COS(lat2)*COS(lon2-lon1))*6371 KM 

Other than outputting kilometers rather than miles, and using SIN where my formula uses COS, the two formulas are close to being identical.  Based on that observation, alpha must be the latitude coordinate and theta must be the longitude coordinate in the formula that I found in my lesson plan.

The lesson plan included a couple of sample questions and answers:

Prague (14 degrees 26 minutes east, 50 degrees 5 minutes north)
Rio de Janeiro (43 degrees 12 minutes west, 22 degrees 57 minutes south)
Distance = 6152 miles

—

(119 19 degrees 48 minutes west, 36 degrees 44 minutes north)
(88 degrees 30 minutes west, 42 degrees south)
Distance = 5789.38 4832.09 miles

Will either of the two formulas, when plugged into an Oracle Database, output the distances that I wrote down roughly two decades ago?  That sounds like a challenge.

The setup (make certain that you do not swap the longitude and latitude numbers, the results will be slightly different):

DROP TABLE T1 PURGE;

CREATE TABLE T1 (
  LOCATION VARCHAR2(40),
  LAT_RAD NUMBER,
  LON_RAD NUMBER,
  LAT_DEG NUMBER,
  LON_DEG NUMBER,
  PRIMARY KEY(LOCATION));

INSERT INTO T1 VALUES (
  'Prague',
  ROUND((-(50 + 5/60))*3.141592654/180,6),
  ROUND((14 + 26/60)*3.141592654/180,6),
  ROUND(-(50 + 5/60),6),
  ROUND(14 + 26/60,6)
  );

INSERT INTO T1 VALUES (
  'Rio de Janeiro',
  ROUND((22 + 57/60)*3.141592654/180,6),
  ROUND((-(43 + 12/60))*3.141592654/180,6),
  ROUND(22 + 57/60,6),
  ROUND(-(43 + 12/60),6)
  );

COMMIT; 

Note in the above that I had to convert the degrees and minutes notation to decimal numbers and then also to equivalent radian values.  It would probably be best just to store the radian values, because those values can be used directly in the calculations.  Let’s take a look at the contents of this table:

COLUMN LAT_RAD FORMAT 99.999999
COLUMN LON_RAD FORMAT 99.999999
COLUMN LAT_DEG FORMAT 999.999999
COLUMN LON_DEG FORMAT 999.999999
COLUMN LOCATION FORMAT A20
SET LINESIZE 140
SET TRIMSPOOL ON
SET PAGESIZE 1000

SELECT
  *
FROM
  T1;

LOCATION                LAT_RAD    LON_RAD     LAT_DEG     LON_DEG
-------------------- ---------- ---------- ----------- -----------
Prague                 -.874119    .251909  -50.083333   14.433333
Rio de Janeiro          .400553   -.753982   22.950000  -43.200000 

As seen in the above, west and north directional values are assigned negative numbers.

Let’s try converting the Excel version of the formula to Oracle Database SQL:

COLUMN DISTANCE FORMAT 999990.00

SELECT
  ABS(ACOS(SIN(LOC1.LAT_RAD)*SIN(LOC2.LAT_RAD)+COS(LOC1.LAT_RAD)*COS(LOC2.LAT_RAD)*COS(LOC2.LON_RAD-LOC1.LON_RAD)))* 3960 DISTANCE
FROM
  (SELECT
    LAT_RAD,
    LON_RAD
  FROM
    T1
  WHERE
    LOCATION='Prague') LOC1,
  (SELECT
    LAT_RAD,
    LON_RAD
  FROM
    T1
  WHERE
    LOCATION='Rio de Janeiro') LOC2;

  DISTANCE
----------
   6152.02 

That number seems to match the number that I wrote into the lesson plan.  Let’s try the formula from the lesson plan:

SELECT
  ACOS(COS(1.570796327-LOC1.LAT_RAD)*COS(1.570796327-LOC2.LAT_RAD) + SIN(1.570796327-LOC1.LAT_RAD)*SIN(1.570796327-LOC2.LAT_RAD)*COS(LOC1.LON_RAD-LOC2.LON_RAD)) * 3960 DISTANCE
FROM
  (SELECT
    LAT_RAD,
    LON_RAD
  FROM
    T1
  WHERE
    LOCATION='Prague') LOC1,
  (SELECT
    LAT_RAD,
    LON_RAD
  FROM
    T1
  WHERE
    LOCATION='Rio de Janeiro') LOC2;

  DISTANCE
----------
   6152.02 

If only I had access to an Oracle Database’s SQL interpretter back then rather than a fancy scientific calculator… 🙂

Let’s try to calculate the distance for the latitude and longitude values provided by the OP (we need to convert the degree values to radians):

SELECT
  ACOS(COS(1.570796327-LOC1.LAT_RAD)*COS(1.570796327-LOC2.LAT_RAD) + SIN(1.570796327-LOC1.LAT_RAD)*SIN(1.570796327-LOC2.LAT_RAD)*COS(LOC1.LON_RAD-LOC2.LON_RAD)) * 3960 DISTANCE
FROM
  (SELECT
    50.716667*3.141592/180 LAT_RAD,
    -1.883333*3.141592/180 LON_RAD
  FROM
    DUAL) LOC1,
  (SELECT
    51.023332*3.141592/180 LAT_RAD,
    -1.872231*3.141592/180 LON_RAD
  FROM
    DUAL) LOC2;

  DISTANCE
----------
21.20 

You may notice that for the short distance between the latitude/longitude pairs that the “earth is flat” method produced the same answer that we see with the more complicated formula shown above.

For a double-check, let’s put the second set of demonstration points from my lesson plan into the T1 table:

INSERT INTO T1 VALUES (
  'TEST P1',
  NULL,
  NULL,
  19 + 48/60,     ---- 119 + 48/60,
  -(36 + 44/60)
  );

INSERT INTO T1 VALUES (
  'TEST P2',
  NULL,
  NULL,
  88 + 30/60,
  42
  );

UPDATE
  T1
SET
  LAT_RAD=ROUND(LAT_DEG/180*3.141592654,6),
  LON_RAD=ROUND(LON_DEG/180*3.141592654,6),
  LAT_DEG=ROUND(LAT_DEG,6),
  LON_DEG=ROUND(LON_DEG,6)
WHERE
  LAT_RAD IS NULL;

COMMIT;

Checking the distance with the two formulas:

SELECT
  ABS(ACOS(SIN(LOC1.LAT_RAD)*SIN(LOC2.LAT_RAD)+COS(LOC1.LAT_RAD)*COS(LOC2.LAT_RAD)*COS(LOC2.LON_RAD-LOC1.LON_RAD)))* 3960 DISTANCE
FROM
  (SELECT
    LAT_RAD,
    LON_RAD
  FROM
    T1
  WHERE
    LOCATION='TEST P1') LOC1,
  (SELECT
    LAT_RAD,
    LON_RAD
  FROM
    T1
  WHERE
    LOCATION='TEST P2') LOC2;

  DISTANCE
----------
   4832.09  ---5789.07--- 

 —

 SELECT
  ACOS(COS(1.570796327-LOC1.LAT_RAD)*COS(1.570796327-LOC2.LAT_RAD) + SIN(1.570796327-LOC1.LAT_RAD)*SIN(1.570796327-LOC2.LAT_RAD)*COS(LOC1.LON_RAD-LOC2.LON_RAD)) * 3960 DISTANCE
FROM
  (SELECT
    LAT_RAD,
    LON_RAD
  FROM
    T1
  WHERE
    LOCATION='TEST P1') LOC1,
  (SELECT
    LAT_RAD,
    LON_RAD
  FROM
    T1
  WHERE
    LOCATION='TEST P2') LOC2;

  DISTANCE
----------
   4832.09  ---5789.07---

The values returned are identical to the value I wrote into the lesson plan, so the formulas are probably correct.

The OP’s second question is to identify all locations that are within a given radius of a specified location.  This might be easy, or it might not.  Someone in the Usenet thread suggested using Oracle Spatial, which is an additional cost licensed item that may be added to the Enterprise Edition for a list price of $17,500 per CPU plus $3,850 (USD) annual maintenance.  As I am not familiar with the product, I do not know if Oracle Spatial could answer this question (does someone know for certain?). 

We need some more test data to see if we can find a solution for the OP.  We will use the SIN and COS functions just to insert some “random” yet repeatable data:

INSERT INTO
  T1
SELECT
  'POINT '||TO_CHAR(ROWNUM),
  NULL,
  NULL,
  COS(ROWNUM/40)*180,
  SIN(ROWNUM/33)*180
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

UPDATE
  T1
SET
  LAT_RAD=ROUND(LAT_DEG/180*3.141592654,6),
  LON_RAD=ROUND(LON_DEG/180*3.141592654,6),
  LAT_DEG=ROUND(LAT_DEG,6),
  LON_DEG=ROUND(LON_DEG,6)
WHERE
  LAT_RAD IS NULL;

COMMIT;

EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER, TABNAME=>'T1', CASCADE=> TRUE) 

Let’s give the first SQL statement a try, looking for those areas that are less than 500 miles away from “TEST P1”:

SELECT
  LOC2.LOCATION,
  ABS(ACOS(SIN(LOC1.LAT_RAD)*SIN(LOC2.LAT_RAD)+COS(LOC1.LAT_RAD)*COS(LOC2.LAT_RAD)*COS(LOC2.LON_RAD-LOC1.LON_RAD)))* 3960 DISTANCE
FROM
  (SELECT
    LAT_RAD,
    LON_RAD
  FROM
    T1
  WHERE
    LOCATION='TEST P1') LOC1,
  (SELECT
    LOCATION,
    LAT_RAD,
    LON_RAD
  FROM
    T1
  WHERE
    LOCATION<>'TEST P1') LOC2
WHERE
  (ABS(ACOS(SIN(LOC1.LAT_RAD)*SIN(LOC2.LAT_RAD)+COS(LOC1.LAT_RAD)*COS(LOC2.LAT_RAD)*COS(LOC2.LON_RAD-LOC1.LON_RAD)))* 3960) < 500
ORDER BY
  LOC2.LOCATION; 

LOCATION               DISTANCE
-------------------- ----------
POINT 1561               331.91
POINT 1562                46.45
POINT 1563               426.22
POINT 200                439.78
POINT 2066               318.73
POINT 2067               387.62
POINT 4970               432.93
POINT 7989               210.35
POINT 7990               268.25
POINT 8494               443.05
POINT 9855               258.38
POINT 9856               120.22
Rio de Janeiro           469.61

13 rows selected. 

LOCATION               DISTANCE -------------------- ---------- POINT 1358               428.75 POINT 1359               487.10 POINT 402                452.35 POINT 403                124.05 POINT 404                247.85 POINT 4675               452.49 POINT 4676               168.24 POINT 4677               306.37 POINT 4847               405.92 POINT 5206               449.08 POINT 5207               160.17 POINT 5208               367.93 POINT 5378               496.50 POINT 5379               341.63 POINT 5380               453.77 POINT 6714               282.04 POINT 6715                96.74 POINT 6716               484.03 POINT 8696               386.01 POINT 8697                77.56 POINT 8698               312.19 POINT 9652               420.05 22 rows selected. 

And the second SQL statement:

SELECT
  LOC2.LOCATION,
  ACOS(COS(1.570796327-LOC1.LAT_RAD)*COS(1.570796327-LOC2.LAT_RAD) + SIN(1.570796327-LOC1.LAT_RAD)*SIN(1.570796327-LOC2.LAT_RAD)*COS(LOC1.LON_RAD-LOC2.LON_RAD)) * 3960 DISTANCE
FROM
  (SELECT
    LAT_RAD,
    LON_RAD
  FROM
    T1
  WHERE
    LOCATION='TEST P1') LOC1,
  (SELECT
    LOCATION,
    LAT_RAD,
    LON_RAD
  FROM
    T1
  WHERE
    LOCATION<>'TEST P1') LOC2
WHERE
  (ABS(ACOS(SIN(LOC1.LAT_RAD)*SIN(LOC2.LAT_RAD)+COS(LOC1.LAT_RAD)*COS(LOC2.LAT_RAD)*COS(LOC2.LON_RAD-LOC1.LON_RAD)))* 3960) < 500
ORDER BY
  LOC2.LOCATION;

LOCATION               DISTANCE
-------------------- ----------
POINT 1561               331.91
POINT 1562                46.45
POINT 1563               426.22
POINT 200                439.78
POINT 2066               318.73
POINT 2067               387.62
POINT 4970               432.93
POINT 7989               210.35
POINT 7990               268.25
POINT 8494               443.05
POINT 9855               258.38
POINT 9856               120.22
Rio de Janeiro           469.61
 
13 rows selected.

LOCATION               DISTANCE -------------------- ---------- POINT 1358               428.75 POINT 1359               487.10 POINT 402                452.35 POINT 403                124.05 POINT 404                247.85 POINT 4675               452.49 POINT 4676               168.24 POINT 4677               306.37 POINT 4847               405.92 POINT 5206               449.08 POINT 5207               160.17 POINT 5208               367.93 POINT 5378               496.50 POINT 5379               341.63 POINT 5380               453.77 POINT 6714               282.04 POINT 6715                96.74 POINT 6716               484.03 POINT 8696               386.01 POINT 8697                77.56 POINT 8698               312.19 POINT 9652               420.05 22 rows selected. 

If you tried either of the above two SQL statements, you probably found that it took a couple of seconds to make it through the 10,004 or so rows in the table.  All of the calculations are likely hammering the server’s CPU (go parallel and hammer more than one CPU?).

Maybe we should try submitting bind variables in place of some of the calculations that we are requesting for the server to perform?  Let’s set up the bind variables to prepare another test:

SELECT
  SIN(LAT_RAD),
  COS(LAT_RAD),
  SIN(LON_RAD),
  COS(LON_RAD)
FROM
  T1
WHERE
  LOCATION='TEST P1'; 

SIN(LAT_RAD) COS(LAT_RAD) SIN(LON_RAD) COS(LON_RAD)
------------ ------------ ------------ ------------
   .33873774   .940880834   -.59809181   .801427592

SIN(LAT_RAD) COS(LAT_RAD) SIN(LON_RAD) COS(LON_RAD) ------------ ------------ ------------ ------------   -.59809181   .801427592   .867765674   -.49697358 VAR SIN1_LAT_RAD NUMBER

VAR COS1_LAT_RAD NUMBER
VAR SIN1_LON_RAD NUMBER
VAR COS1_LON_RAD NUMBER

EXEC :SIN1_LAT_RAD := .33873774
EXEC :COS1_LAT_RAD := .940880834
EXEC :SIN1_LON_RAD := -.59809181
EXEC :COS1_LON_RAD := .801427592

EXEC :SIN1_LAT_RAD := -.59809181 EXEC :COS1_LAT_RAD := .801427592 EXEC :SIN1_LON_RAD := .867765674 EXEC :COS1_LON_RAD := -.49697358 

To test the performance, execute the following scipt (more than once to eliminate the hard parse):

SET ARRAYSIZE 100
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SQL_10046';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

SELECT
  LOC2.LOCATION,
  ABS(ACOS(SIN(LOC1.LAT_RAD)*SIN(LOC2.LAT_RAD)+COS(LOC1.LAT_RAD)*COS(LOC2.LAT_RAD)*COS(LOC2.LON_RAD-LOC1.LON_RAD)))* 3960 DISTANCE
FROM
  (SELECT
    LAT_RAD,
    LON_RAD
  FROM
    T1
  WHERE
    LOCATION='TEST P1') LOC1,
  (SELECT
    LOCATION,
    LAT_RAD,
    LON_RAD
  FROM
    T1
  WHERE
    LOCATION<>'TEST P1') LOC2
WHERE
  (ABS(ACOS(SIN(LOC1.LAT_RAD)*SIN(LOC2.LAT_RAD)+COS(LOC1.LAT_RAD)*COS(LOC2.LAT_RAD)*COS(LOC2.LON_RAD-LOC1.LON_RAD)))* 3960) < 500
ORDER BY
  LOC2.LOCATION;

SELECT
  LOC2.LOCATION,
  ACOS(COS(1.570796327-LOC1.LAT_RAD)*COS(1.570796327-LOC2.LAT_RAD) + SIN(1.570796327-LOC1.LAT_RAD)*SIN(1.570796327-LOC2.LAT_RAD)*COS(LOC1.LON_RAD-LOC2.LON_RAD)) * 3960 DISTANCE
FROM
  (SELECT
    LAT_RAD,
    LON_RAD
  FROM
    T1
  WHERE
    LOCATION='TEST P1') LOC1,
  (SELECT
    LOCATION,
    LAT_RAD,
    LON_RAD
  FROM
    T1
  WHERE
    LOCATION<>'TEST P1') LOC2
WHERE
  (ABS(ACOS(SIN(LOC1.LAT_RAD)*SIN(LOC2.LAT_RAD)+COS(LOC1.LAT_RAD)*COS(LOC2.LAT_RAD)*COS(LOC2.LON_RAD-LOC1.LON_RAD)))* 3960) < 500
ORDER BY
  LOC2.LOCATION;

SELECT
  LOC2.LOCATION,
  ABS(ACOS(:SIN1_LAT_RAD*SIN(LOC2.LAT_RAD)+:COS1_LAT_RAD*COS(LOC2.LAT_RAD)*COS(LOC2.LON_RAD-LOC1.LON_RAD)))* 3960 DISTANCE
FROM
  (SELECT
    LAT_RAD,
    LON_RAD
  FROM
    T1
  WHERE
    LOCATION='TEST P1') LOC1,
  (SELECT
    LOCATION,
    LAT_RAD,
    LON_RAD
  FROM
    T1
  WHERE
    LOCATION<>'TEST P1') LOC2
WHERE
  (ABS(ACOS(:SIN1_LAT_RAD*SIN(LOC2.LAT_RAD)+:COS1_LAT_RAD*COS(LOC2.LAT_RAD)*COS(LOC2.LON_RAD-LOC1.LON_RAD)))* 3960) < 500
ORDER BY
  LOC2.LOCATION;

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'; 

Trace file output for the first query (1.794 CPU seconds):

FETCH #406890544:c=1794011,e=1797194,p=0,cr=94,cu=0,mis=0,r=1,dep=0,og=1,plh=2797247905,tim=2500817545497
WAIT #406890544: nam='SQL*Net message from client' ela= 357 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2500817545983
WAIT #406890544: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2500817546020
FETCH #406890544:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=21,dep=0,og=1,plh=2797247905,tim=2500817546058
STAT #406890544 id=1 cnt=22 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=94 pr=0 pw=0 time=1797194 us cost=13 size=25000 card=500)'
STAT #406890544 id=2 cnt=22 pid=1 pos=1 obj=0 op='NESTED LOOPS  (cr=94 pr=0 pw=0 time=125707 us cost=12 size=25000 card=500)'
STAT #406890544 id=3 cnt=1 pid=2 pos=1 obj=90695 op='TABLE ACCESS BY INDEX ROWID T1 (cr=3 pr=0 pw=0 time=23 us cost=2 size=25 card=1)'
STAT #406890544 id=4 cnt=1 pid=3 pos=1 obj=90696 op='INDEX UNIQUE SCAN SYS_C0038308 (cr=2 pr=0 pw=0 time=17 us cost=1 size=0 card=1)'
STAT #406890544 id=5 cnt=22 pid=2 pos=2 obj=90695 op='TABLE ACCESS FULL T1 (cr=91 pr=0 pw=0 time=125683 us cost=10 size=12500 card=500)'
WAIT #406890544: nam='SQL*Net message from client' ela= 4359 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2500817550490
CLOSE #406890544:c=0,e=14,dep=0,type=0,tim=2500817550613 

Trace file output for the second query (1.529 CPU seconds):

FETCH #406890544:c=1528810,e=1519062,p=0,cr=94,cu=0,mis=0,r=1,dep=0,og=1,plh=2797247905,tim=2500819083108
WAIT #406890544: nam='SQL*Net message from client' ela= 320 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2500819083487
WAIT #406890544: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2500819083524
FETCH #406890544:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=21,dep=0,og=1,plh=2797247905,tim=2500819083560
STAT #406890544 id=1 cnt=22 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=94 pr=0 pw=0 time=1519059 us cost=13 size=25000 card=500)'
STAT #406890544 id=2 cnt=22 pid=1 pos=1 obj=0 op='NESTED LOOPS  (cr=94 pr=0 pw=0 time=50452 us cost=12 size=25000 card=500)'
STAT #406890544 id=3 cnt=1 pid=2 pos=1 obj=90695 op='TABLE ACCESS BY INDEX ROWID T1 (cr=3 pr=0 pw=0 time=15 us cost=2 size=25 card=1)'
STAT #406890544 id=4 cnt=1 pid=3 pos=1 obj=90696 op='INDEX UNIQUE SCAN SYS_C0038308 (cr=2 pr=0 pw=0 time=10 us cost=1 size=0 card=1)'
STAT #406890544 id=5 cnt=22 pid=2 pos=2 obj=90695 op='TABLE ACCESS FULL T1 (cr=91 pr=0 pw=0 time=50437 us cost=10 size=12500 card=500)'
WAIT #406890544: nam='SQL*Net message from client' ela= 3294 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2500819086974
CLOSE #406890544:c=0,e=13,dep=0,type=0,tim=2500819087016 

Trace file output for the third query (1.388 CPU seconds):

FETCH #406890544:c=1388409,e=1380995,p=0,cr=94,cu=0,mis=0,r=1,dep=0,og=1,plh=2797247905,tim=2500820478110
WAIT #406890544: nam='SQL*Net message from client' ela= 278 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2500820478441
WAIT #406890544: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2500820478480
FETCH #406890544:c=0,e=46,p=0,cr=0,cu=0,mis=0,r=21,dep=0,og=1,plh=2797247905,tim=2500820478519
STAT #406890544 id=1 cnt=22 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=94 pr=0 pw=0 time=1380991 us cost=13 size=21500 card=500)'
STAT #406890544 id=2 cnt=22 pid=1 pos=1 obj=0 op='NESTED LOOPS  (cr=94 pr=0 pw=0 time=45079 us cost=12 size=21500 card=500)'
STAT #406890544 id=3 cnt=1 pid=2 pos=1 obj=90695 op='TABLE ACCESS BY INDEX ROWID T1 (cr=3 pr=0 pw=0 time=12 us cost=2 size=18 card=1)'
STAT #406890544 id=4 cnt=1 pid=3 pos=1 obj=90696 op='INDEX UNIQUE SCAN SYS_C0038308 (cr=2 pr=0 pw=0 time=9 us cost=1 size=0 card=1)'
STAT #406890544 id=5 cnt=22 pid=2 pos=2 obj=90695 op='TABLE ACCESS FULL T1 (cr=91 pr=0 pw=0 time=45046 us cost=10 size=12500 card=500)'
WAIT #406890544: nam='SQL*Net message from client' ela= 2713 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2500820481326
CLOSE #406890544:c=0,e=15,dep=0,type=0,tim=2500820481372 

Maybe we should just assume that the world is flat to save time (note that I am not converting the degree values to radians)?

SELECT
  LOC2.LOCATION,
  SQRT(POWER((LOC1.LAT_DEG - LOC2.LAT_DEG),2) + POWER((LOC1.LON_DEG - LOC2.LON_DEG),2)) * 3960 * 3.141592654/180 DISTANCE
FROM
  (SELECT
    LAT_DEG,
    LON_DEG
  FROM
    T1
  WHERE
    LOCATION='TEST P1') LOC1,
  (SELECT
    LOCATION,
    LAT_DEG,
    LON_DEG
  FROM
    T1
  WHERE
    LOCATION<>'TEST P1') LOC2
WHERE
  (SQRT(POWER((LOC1.LAT_DEG - LOC2.LAT_DEG),2) + POWER((LOC1.LON_DEG - LOC2.LON_DEG),2)) * 3960 * 3.141592654/180) < 500
ORDER BY
  LOC2.LOCATION;

LOCATION               DISTANCE
-------------------- ----------
POINT 1561               351.10
POINT 1562                49.17
POINT 1563               446.41
POINT 200                447.03
POINT 2066               327.96
POINT 2067               389.31
POINT 4970               436.30
POINT 7989               222.68
POINT 7990               274.47
POINT 8494               446.20
POINT 9855               272.91
POINT 9856               126.70
Rio de Janeiro           497.15

13 rows selected.
LOCATION               DISTANCE -------------------- ---------- POINT 4847           487.345998 POINT 5207           194.867475 POINT 5208           371.803354 POINT 6714           306.023772 POINT 6715            110.18648  5 rows selected.

Still 13 rows returned, but the distances are a bit off due to the curvature of the earth. What happened to the other 17 rows… did I make a mistake, or is the world not flat?

Note that the original version of this article made a critical mistake – latitude values cannot exceed 90 degrees.  While trying to implement my suggestion in a comment to use +-0.0002525254004999719 radians to speed up the query (note, still need to take into account the wrap-around for longitude values) I discovered inconsistent results.  This article is corrected to fix that mistake.

Added June 14, 2011:

The speed up version of the query:

SET ARRAYSIZE 100 ALTER SESSION SET TRACEFILE_IDENTIFIER = 'SQL_10046-FAST'; 
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';     
SELECT 
  LOC2.LOCATION, 
  ABS(ACOS(SIN(LOC1.LAT_RAD)*SIN(LOC2.LAT_RAD)+COS(LOC1.LAT_RAD)*COS(LOC2.LAT_RAD)*COS(LOC2.LON_RAD-LOC1.LON_RAD)))* 3960 DISTANCE 
FROM 
  (SELECT 
     LAT_RAD, 
     LON_RAD 
   FROM 
     T1 
   WHERE 
     LOCATION='TEST P1') LOC1, 
  (SELECT 
     LOCATION, 
     LAT_RAD, 
     LON_RAD 
   FROM 
     T1 
   WHERE 
     LOCATION<>'TEST P1') LOC2 
WHERE 
  LOC2.LAT_RAD BETWEEN LOC1.LAT_RAD-(0.0002525254004999719 * 500) AND LOC1.LAT_RAD+(0.0002525254004999719 * 500) 
  AND LOC2.LON_RAD BETWEEN LOC1.LON_RAD-(0.0002525254004999719 * 500) AND LOC1.LON_RAD+(0.0002525254004999719 * 500) 
  AND (ABS(ACOS(SIN(LOC1.LAT_RAD)*SIN(LOC2.LAT_RAD)+COS(LOC1.LAT_RAD)*COS(LOC2.LAT_RAD)*COS(LOC2.LON_RAD-LOC1.LON_RAD)))* 3960) < 500 
ORDER BY 
  LOC2.LOCATION; 

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'; 

The fetch required almost no CPU time, from the 10046 trace file:

PARSE #406878256:c=0,e=3157,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1311020123,tim=2586341558538 
EXEC #406878256:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1311020123,tim=2586341558744 
WAIT #406878256: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2586341558793 
FETCH #406878256:c=15600,e=7387,p=0,cr=94,cu=0,mis=0,r=1,dep=0,og=1,plh=1311020123,tim=2586341566207 
WAIT #406878256: nam='SQL*Net message from client' ela= 11647 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2586341577955 
WAIT #406878256: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2586341578017 
FETCH #406878256:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=12,dep=0,og=1,plh=1311020123,tim=2586341578052 
STAT #406878256 id=1 cnt=13 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=94 pr=0 pw=0 time=7387 us cost=13 size=1950 card=39)' 
STAT #406878256 id=2 cnt=13 pid=1 pos=1 obj=0 op='NESTED LOOPS  (cr=94 pr=0 pw=0 time=3920 us cost=12 size=1950 card=39)' 
STAT #406878256 id=3 cnt=1 pid=2 pos=1 obj=90785 op='TABLE ACCESS BY INDEX ROWID T1 (cr=3 pr=0 pw=0 time=23 us cost=2 size=25 card=1)' 
STAT #406878256 id=4 cnt=1 pid=3 pos=1 obj=90786 op='INDEX UNIQUE SCAN SYS_C0038321 (cr=2 pr=0 pw=0 time=15 us cost=1 size=0 card=1)' 
STAT #406878256 id=5 cnt=13 pid=2 pos=2 obj=90785 op='TABLE ACCESS FULL T1 (cr=91 pr=0 pw=0 time=3894 us cost=10 size=975 card=39)' 
WAIT #406878256: nam='SQL*Net message from client' ela= 7604 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2586341585780 
CLOSE #406878256:c=0,e=13,dep=0,type=0,tim=2586341585850 

The above is not a finished product, it is still necessary to take care of wrap-around values for the longitude coordinates.

Row Lock is Synonymous with TX Lock, and is a Lock on a Single Row of a Table – Verify the Documentation Challenge

June 10, 2011

I found an interesting quote in the Oracle Database documentation library:

“Row Locks (TX)
A row lock, also called a TX lock, is a lock on a single row of a table. A transaction acquires a row lock for each row modified by one of the following statements: INSERT, UPDATE, DELETE, MERGE, and SELECT … FOR UPDATE. The row lock exists until the transaction commits or rolls back.

When a transaction obtains a row lock for a row, the transaction also acquires a table lock for the table in which the row resides. The table lock prevents conflicting DDL operations that would override data changes in a current transaction.

Table Locks (TM)
A transaction automatically acquires a table lock (TM lock) when a table is modified with the following statements: INSERT, UPDATE, DELETE, MERGE, and SELECT … FOR UPDATE. These DML operations require table locks to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction. You can explicitly obtain a table lock using the LOCK TABLE statement, as described in ‘Manual Data Locking‘.”

It appears that the above quote was changed a bit and included in section 5-11 of the alpha copy of the book that I am reading:

“Oracle uses two types of locks to prevent destructive behavior: exclusive and share locks… Row locks, indicated by the symbol TX, lock just a single row of table for each row that’ll be modified by a DML statement such as INSERT, UPDATE, and DELETE. This is true also for a MERGE or a SELECT … FOR UPDATE statement. The transaction that includes one of these statements grabs an exclusive row lock as well as a row share table lock.”

—

Something to think about: If a row lock is also called a TX lock, and it is a lock on a single row in a table, does that mean that one would expect to see 1,000 of these TX locks for a single session if that session modifies 1,000 rows in a single table?  Or, might we expect to see only a single transaction lock (TX) for the session that modified 1,000 rows in a single table – that would indicate that a row lock is not synonymous with a TX lock.  For that matter, what about the other types of TX enqueues?

—

Maybe it works as Tom Kyte states here:

“the TX is not a row lock, TM is the ‘row lock’ actually.”

The above is further explained in this exchange on the OTN forums between Tom Kyte and Mark Bobak.

—

Some additional clarification from the latest version of Tom Kyte’s book (also found in his much older “Expert One-On-One Oracle” book):

“Whereas we get only one TX lock per transaction, we can get as many TM locks as the objects we modify.”

Combining the above quote with the quote from the documentation, does that mean that a session can only lock a single row per transaction?  🙂

—

So, is the documentation correct?  If not, how would you re-word what appears in the documentation?  Keep in mind the sub-title of this blog:

“Stop, Think, … Understand“

NOLOGGING Option and log file sync Waits – What is Wrong with this Quote?

June 8, 2011

I have not had published a “What is Wrong with this Quote” blog article for a couple of months, so I thought that I would publish a few of those types of articles.

The first quote is from the Alpha copy of the book “Oracle 11g Performance Tuning Recipes”, recipe 5-7 Resolving Log File Sync Waits.  As always, I try to minimize the amount of text that I post for a quote, but a sufficient amount so that the context of the quote is not completely lost.  The quote:

“As the log file sync wait event is caused by contention caused by the LGWR process, see if you can use the NOLOGGING option to get rid of these waits. Of course, in a production system, you can’t use the NOLOGGING option when the database is processing user requests, so this option is of limited use in most cases.”

“Although the database automatically calculates the value of the log_io_size parameter, you can specify a value for it, by issuing a command such as the following:

SQL> alter system set "_log_io_size"=1024000 scope=spfile;

System altered.                                 "

What, if anything, is wrong with the above quote?

Hint for the first half of the quote: AskTom, Documentation
Hint for the second half of the quote: The scale for the _log_io_size parameter is NOT bytes.  This article may also be helpful.

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.

V$SESSION’s ROW_WAIT_OBJ# Column Shows the DATA_OBJECT_ID – Verify the Documentation Challenge

June 7, 2011

While reading through the alpha copy of a performance tuning book, I noticed a set of SQL statements that were identical to those found in the Oracle Database 11.2 Performance Tuning Guide.  The set of SQL statements from the Performance Tuning Guide follow:

10.3.1.1 Causes
To determine the possible causes, first query V$SESSION to identify the value of ROW_WAIT_OBJ# when the session waits for buffer busy waits. For example:

SELECT row_wait_obj#
  FROM V$SESSION
 WHERE EVENT = 'buffer busy waits';

To identify the object and object type contended for, query DBA_OBJECTS using the value for ROW_WAIT_OBJ# that is returned from V$SESSION. For example:

SELECT owner, object_name, subobject_name, object_type
  FROM DBA_OBJECTS
 WHERE data_object_id = &row_wait_obj;

In an earlier article we found that V$SESSION’s ROW_WAIT_OBJ# column indicated the OBJECT_ID, not the DATA_OBJECT_ID of the object (as found in DBA_OBJECTS) and the same was true for the obj# entry found in a 10046 trace file.  When you first create an index the OBJECT_ID and the DATA_OBJECT_ID values will be the same for the index, as displayed in DBA_OBJECTS.  However, when you rebuild the index the DATA_OBJECT_ID value for the index will change while the OBJECT_ID value remains unchanged.  Likewise, when you first create a table the OBJECT_ID and the DATA_OBJECT_ID values will be the same, as displayed in DBA_OBJECTS.  If you TRUNCATE a table, the OBJECT_ID value will remain the same while the DATA_OBJECT_ID value for the table will change.

The challenge is to build a test case that either confirms or refutes the claim made by the Oracle Database Performance Tuning Guide.  Possible references:

• AskTom thread
• Metalink (MOS) ID 15476.1 “FAQ about Detecting and Resolving Locking Conflicts”
• Metalink (MOS) Doc ID 603661.1 “ORA-01410: Invalid Rowid When Using rowid Generated from V$Session and Dba_objects”
• Another article found on this blog (take a look at the Other Resources links)

Unique Index Result Error with Ref Cursor on Oracle 11g but Not on 10g

June 6, 2011

You might expect to see some differences when upgrading from one release version of Oracle Database to another release version (edit June 6, 2011: see this article for expected changes when upgrading from 10g to 11g).  For instance, you might find that Oracle Database 11.1.0.6 deadlock when Oracle Database 10.2.0.5 and below do not.  You might find that Oracle Database 11.1.0.6 and above throw error messages when ROWNUM is used when Oracle Database 10.2.0.5 and below do not throw error messages.  You might find that the following SQL statement (based on the contents of this article) returns different values for the SYS user depending on the Oracle Database release version:

SELECT SYS_CONTEXT('USERENV', 'SESSIONID') USERENV FROM DUAL; 

For example, on Oracle Database 11.2.0.2 when SYS connects as SYSDBA, you will see something like this for the output of that SQL statement:

USERENV
----------
4294967295

If the O7_DICTIONARY_ACCESSIBILITY parameter is set to TRUE on 11.2.0.2, and the SYS user connects without specifying AS SYSDBA, you might see something like this for the output of that SQL statement:

USERENV
-------
344841

If the SYS user connects on Oracle Database 9i, you will probably see something like this for the output of that SQL statement:

USERENV
-------
      0 

The above are all minor changes in bahavior.  If you are brave, go ahead and blindly upgrade to a new version of Oracle Database.  🙂

While browsing a couple of web forums I found another example of changed results caused by upgrading from Oracle Database 10g (possibly 10.2.0.4 or lower) to 11g (not stated, but possibly 11.2.0.2).  To be kind, my PL/SQL coding abilities are rusty, but I think that I follow the logic in that forum thread.  Let’s see if we are able to reproduce the problem with a couple of slight modifications.  First, let’s create a table with three rows:

DROP TABLE T1 PURGE;

CREATE TABLE T1 (
  ORDER_ID VARCHAR2(20),
  PROCESSED NUMBER);

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT; 

Now create a PL/SQL package with an embedded COMMIT (as stated in the forum thread linked to above, this embedded COMMIT is included because this example is from a stateless web application):

CREATE OR REPLACE PACKAGE T1_PACKAGE AS
  TYPE T1_CURSOR IS REF CURSOR;
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR);
END T1_PACKAGE;
/

CREATE OR REPLACE PACKAGE BODY T1_PACKAGE IS
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR) IS
  BEGIN
    OPEN T1_CURSOR_CURSOR FOR
    SELECT
      ORDER_ID
    FROM
      T1
    WHERE
      ORDER_ID= ORDER_ID_VARCHAR
      AND PROCESSED=0;

    UPDATE
      T1
    SET
      PROCESSED=1
    WHERE
      ORDER_ID= ORDER_ID_VARCHAR; 

    COMMIT;
  END T1_PROC_SELECT_UPDATE;
END T1_PACKAGE;
/ 

Hopefully, I haven’t lost anyone yet, in the above: select a row, return the row as a ref cursor, and then update the row to show that the row has been processed.  Now let’s process the three orders using three anonymous PL/SQL blocks (it is not necessary to use three anonymous PL/SQL blocks, but this was done to verify that the problem happens on multiple executions also):

SET SERVEROUTPUT ON

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/ 

The output from Oracle Database 11.2.0.2 looks like this:

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.
 

The above worked as expected, the anonymous PL/SQL blocks output TESTING, TESTING2, and TESTING3.  Not particularly useful as written, but it executes and outputs the expected information.

Let’s create a non-unique index on the ORDER_ID column and repeat the anonymous PL/SQL portion of the test:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

CREATE INDEX IND_T1 ON T1(ORDER_ID); 

The output from Oracle Database 11.2.0.2 showing the anonymous PL/SQL:

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.

The above test seemed to work as expected… TESTING, TESTING2, TESTING3.  Let’s create a primary key constraint on the ORDER_ID column, which will use the existing IND_T1 index to enforce the primary key constraint, then we will repeat the anonymous PL/SQL portion of the test:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

ALTER TABLE T1 ADD PRIMARY KEY(ORDER_ID);

SELECT
  INDEX_NAME
FROM
  DBA_INDEXES
WHERE
  TABLE_NAME='T1'; 

The script output on Oracle Database 11.2.0.2 showing the anonymous PL/SQL portion:

SQL> SELECT
  2    INDEX_NAME
  3  FROM
  4    DBA_INDEXES
  5  WHERE
  6    TABLE_NAME='T1';

INDEX_NAME
------------------------------
IND_T1

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.

Nothing unusual yet – you are probably wondering about the point of this article by now, almost there.  Let’s create a unique function based index on UPPER(ORDER_ID) and then repeat the anonymous PL/SQL portion of the test:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

CREATE UNIQUE INDEX IND_T1_FB ON T1(UPPER(ORDER_ID));

SELECT
  INDEX_NAME
FROM
  DBA_INDEXES
WHERE
  TABLE_NAME='T1'; 

The script output on Oracle Database 11.2.0.2 showing the anonymous PL/SQL portion of the output:

SQL> SELECT
  2    INDEX_NAME
  3  FROM
  4    DBA_INDEXES
  5  WHERE
  6    TABLE_NAME='T1';

INDEX_NAME
------------------------------
IND_T1
IND_T1_FB

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.

Still nothing unusual yet.  Let’s try one more time, removing the primary key constraint, dropping the two indexes, and creating a single unique index on the ORDER_ID column (in case you are wondering, with just the function-based unique index on the table there were no problems):

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

ALTER TABLE T1 DROP PRIMARY KEY;
DROP INDEX IND_T1;
DROP INDEX IND_T1_FB;
CREATE UNIQUE INDEX IND_T1_UNIQUE ON T1(ORDER_ID);

SELECT
  INDEX_NAME
FROM
  DBA_INDEXES
WHERE
  TABLE_NAME='T1'; 

The script output on Oracle Database 11.2.0.2 (and 10.2.0.5):

SQL> SELECT
  2    INDEX_NAME
  3  FROM
  4    DBA_INDEXES
  5  WHERE
  6    TABLE_NAME='T1';

INDEX_NAME
------------------------------
IND_T1_UNIQUE

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

Note that this time the anonymous PL/SQL blocks did NOT output TESTING, TESTING2, and TESTING3.  Oracle Database 10.2.0.5, 11.1.0.7, 11.2.0.1 and 11.2.0.2 all exhibit this unexpected behavior when a unique index is present on table T1’s ORDER_ID column. 

Oracle Database 10.2.0.4 does not exhibit this problem.  Below is the output from Oracle Database 10.2.0.4 for the last portion of the script:

SQL> CREATE UNIQUE INDEX IND_T1_UNIQUE ON T1(ORDER_ID);

Index created.

SQL>
SQL> SELECT
  2    BANNER
  3  FROM
  4    V$VERSION;

BANNER
---------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - Production
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL>
SQL> SELECT
  2    INDEX_NAME
  3  FROM
  4    DBA_INDEXES
  5  WHERE
  6    TABLE_NAME='T1';

INDEX_NAME
------------------------------
IND_T1_UNIQUE

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed.

Well, that is a little interesting.  Let’s try another test – remember that we had no trouble with the unique function-based index.  Let’s quickly revisit that example.  First, reset the T1 test table, drop the unique index, and then recreate the unique function-based index:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT;

DROP INDEX IND_T1_UNIQUE;
CREATE UNIQUE INDEX IND_T1_FB ON T1(UPPER(ORDER_ID)); 

Now let’s modify the package so that it will likely use the unique function-based index:

CREATE OR REPLACE PACKAGE T1_PACKAGE AS
  TYPE T1_CURSOR IS REF CURSOR;
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR);
END T1_PACKAGE;
/

CREATE OR REPLACE PACKAGE BODY T1_PACKAGE IS
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR) IS
  BEGIN
    OPEN T1_CURSOR_CURSOR FOR
    SELECT
      ORDER_ID
    FROM
      T1
    WHERE
      UPPER(ORDER_ID)= ORDER_ID_VARCHAR
      AND PROCESSED=0;

    UPDATE
      T1
    SET
      PROCESSED=1
    WHERE
      UPPER(ORDER_ID)= ORDER_ID_VARCHAR;

    COMMIT;
  END T1_PROC_SELECT_UPDATE;
END T1_PACKAGE;
/ 

Let’s retry the anonymous PL/SQL blocks:

SET SERVEROUTPUT ON

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

DECLARE
  CUR T1_PACKAGE.T1_CURSOR;
  TYPE T1_ROW_TYPE IS RECORD (
    ORDER_ID T1.ORDER_ID%TYPE);
  T1_ROW T1_ROW_TYPE;
BEGIN
  T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);

  LOOP
    FETCH CUR INTO T1_ROW;
    EXIT WHEN CUR%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Processing is complete');
END;
/

The output of the anonymous PL/SQL blocks:

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
Processing is complete

PL/SQL procedure successfully completed. 

Note that the anonymous PL/SQL blocks did NOT output TESTING, TESTING2, and TESTING3.  Let’s try fixing the package so that it will not use the unique function-based index:

CREATE OR REPLACE PACKAGE T1_PACKAGE AS
  TYPE T1_CURSOR IS REF CURSOR;
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR);
END T1_PACKAGE;
/

CREATE OR REPLACE PACKAGE BODY T1_PACKAGE IS
  PROCEDURE T1_PROC_SELECT_UPDATE (ORDER_ID_VARCHAR IN VARCHAR2,T1_CURSOR_CURSOR OUT T1_CURSOR) IS
  BEGIN
    OPEN T1_CURSOR_CURSOR FOR
    SELECT /*+ NO_INDEX(T1 IND_T1_FB) */
      ORDER_ID
    FROM
      T1
    WHERE
      UPPER(ORDER_ID)= ORDER_ID_VARCHAR
      AND PROCESSED=0;

    UPDATE
      T1
    SET
      PROCESSED=1
    WHERE
      UPPER(ORDER_ID)= ORDER_ID_VARCHAR;

    COMMIT;
  END T1_PROC_SELECT_UPDATE;
END T1_PACKAGE;
/ 

Now, reset the table’s rows:

TRUNCATE TABLE T1;

INSERT INTO T1 VALUES ('TESTING',0);
INSERT INTO T1 VALUES ('TESTING2',0);
INSERT INTO T1 VALUES ('TESTING3',0);

COMMIT; 

Finally, the output from the anonymous PL/SQL blocks on Oracle Database 11.2.0.2:

SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING2', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING2
Processing is complete

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    CUR T1_PACKAGE.T1_CURSOR;
  3    TYPE T1_ROW_TYPE IS RECORD (
  4      ORDER_ID T1.ORDER_ID%TYPE);
  5    T1_ROW T1_ROW_TYPE;
  6  BEGIN
  7    T1_PACKAGE.T1_PROC_SELECT_UPDATE('TESTING3', CUR);
  8
  9    LOOP
 10      FETCH CUR INTO T1_ROW;
 11      EXIT WHEN CUR%NOTFOUND;
 12
 13      DBMS_OUTPUT.PUT_LINE(T1_ROW.ORDER_ID);
 14    END LOOP;
 15
 16    DBMS_OUTPUT.PUT_LINE('Processing is complete');
 17  END;
 18  /
TESTING3
Processing is complete

PL/SQL procedure successfully completed. 

Testing 1, 2, 3.  I think that I just fixed a problem by using a hint… although we could have avoided the problem by creating a non-unique normal index with a primary key constraint, or by creating a non-unique index with a second unique function-based index to enforce the uniqueness in the column.

—

Added June 6, 2011:

The linked forum thread suggests that the problem might be identified in Metalink (MOS) Bug 10425196: “PL/SQL RETURNING REF CURSOR ACTS DIFFERENTLY ON 11.1.0.6 VS 10.2.0.5”.  I was able to reproduce the problem in Oracle Database 10.2.0.5 on the Windows platform.  Also, note in the above tests that it is not necessarily the presence of a primary key index on the table that could cause the problem – as shown above, the problem is apparently caused by cases where a unique index is used to retrieve the rows for the ref cursor.

Insert Error on Oracle 11g but Not on 10g

June 2, 2011

The most popular article on this blog includes a script that demonstrates generating a deadlock on Oracle Database 11g R1 and R2 when the exact same script on Oracle Database 10g R2 does not trigger a deadlock.  I came across another interesting change between Oracle Database 10.2.0.5 and 11.2.0.2 (the change also appears in 11.1.0.7) that affects the results of Insert statements that seemingly worked fine for years.

We will use the following simple script for this blog article:

CREATE TABLE T1(C1 DATE, C2 NUMBER);

CREATE TABLE T2(C1 DATE, C2 NUMBER);

CREATE OR REPLACE TRIGGER T1_AFTER_INSERT AFTER INSERT ON T1
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
  INSERT INTO T2 VALUES (SYSDATE,ROWNUM);
END T1_AFTER_INSERT;
/

INSERT INTO T1 VALUES(SYSDATE-10,-10);

INSERT INTO
  T1
SELECT
  SYSDATE+ROWNUM,
  ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=10;

INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM);

SELECT * FROM T2;

First, a pop quiz:

What is the expected output of the select from table T2?

 Answer A:

C1                C2
--------- ----------
02-JUN-11          1
02-JUN-11          2
02-JUN-11          3
02-JUN-11          4
02-JUN-11          5
02-JUN-11          6
02-JUN-11          7
02-JUN-11          8
02-JUN-11          9
02-JUN-11         10

10 rows selected.

—

Answer B:

C1                C2
--------- ----------
02-JUN-11          0
02-JUN-11          1
02-JUN-11          2
02-JUN-11          3
02-JUN-11          4
02-JUN-11          5
02-JUN-11          6
02-JUN-11          7
02-JUN-11          8
02-JUN-11          9
02-JUN-11         10

11 rows selected.

—

Answer C:

C1                C2
--------- ----------
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0
02-JUN-11          0

12 rows selected.

—

Answer D:

no rows selected

—

Answer E:

C1                C2
--------- ----------
02-JUN-11          1
02-JUN-11          2
02-JUN-11          3
02-JUN-11          4
02-JUN-11          5
02-JUN-11          6
02-JUN-11          7
02-JUN-11          8
02-JUN-11          9
02-JUN-11         10
02-JUN-11         11
02-JUN-11         12

12 rows selected.

—

Answer F:

C1                C2
--------- ----------
02-JUN-11          1
02-JUN-11          1

2 rows selected.

—————————————————–

Think about your answer for a minute… you might even want to try the experiment on an Oracle Database.  Scroll down when you think that you have the answer.

—

—

—

—

—

—

—

—

—

—

—

—

—

—

Script Output from Oracle Database 10.2.0.5:

SQL> CREATE TABLE T1(C1 DATE, C2 NUMBER);

Table created.

SQL> CREATE TABLE T2(C1 DATE, C2 NUMBER);

Table created.

SQL> CREATE OR REPLACE TRIGGER T1_AFTER_INSERT AFTER INSERT ON T1
  2  REFERENCING NEW AS NEW
  3  FOR EACH ROW
  4  BEGIN
  5  INSERT INTO T2 VALUES (SYSDATE,ROWNUM);
  6  END T1_AFTER_INSERT;
  7  /

Trigger created.

SQL> INSERT INTO T1 VALUES(SYSDATE-10,-10);

1 row created.

SQL> INSERT INTO
  2    T1
  3  SELECT
  4    SYSDATE+ROWNUM,
  5    ROWNUM
  6  FROM
  7    DUAL
  8  CONNECT BY
  9    LEVEL<=10;

10 rows created.

SQL> INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM);

1 row created.

SQL> SELECT * FROM T2;

C1                C2
--------- ----------
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0
01-JUN-11          0

12 rows selected.

Script Output from Oracle Database 11.2.0.2:

SQL> CREATE TABLE T1(C1 DATE, C2 NUMBER);

Table created.

SQL> CREATE TABLE T2(C1 DATE, C2 NUMBER);

Table created.

SQL> CREATE OR REPLACE TRIGGER T1_AFTER_INSERT AFTER INSERT ON T1
  2  REFERENCING NEW AS NEW
  3  FOR EACH ROW
  4  BEGIN
  5    INSERT INTO T2 VALUES (SYSDATE,ROWNUM);
  6  END T1_AFTER_INSERT;
  7  /

Trigger created.

SQL> INSERT INTO T1 VALUES(SYSDATE-10,-10);
INSERT INTO T1 VALUES(SYSDATE-10,-10)
            *
ERROR at line 1:
ORA-00976: Specified pseudocolumn or operator not allowed here.
ORA-06512: at "TESTUSER.T1_AFTER_INSERT", line 2
ORA-04088: error during execution of trigger 'TESTUSER.T1_AFTER_INSERT'

SQL> INSERT INTO
  2    T1
  3  SELECT
  4    SYSDATE+ROWNUM,
  5    ROWNUM
  6  FROM
  7    DUAL
  8  CONNECT BY
  9    LEVEL<=10;
  T1
  *
ERROR at line 2:
ORA-00976: Specified pseudocolumn or operator not allowed here.
ORA-06512: at "TESTUSER.T1_AFTER_INSERT", line 2
ORA-04088: error during execution of trigger 'TESTUSER.T1_AFTER_INSERT'

SQL> INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM);
INSERT INTO T1 VALUES(SYSDATE-ROWNUM,ROWNUM)
                              *
ERROR at line 1:
ORA-00976: Specified pseudocolumn or operator not allowed here.

SQL> SELECT * FROM T2;

no rows selected

—

The results for Oracle Database 11.1.0.7 are identical to those of Oracle Database 11.2.0.2.

The answer to the quiz question is therefore… C AND D, which is of course logically impossible.  Disappointed?  Maybe there is a bug ID in Metalink (MOS) for this particular problem.  🙂  If you search the web, you can find similar SQL statements in various Internet forums, for example on the OTN forums.