TOP n Queries

So you’ve been given a relatively simple task of getting the last 5 modified rows from a table, which contains a column that holds the modified date. How do you write the query to do that?

Back in pre-10 versions of Oracle it was likely that the query was written:

SELECT *
FROM   (SELECT *
        FROM   my_table
        ORDER  BY
               created_date DESC)
WHERE  ROWNUM <= 5

… and this works pretty well. If you had written it:

SELECT *
FROM   my_table
WHERE  ROWNUM <= 5
ORDER  BY
       created_date DESC

you would (hopefully) have observed some strange output during your testing and adjusted your query accordingly. For the Oracle newcomers, in the above query Oracle selects 5 rows and then sorts them, not the other way around, so it’s the equivalent of telling Oracle “get any 5 rows from the table and give them to me sorted descending by the created date”.

Somewhere around Oracle 10g the recommendation was to use the ROW_NUMBER analytic function in place of ROWNUM, i.e.

SELECT *
FROM   (SELECT t.*
        ,      ROW_NUMBER() OVER (ORDER BY created_date DESC) rn
        FROM   my_table t)
WHERE  rn <= 5

Now in version 12c of the database Oracle has introduced the FETCH FIRST ROWS syntax for doing exactly this kind of query. It makes things quite simple and clear:

SELECT *
FROM   my_table
ORDER  BY
       created_date DESC
FETCH FIRST 5 ROWS ONLY

Now let’s take a peek under the covers and see what Oracle is actually doing when faced with these queries. To start with we’ll create a simple table with 1,000,000 rows:

CREATE TABLE big_table
   (id            NUMBER(8)   NOT NULL
   ,cat           NUMBER(4)   NOT NULL
   ,padding       CHAR(30)    NOT NULL
   ,last_mod_date DATE        NOT NULL)
/

INSERT INTO big_table
SELECT ROWNUM
,      MOD(ROWNUM,1000) AS cat
,      'x' AS padding
,      TO_DATE('01/01/2000','dd/mm/yyyy') + dbms_random.value(0,5000) AS last_mod_date
FROM   (SELECT 'x'
        FROM   dual
        CONNECT BY ROWNUM <= 1000) x
,      (SELECT 'x'
        FROM   dual
        CONNECT BY ROWNUM <= 1000) y
/        

COMMIT
/

EXEC dbms_stats.gather_table_stats ('','big_table')

CREATE UNIQUE INDEX  big_table_pk
   ON big_table (id)
/

ALTER TABLE big_table
   ADD CONSTRAINT big_table_pk
   PRIMARY KEY (id)
/

Obviously we want to access our last created rows as quick as possible so we’ll index that column:

CREATE INDEX big_table_ix1
   ON big_table(last_mod_date)
/   

We’ll run each query against our table with AUTOTRACE enabled to see the execution plan and cost. As with anything related to Oracle performance it’s important to keep in mind the version that you’re using as things can change across versions. In light of that statement, the following examples were run against a 12.1.0.2 database. First up is our ROWNUM approach:

SQL>SELECT r.id
  2  ,      r.cat
  3  ,      r.padding
  4  ,      r.last_mod_date
  5  FROM   (SELECT b.id
  6          ,      b.padding
  7          ,      b.last_mod_date
  8          ,      b.cat
  9          FROM   big_table b
 10          ORDER  BY
 11                 b.last_mod_date DESC) r
 12  WHERE  ROWNUM <= 5
 13  /

        ID        CAT PADDING                        LAST_MOD_DATE
---------- ---------- ------------------------------ -------------------
    545616        616 x                              08/09/2013 23:59:05
    557331        331 x                              08/09/2013 23:57:45
      5220        220 x                              08/09/2013 23:54:28
    874232        232 x                              08/09/2013 23:50:34
    610984        984 x                              08/09/2013 23:39:15


Execution Plan
----------------------------------------------------------
Plan hash value: 2877194421

-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |     5 |   335 |     8   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                |               |       |       |            |          |
|   2 |   VIEW                        |               |     5 |   335 |     8   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| BIG_TABLE     |  1000K|    45M|     8   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN DESCENDING| BIG_TABLE_IX1 |     5 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=5)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        954  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

As we can see from the execution plan, Oracle traversed the index on the LAST_MODIFIED_DATE in a descending fashion, returned each table entry found until, and this is the COUNT STOPKEY bit, it had returned the requested number of rows. All up the query required 9 logical IO operations so it’s very efficient.

Next up we do the same with the ROW_NUMBER query:

SQL>SELECT r.id
  2  ,      r.cat
  3  ,      r.padding
  4  ,      r.last_mod_date
  5  FROM   (SELECT b.id
  6          ,      b.cat
  7          ,      b.padding
  8          ,      b.last_mod_date
  9          ,      ROW_NUMBER() OVER (ORDER BY b.last_mod_date DESC) AS rn
 10          FROM   big_table b) r
 11  WHERE  rn <= 5
 12  /

        ID        CAT PADDING                        LAST_MOD_DATE
---------- ---------- ------------------------------ -------------------
    545616        616 x                              08/09/2013 23:59:05
    557331        331 x                              08/09/2013 23:57:45
      5220        220 x                              08/09/2013 23:54:28
    874232        232 x                              08/09/2013 23:50:34
    610984        984 x                              08/09/2013 23:39:15


Execution Plan
----------------------------------------------------------
Plan hash value: 2679878340

----------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |     5 |   400 |       | 13907   (1)| 00:00:01 |
|*  1 |  VIEW                    |           |     5 |   400 |       | 13907   (1)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|           |  1000K|    45M|    57M| 13907   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | BIG_TABLE |  1000K|    45M|       |  1984   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN"<=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("B"."LAST_MOD_DATE")
              DESC )<=5)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       7302  consistent gets
          0  physical reads
          0  redo size
        954  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed

Well, at least we got back the same rows so our query worked but Oracle completely ignored our index and chose to perform a full table scan, costing a total of 7,302 logical IOs. Ouch!

Moving onto the FETCH FIRST ROWS syntax:

SQL>SELECT r.id
  2  ,      r.cat
  3  ,      r.padding
  4  ,      r.last_mod_date
  5  FROM   big_table r
  6  ORDER  BY
  7         r.last_mod_date DESC
  8  FETCH FIRST 5 ROWS ONLY
  9  /

        ID        CAT PADDING                        LAST_MOD_DATE
---------- ---------- ------------------------------ -------------------
    545616        616 x                              08/09/2013 23:59:05
    557331        331 x                              08/09/2013 23:57:45
      5220        220 x                              08/09/2013 23:54:28
    874232        232 x                              08/09/2013 23:50:34
    610984        984 x                              08/09/2013 23:39:15


Execution Plan
----------------------------------------------------------
Plan hash value: 2679878340

----------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |     5 |   445 |       | 13907   (1)| 00:00:01 |
|*  1 |  VIEW                    |           |     5 |   445 |       | 13907   (1)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|           |  1000K|    45M|    57M| 13907   (1)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | BIG_TABLE |  1000K|    45M|       |  1984   (1)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("R"."LAST_MOD_DATE")
              DESC )<=5)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       7302  consistent gets
          0  physical reads
          0  redo size
        954  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed

We can see from the execution plan that the FETCH FIRST ROWS syntax has used exactly the same execution plan as the ROW_NUMBER approach; a full table scan. Indeed, the predicate information of the plan shows that it has used the ROW_NUMBER function.

So far we have seen that the ROW_NUMBER and FETCH FIRST ROWS approaches have ignored a suitable index. Let’s look at another scenario that might be a bit more realistic. The table contains a CAT column (for CATegory), which contains 1,000 distinct values. The query we’ll run is “Get the last 5 modified rows for category 42″. For this exercise we’ll create a new index on the category and the last modified date:

CREATE INDEX big_table_ix2
   ON big_table(cat, last_mod_date)

First up the ROWNUM approach:

SQL>SELECT r.id
  2  ,      r.cat
  3  ,      r.padding
  4  ,      r.last_mod_date
  5  FROM   (SELECT b.id
  6          ,      b.padding
  7          ,      b.last_mod_date
  8          ,      b.cat
  9          FROM   big_table b
 10          WHERE  cat = 42
 11          ORDER  BY
 12                 b.last_mod_date DESC) r
 13  WHERE  ROWNUM <= 5
 14  /

        ID        CAT PADDING                        LAST_MOD_DATE
---------- ---------- ------------------------------ -------------------
    156042         42 x                              17/08/2013 23:57:57
    118042         42 x                              17/08/2013 12:44:38
    266042         42 x                              11/08/2013 20:13:13
    805042         42 x                              04/08/2013 08:45:18
    151042         42 x                              30/07/2013 06:46:54


Execution Plan
----------------------------------------------------------
Plan hash value: 200163764

------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |     5 |   335 |     9   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                 |               |       |       |            |          |
|   2 |   VIEW                         |               |     5 |   335 |     9   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID | BIG_TABLE     |  1000 | 48000 |     9   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN DESCENDING| BIG_TABLE_IX2 |     5 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=5)
   4 - access("CAT"=42)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        941  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

Similar to our previous query, the ROWNUM approach does uses the new index, keeping the number of logical IO operations down to just 9. Let’s see how the ROW_NUMBER version of our query fares:

SQL>SELECT r.id
  2  ,      r.cat
  3  ,      r.padding
  4  ,      r.last_mod_date
  5  FROM   (SELECT b.id
  6          ,      b.cat
  7          ,      b.padding
  8          ,      b.last_mod_date
  9          ,      ROW_NUMBER() OVER (ORDER BY b.last_mod_date DESC) AS rn
 10          FROM   big_table b
 11          WHERE  cat = 42) r
 12  WHERE  rn <= 5
 13  /

        ID        CAT PADDING                        LAST_MOD_DATE
---------- ---------- ------------------------------ -------------------
    156042         42 x                              17/08/2013 23:57:57
    118042         42 x                              17/08/2013 12:44:38
    266042         42 x                              11/08/2013 20:13:13
    805042         42 x                              04/08/2013 08:45:18
    151042         42 x                              30/07/2013 06:46:54

Execution Plan
----------------------------------------------------------
Plan hash value: 1296513801

------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |     5 |   400 |  1006   (0)| 00:00:01 |
|*  1 |  VIEW                          |               |     5 |   400 |  1006   (0)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY        |               |  1000 | 48000 |  1006   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID | BIG_TABLE     |  1000 | 48000 |  1006   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN DESCENDING| BIG_TABLE_IX2 |  1000 |       |     6   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RN"<=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("B"."LAST_MOD_DATE") DESC
              )<=5)
   4 - access("CAT"=42)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        941  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

Well, that’s a bit better. With the ROW_NUMBER query Oracle did elect to use the new index. The presence of the number of the INDEX RANGE SCAN DESCENDING and WINDOW NOSORT STOPKEY lines in the execution plan show that Oracle is only accessing the minimum number of entries it needs to satisfy the query, as opposed to all the entries with a CAT value of 42. This is confirmed by the statistics report showing the logical IO operations is just 10.

Now to see if the FETCH FIRST ROWS syntax does the same:

SQL>SELECT r.id
  2  ,      r.cat
  3  ,      r.padding
  4  ,      r.last_mod_date
  5  FROM   big_table r
  6  WHERE  cat = 42
  7  ORDER  BY
  8         r.last_mod_date DESC
  9  FETCH FIRST 5 ROWS ONLY
 10  /

        ID        CAT PADDING                        LAST_MOD_DATE
---------- ---------- ------------------------------ -------------------
    156042         42 x                              17/08/2013 23:57:57
    118042         42 x                              17/08/2013 12:44:38
    266042         42 x                              11/08/2013 20:13:13
    805042         42 x                              04/08/2013 08:45:18
    151042         42 x                              30/07/2013 06:46:54


Execution Plan
----------------------------------------------------------
Plan hash value: 1296513801

------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |     5 |   445 |  1006   (0)| 00:00:01 |
|*  1 |  VIEW                          |               |     5 |   445 |  1006   (0)| 00:00:01 |
|*  2 |   WINDOW NOSORT STOPKEY        |               |  1000 | 48000 |  1006   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID | BIG_TABLE     |  1000 | 48000 |  1006   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN DESCENDING| BIG_TABLE_IX2 |  1000 |       |     6   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("R"."LAST_MOD_DATE") DESC
              )<=5)
   4 - access("CAT"=42)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        941  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

As per our previous query, the FETCH FIRST ROWS approach appears to be nothing more than a rewrite of the ROW_NUMBER one. The plan and the statistics are all the same.

Admittedly both of the above query scenarios are somewhat contrived. It is unlikely that the last modified date column would be indexed unless there is a driving need for top n type queries against the data. A more likely scenario would be to have an index on the CAT column only. With just this index in place all three queries performed near identical plans of:

--------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |               |     5 |   335 |  1006   (1)| 00:00:01 |
|*  1 |  COUNT STOPKEY                         |               |       |       |            |          |
|   2 |   VIEW                                 |               |  1000 | 67000 |  1006   (1)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY               |               |  1000 | 48000 |  1006   (1)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| BIG_TABLE     |  1000 | 48000 |  1005   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN                  | BIG_TABLE_IX3 |  1000 |       |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

and had statistics of:

Statistics
---------------------------------------------------
          0  recursive calls
          0  db block gets
       1005  consistent gets
          0  physical reads
          0  redo size
        941  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed

In this scenario any approach to finding the last modified rows is the same from a resource consumption perspective. However, it is particularly interesting how efficient a suitable index makes the query though. Without the LAST_MOD_DATE column being indexed the queries now require just over 1,000 logical IO operations, which is 100 times what they required when the column was included from the index. This provides a nice example of creating indexes that are appropriate to the queries being run against the data.

In summary, we have multiple approaches to writing a TOP n type query; a sorted in-line view with ROWNUM filtering, an in-line view with ROW_NUMBER filter and, with 12c, the FETCH FIRST ROWS syntax. If a suitable access path index is in place then all approaches seem roughly equivalent in terms of cost, except for the cast where the query run against the entire table. In this situation only the ROWNUM approach made use of an index on the LAST_MOD_DATE. As per many things related to the query optimiser, check what you might expect is actually happening and adjust accordingly.

ORA-00001 unique key violated with MERGE

I arrived at work one morning to find a system generated email telling me that a user session had thrown a unique constraint violation exception. Curious given the code had been running in production for 6 months…

The line number for the PL/SQL package that had thrown the exception showed that it came from a MERGE statement. The fact that it was a MERGE that had thrown an ORA-00001 immediately points to a concurrency issue. At first the developer of the code didn’t understand how a MERGE could throw ORA-00001 until I showed them the following example.

Starting with a simple two column table with 10 rows (running under Oracle 12.1.0.2):

CREATE TABLE merge_test
    (id     NUMBER(6) NOT NULL
    ,val    NUMBER(6) NOT NULL
    ,CONSTRAINT merge_test_pk PRIMARY KEY (id))
/

INSERT INTO merge_test
SELECT ROWNUM
,      ROWNUM
FROM   dual
CONNECT BY ROWNUM <= 10
/

COMMIT
/

EXEC dbms_stats.gather_table_stats ('','merge_test')

we run the following MERGE in two sessions, operating against an existing row in the table:

MERGE INTO merge_test dest
USING (SELECT 5    AS id
       ,      20   AS val
       FROM   dual) src
ON (dest.id = src.id)
WHEN MATCHED THEN UPDATE
SET dest.val = src.val
WHEN NOT MATCHED THEN INSERT
   (id, val)
VALUES
   (src.id, src.val)

The first session immediately reports:

1 row merged.

while the second session, attempting to obtain a lock on the same row, is blocked. Once the first session issues a commit or rollback then the second session also reports 1 row merged.

Now let’s repeat this exercise using an ID value for a row that does not exist in the table:

MERGE INTO merge_test dest
USING (SELECT 20   AS id
       ,      20   AS val
       FROM   dual) src
ON (dest.id = src.id)
WHEN MATCHED THEN UPDATE
SET dest.val = src.val
WHEN NOT MATCHED THEN INSERT
   (id, val)
VALUES
   (src.id, src.val)

Session 1 reports the 1 row merged while session 2, after session 1 commits, reports:

MERGE INTO merge_test dest
*
ERROR at line 1:
ORA-00001: unique constraint (DEVELOPER.MERGE_TEST_PK) violated

The reason for this is all related to Oracle’s multi-versioning read consistency model. At the point in time that session 2 executed the MERGE session 1 had not committed the data so session 2 cannot “see” the row that session 1 is about to insert. As a result the MERGE that session 2 executes also attempts to insert a new row. Oracle then blocks session 2 as it detects a uniqueness conflict with the row that session 1 is inserting. Oracle does not immediately report the exception as it needs to wait until session 1 commits or rollbacks its transaction. When session 1 commits the transaction then session 2 throws the ORA-00001 exception. If session 1 had issued a rollback then session 2 would have been permitted to insert the new row.

The solution to this problem is to simply remove the MERGE and replace it with an INSERT/UPDATE combination:

BEGIN
   INSERT INTO merge_test (id, val)
   VALUES (20, 20);
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      UPDATE merge_test
      SET    val = 20
      WHERE  id = 20;
END;

With this code session 2 will block on its attempt to perform the INSERT. Once session 1 issues a commit then session 2 intercepts the DUP_VAL_ON_INDEX exception that is thrown, which is then caught and the operation is changed to an UPDATE.

Back to the production problem, a scan of the application log files and database entries did indeed show that there were two near simultaneous request that would have resulted in the MERGE being run for the same key, confirming the diagnosis. Another problem solved and, for the developer of the code, another lesson learned.

String constants and package state

I have previously discussed package state and avoiding the “ORA-04068: existing state of packages has been discarded” exception. Connor McDonald has pointed out that come Oracle 11.2 constants do not create package state, except when they involve a calculation. This post expands on that by noting that string constants always create package state (thanks to my work colleague Vlad for pointing this out, after finding out the hard way…).

First up, let’s take a look at how numeric constants don’t create package state. We start with the simplest of packages compiled in session 1:

CREATE OR REPLACE PACKAGE pkg_state
AS
   c_number   CONSTANT NUMBER(5) := 123;
END pkg_state;

and in another session, session 2, we run a short program that makes reference to the package constant:

SET SERVEROUTPUT ON
BEGIN
   dbms_output.put_line ('Package value is: ' || TO_CHAR(pkg_state.c_number));
END;

Not surprisingly we get the output:

Package value is: 123

If we go back to session 1 and recompile the package changing the constant to a different value:

CREATE OR REPLACE PACKAGE pkg_state
AS
   c_number   CONSTANT NUMBER(5) := 789;
END pkg_state;

and rerun our simple output script back in session 2 we don’t get ORA-04068 but instead our session displays the new value:

Package value is: 789

Now let’s repeat the entire exercise but with a string constant. In session 1:

CREATE OR REPLACE PACKAGE pkg_state
AS
   c_string   CONSTANT VARCHAR2(5) := 'ABC';
END pkg_state;

Running this in session 2:

SET SERVEROUTPUT ON
BEGIN
   dbms_output.put_line ('Package value is: ' || pkg_state.c_string);
END;

we get:

Package value is: ABC

Compiling the package in session 1 to:

CREATE OR REPLACE PACKAGE pkg_state
AS
   c_string   CONSTANT CHAR(5) := 'XYZ';
END pkg_state;

gives us the following when we rerun our display routine in session 2:

BEGIN
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "DEVELOPER.PKG_STATE" has been invalidated
ORA-04065: not executed, altered or dropped package "DEVELOPER.PKG_STATE"
ORA-06508: PL/SQL: could not find program unit being called: "DEVELOPER.PKG_STATE"
ORA-06512: at line 2

This occurs with Oracle 11.2 and 12.1. So, while in these versions have taken steps to limit constants from creating package state, Connor’s post and this post show that it doesn’t apply to all constants. Hence some care needs to be taken when planning your deployments. You wouldn’t want to be in a situation where you think you don’t have package state but in reality you do. Right Vlad?

ORA-01001: invalid cursor with REF CURSOR output parameter

A short post to note an Oracle database bug I encountered recently. Whilst testing some code the exception:

ORA-01001: invalid cursor

was produced. The error was originating from a pipelined function. Tracking things down it seems that Oracle will throw this exception when the following occur:

  • A PL/SQL routine passes out a ref cursor variable
  • The ref cursor passed out uses a pipelined function
  • The pipelined function itself has a ref cursor as as input parameter

Having replicated the problem on Linux, AIX and WIndows I believe it is not platform specific. It occurs in database versions 11.2 and 12.1. Earlier versions have not been tested and so may be affected too.

The bug appears to be similar to Bug 2968664 : ORA-1001 IN FUNCTION CALLED FROM SQL WHICH FETCHES FROM A REFCURSOR PARAMETER but that bug is noted to be fixed in version 10.1.0.1.

I created a simple test package, imaginatively named pipeline_test, for Oracle Support to replicate the problem. The package contains 3 callable routines:

  • run_test1 – a routine with an OUT ref cursor parameter that matches the criteria noted earlier. Calling this routine throws ORA-01001
  • run_test2 – a routine that opens a ref cursor similar to run_test1 but this routine consumes that cursor rather than passing it out to a caller. This routine does not generate ORA-01001.
  • run_test3 – A routine that calls run_test1 to obtain a ref cursor and then consumes it, similar to run_test2. This routine does generate ORA-01001.

The package and test script are available for download. The setup.sql file creates the package and collection object required by the pipelined function while the run.sql script is a basic SQL*Plus script to call the test routines.

For my situation I was to work around the problem by substituting a collection for the ref cursor that the pipelined function used. I had some luck with hinting the query that used the pipelined function with the MATERIALIZE hint.

I’ll update this post with the details that I get back from Oracle Support. At the moment they have acknowledged that there is a problem and they are investigating.


Update 01 May 2015: Oracle have created a new bug, Bug 20405099, to track this problem. However, access to the bug details on Oracle Support is restricted so you’ll have to take my word on it. :-)

Avoid SQL injection with DBMS_ASSERT

A simple internet search will reveal some serious computer system hacks that were, fundamentally, a result of SQL injection. If you plan on doing any significant amount of database development you need to be aware of what SQL injection is and how to identify and mitigate it within your system.

In Oracle the easiest way to avoid SQL injection is to simply use static SQL with bind variables. Stick to this strategy and you can’t go wrong. However, there may be some scenarios where it isn’t possible to use simple queries with binds and dynamic SQL is required. One scenario that regularly crops up in applications I’ve worked on is a generic search screen; uses are presented with a screen containing a number of fields for entering search criteria. The system dynamically generates an SQL statement using only the fields that have been filled in. Once you’re dealing with dynamic SQL you’ve got a potential SQL injection problem. Thankfully Oracle has provided a package to help us; DBMS_ASSERT.

I’ll illustrate the use of DBMS_ASSERT using a simple dynamic SQL routine. The following DO_SQL procedure takes as input parameters a schema name, a table name, a column name and a “starts with” data filter for the column requested. The routine dynamically generates an SQL statement of the form:

SELECT <column_name>
FROM   <schema_name>.<table_name>
WHERE  <column_name> LIKE '<starts_with>%';

and returns a cursor with the results from the generated statement. The routine, wrapped into a simple SQL*PLus test harness, is:

VARIABLE rc REFCURSOR

DECLARE

   l_table_owner VARCHAR2(100);
   l_table_name  VARCHAR2(100);
   l_column_name VARCHAR2(100);
   l_starts_with VARCHAR2(100);

   PROCEDURE do_sql (p_table_owner IN  VARCHAR2
                    ,p_table_name  IN  VARCHAR2
                    ,p_column_name IN  VARCHAR2
                    ,p_starts_with IN  VARCHAR2
                    ,p_rc          OUT SYS_REFCURSOR)
   AS                 
      l_sql_string VARCHAR2(32767);
   BEGIN
      l_sql_string := 'SELECT ' || p_column_name ||
                      ' FROM  ' || p_table_owner ||'.' || p_table_name ||
                      ' WHERE ' || p_column_name || ' LIKE ''' || p_starts_with || '%'''; 

      OPEN p_rc FOR l_sql_string;      
   END do_sql;
   
BEGIN   
   l_table_owner  := 'SCOTT';
   l_table_name   := 'DEPT';                  
   l_column_name  := 'DNAME';                 
   l_starts_with  := 'A';

   do_sql (p_table_owner => l_table_owner
          ,p_table_name  => l_table_name
          ,p_column_name => l_column_name
          ,p_starts_with => l_starts_with
          ,p_rc          => :rc);
END;
/

PRINT rc

Running the routine using the inputs of:

  • schema name: SCOTT
  • table name: DEPT
  • column_name: DNAME
  • starts with: A

produces the following:

DNAME
--------------
ACCOUNTING

Before we get to DBMS_ASSERT let’s now look at how we might exploit this routine using SQL injection. Let’s change the inputs to the following:

  • schema name: SCOTT
  • table name: DEPT
  • column_name: DNAME
  • starts with: A” UNION ALL SELECT username FROM all_users —

which produces the output:

DNAME
----------------------
SCOTT
APEX_APPS
OEHRAPP
--snip --
SYSDG
SYSBACKUP
SYSTEM
AUDSYS
SYS

44 rows selected.

In the above I managed to obtain a listing of the users in the database by “extending” the query to run SELECT USERNAME FROM ALL_USERS. By modifying the input parameters I managed to execute the query:

SELECT DNAME 
FROM   SCOTT.DEPT 
WHERE  DNAME LIKE 'A' 
UNION  ALL 
SELECT username 
FROM   all_users --%'

Note, the double hyphen at the end, which denotes the start of a comment in SQL, is to remove the %’ that the generated SQL puts on the end.

Let’s now look at how we can protect ourselves against this using DBMS_ASSERT. We’ll look at the following routines from the package:

  • SCHEMA_NAME
  • SQL_OBJECT_NAME
  • SIMPLE_SQL_NAME
  • QUALIFIED_SQL_NAME
  • ENQUOTE_NAME
  • ENQUOTE_LITERAL

SCHEMA_NAME

This routine can be used to ensure that a value provided for a schema is actually a schema within the database. If the value isn’t a schema name then an exception is thrown. In our example, we would use the routine as follows:

l_sql_string := 'SELECT ' || p_column_name ||
                ' FROM  ' || DBMS_ASSERT.SCHEMA_NAME(p_table_owner) ||'.' || p_table_name ||
                ' WHERE ' || p_column_name || ' LIKE ''' || p_starts_with || '%'''; 

Providing a value that is not a schema in the database results in:

ORA-44001: invalid schema

SQL_OBJECT_NAME

Similar to the SCHEMA_NAME routine, this one checks that the name provided is a valid SQL object name that exists in the database. We could use this routine to check that the table we want to query exists, i.e.:

l_sql_string := 'SELECT ' || p_column_name ||
                ' FROM  ' || DBMS_ASSERT.SQL_OBJECT_NAME(p_table_owner||'.' || p_table_name)  ||
                ' WHERE ' || p_column_name || ' LIKE ''' || p_starts_with || '%'''; 

This would be a safer test than just a test to check that the schema exists. Providing a value that is not an object in the database results in:

ORA-44002: invalid object name

SIMPLE_SQL_NAME

This routine checks that the value provided satisfies the rules for an object name without actually verifying that such an object exists in the database. In our example we would use it as follows:

l_sql_string := 'SELECT ' || p_column_name ||
                ' FROM  ' || DBMS_ASSERT.SIMPLE_SQL_NAME(p_table_owner) ||'.'
                          || DBMS_ASSERT.SIMPLE_SQL_NAME(p_table_name) ||
                ' WHERE ' || p_column_name || ' LIKE ''' || p_starts_with || '%'''; 

If we wanted to ensure that the column reference is actually a column, as opposed to some sort of calculation, then we could apply SIMPLE_SQL_NAME to the column reference too, i.e.:

l_sql_string := 'SELECT ' || DBMS_ASSERT.SIMPLE_SQL_NAME(p_column_name) ||
                ' FROM  ' || DBMS_ASSERT.SIMPLE_SQL_NAME(p_table_owner) ||'.'
                          || DBMS_ASSERT.SIMPLE_SQL_NAME(p_table_name) ||
                ' WHERE ' || p_column_name || ' LIKE ''' || p_starts_with || '%'''; 

Providing a parameter that violates the naming rules results in:

ORA-44003: invalid SQL name

QUALIFIED_SQL_NAME

While the SIMPLE_SQL_NAME can only be used to validate each component of an overall object name the QUALIFIED_SQL_NAME routine can be used for a fully qualified object name, inclusive of database link component if required. In our example we would use it as follows:

l_sql_string := 'SELECT ' || p_column_name ||
                ' FROM  ' || DBMS_ASSERT.QUALIFIED_SQL_NAME(p_table_owner || '.' || p_table_name) ||
                ' WHERE ' || p_column_name || ' LIKE ''' || p_starts_with || '%'''; 

Passing in an invalid name results in:

ORA-44004: invalid qualified SQL name

Once again, this routine does not verify that an SQL object exists with such a name, only if the name is a valid name. In our example, running the routine with a valid name that does not exist results in;

ORA-00942: table or view does not exist

ENQUOTE_NAME

Oracle will accept names using any character, including spaces and punctuation, if the name is enclosed in double quotes. For example “A silly column name!” is a legitimate name. DBMS_ASSERT.ENQUOTE_NAME wraps a name in double quotes to handle this situation. By default it will also make all alphabetic characters upper case unless the second parameter is set to FALSE. Names already enclosed in double quotes are left alone.

In our example, we would use the routine as follows:

l_sql_string := 'SELECT ' || DBMS_ASSERT.SIMPLE_SQL_NAME(DBMS_ASSERT.ENQUOTE_NAME(p_column_name)) ||
                ' FROM  ' || DBMS_ASSERT.QUALIFIED_SQL_NAME(DBMS_ASSERT.ENQUOTE_NAME(p_table_owner) || '.' 
                          || DBMS_ASSERT.ENQUOTE_NAME(p_table_name)) ||
                ' WHERE ' || p_column_name || ' LIKE ''' || p_starts_with || '%'''; 

Once a name is enclosed in double quotes it will pass the checking done within DBMS_ASSERT.SIMPLE_SQL_NAME.
An exception is thrown by DBMS_ASSERT.ENQUOTE_NAME if the string provided contains a double quote in any position other than the first and last character:

ORA-44003: invalid SQL name

ENQUOTE_LITERAL

This routine will enclose a string literal inside single quotes, making it suitable for string literal parameters within SQL. In our example we would use it as:

l_sql_string := 'SELECT ' || DBMS_ASSERT.SIMPLE_SQL_NAME(p_column_name) ||
                ' FROM  ' || DBMS_ASSERT.QUALIFIED_SQL_NAME(p_table_owner || '.' || p_table_name) ||
                ' WHERE ' || p_column_name || ' LIKE ' || DBMS_ASSERT.ENQUOTE_LITERAL(p_starts_with || '%') ; 

If we were to attempt the SQL injection attack noted at the start of the post then we would get the following exception:

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_ASSERT", line 409
ORA-06512: at "SYS.DBMS_ASSERT", line 493
ORA-06512: at line 16
ORA-06512: at line 36

which is what DBMS_ASSERT throws if the string contains a single quote, not doubled up with another single quote.

Summary

SQL injection is not a theoretical security problem, it’s a very real problem. In Oracle the best way to mitigate it is use static SQL statements. This may not always be possible though and so you will need to take steps to prevent dynamically generated SQL from being abused. Oracle provides you with DBMS_ASSERT for just this purpose. The routines contained in DBMS_ASSERT go a long way to protect the various parts of an SQL statement from being used for injection.

Lastly, please be aware that if you do something as silly as to allow applications to call a routine similar to:

PROCEDURE do_sql (p_sql_string)
AS
BEGIN
    EXECUTE IMMEDIATE p_sql_string;
END do_sql;

then there is very, very little that can do done for your security.

ORA-01466: unable to read data – table definition has changed

Where I currently work a significant proportion of business is done on-line, via the web or mobile app. For the OLTP side of the systems this means we have a strong focus on performance and availability. As an example, we deliberately avoid package state in our database packages so we can deploy new versions of code without having to take the system down. Similarly, many database maintenance activities are performed on-line too… which is why I was concerned when I arrived at work one morning to find an on-line session had encountered the following exception:

ORA-01466: unable to read data - table definition has changed

The operation in question was a simple SELECT against a single interval partitioned table. Cross referencing the application logs to the database activity the error occurred at the same time that a partition was dropped on the table. The partition drop was part of a standard job to remove old data. The DDL issued against the table was:

ALTER TABLE partition_test SET INTERVAL (INTERVAL '7' DAY)
ALTER TABLE partition_test DROP PARTITION earliest_partition

The first command shifts the last range partition to the last partition of the table, converting the interval partitions to range partitions, thereby avoiding the exception thrown if an attempt is made to drop the last range partition. This then allows the dropping of old partitions without trouble.

Testing using the packaged routines in separate sessions failed to generate ORA-01466. No matter the order that the SELECT or DDL was executed no problems were encountered. Reading up on ORA-01466 on-line, one scenario that can give rise to the error is the use of read only queries, which led to how the error occurred…

Let’s use a simple interval partitioned table populated with 50 rows to illustrate the problem:

CREATE TABLE partition_test
   (id      NUMBER(5))
PARTITION BY RANGE (id) INTERVAL (10)
(PARTITION partition_test_p1 VALUES LESS THAN (10))
/

INSERT INTO partition_test
SELECT ROWNUM
FROM   dual
CONNECT BY ROWNUM <= 50
/

COMMIT
/

If we execute our partition maintenance commands followed immediately by a simple SELECT then we get a correct result:

SQL> ALTER TABLE partition_test SET INTERVAL (10)
  2  /

Table altered.

SQL> ALTER TABLE partition_test DROP PARTITION partition_test_p1
  2  /

Table altered.

SQL> SELECT *
  2  FROM   partition_test
  3  WHERE  id = 40
  4  /

        ID
----------
        40

However if our SELECT is done within a session that is read only:

SQL> ALTER TABLE partition_test SET INTERVAL (10)
  2  /

Table altered.

SQL> ALTER TABLE partition_test DROP PARTITION partition_test_p1
  2  /

Table altered.

SQL> SET TRANSACTION READ ONLY
  2  /

Transaction set.

SQL> SELECT *
  2  FROM   partition_test
  3  WHERE  id = 40
  4  /
FROM   partition_test
       *
ERROR at line 2:
ORA-01466: unable to read data - table definition has changed

From Oracle Support information, if a read only query if issued immediately against a table that has had DDL done on it then it will result in ORA-01466.

So, going back to the original system error, the developer of the client application confirmed that the session was put into read-only mode as it had no requirement to modify data. Sensible thinking on behalf of the developer but unfortunately it resulted in the problem. The solution was to simply run the session in standard read/write mode.

AusOUG Conference 2014

I’m on the train home after a very informative two days at the Perth AusOUG conference. It was great to hear some very useful talks by both local and international speakers, including:

If you ever have the opportunity to attend an AusOUG conference or hear any of these speakers at some other conference then I would highly recommend it! A big thanks to the conference organisers, AusOUG, the sponsors and Oracle.