Oracle Prequel

April 1, 2018

Oracle Spokesperson April Fulz released the following brief statement today:

The Oracle database has undergone many changes to it’s release numbering scheme over the years that caused an undue amount of confusion. To alleviate the confusion we have decided to go right back to the beginning with Release 1.0.

You may know that we skipped release 1.0 in the early days for fear that nobody would trust the 1.0 release of any software. This was just the first rule of our release numbering scheme. Other highlights include:

  • Using incremental whole numbers for each release to denote forward movement and added features
  • Offset the intimidation of whole number increments by including decimal points
  • Append decrimental lowercase letters (i, g, c) to further offset the perceived complexity (thankfully industry trends such as Internet [i] and Cloud [c] arose to make the chosen letters more relevant)
  • The years between 13 and 17 were awkward for a lot of us so we planned to skip those release numbers and go directly to 18 (thankfully the year 2018 arrived just in time for this)
  • Just like the great movie franchises we always planned a prequel release of our flagship Database that we are now proudly branding Oracle RDBMS 1.0

More information will be made available closer to the final release of…the first release.

JSON Index Relies On Left Side Of Criteria

This post further explains how JSON queries and indexes interact with the Oracle optimizer.

The code shown below relies on the indexes created in the previous post here.

Here’s a quick summary of the indexes:

JSON_4
JSON_VALUE("JSON_COL" FORMAT JSON , '$.col'
   RETURNING VARCHAR2(4)...

JSON_5
JSON_VALUE("JSON_COL" FORMAT JSON , '$.col'
   RETURNING VARCHAR2(5)..

JSON_6
JSON_VALUE("JSON_COL" FORMAT JSON , '$.col'
   RETURNING VARCHAR2(6)...

And now for some code!

SQL> --
SQL> -- Oracle uses the JSON_4 index for this query because
SQL> -- it matches the left side of the criteria even though
SQL> -- it will not find return the row with val100
SQL> --
SQL> EXPLAIN PLAN FOR
 2   SELECT JSON_VALUE(jd.json_col,'$.col' )
 3     FROM json_data jd
 4    WHERE JSON_VALUE(jd.json_col,'$.col'
 5                     RETURNING VARCHAR2(4) ) = 'val100';
Explained.

SQL> SELECT *
 2     FROM TABLE(DBMS_XPLAN.DISPLAY)
 3    WHERE TRIM(plan_table_output) IS NOT NULL;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2835480143
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2006 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| JSON_DATA | 1 | 2006 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | JSON_4 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 2 - access(JSON_VALUE("JSON_COL" FORMAT JSON , '$.col'
            RETURNING VARCHAR2(4) NULL ON ERROR)='val100')
SQL> --
SQL> -- Running the query does not find return the row
SQL> --
SQL> SELECT JSON_VALUE(jd.json_col,'$.col' )
 2     FROM json_data jd
 3    WHERE JSON_VALUE(jd.json_col,'$.col'
 4                     RETURNING VARCHAR2(4) ) = 'val100';
no rows selected

SQL> --
SQL> -- Another example - Oracle uses the JSON_6 index even
SQL> -- though the JSON_4 index MIGHT be better suited
SQL> -- to finding the val9 row
SQL> --
SQL> EXPLAIN PLAN FOR
 2   SELECT JSON_VALUE(jd.json_col,'$.col' )
 3     FROM json_data jd
 4    WHERE JSON_VALUE(jd.json_col,'$.col'
 5                     RETURNING VARCHAR2(6) ) = 'val9';
Explained.

SQL> SELECT *
 2     FROM TABLE(DBMS_XPLAN.DISPLAY)
 3    WHERE TRIM(plan_table_output) IS NOT NULL;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 32193117
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2007 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| JSON_DATA | 1 | 2007 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | JSON_6 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 2 - access(JSON_VALUE("JSON_COL" FORMAT JSON , '$.col'
            RETURNING VARCHAR2(6) NULL ON ERROR)='val9')

SQL> --
SQL> -- Running the query DOES find the row
SQL> --
SQL> SELECT JSON_VALUE(jd.json_col,'$.col' )
 2     FROM json_data jd
 3    WHERE JSON_VALUE(jd.json_col,'$.col'
 4          RETURNING VARCHAR2(6) ) = 'val9';

JSON_VALUE(JD.JSON_COL,'$.COL')
--------------------------------------------------------
val9

If this was not JSON data then the Oracle optimizer would switch to another index or a full table scan in order to find the row that satisfied the criteria. But JSON is different as the examples show.

In the next post I’ll show some final examples to tie it all together.

Thanks for reading!

Explicit Cursors For Better Program Control

Arrgh! I just spent several days working through a bug in a large (1000+ line) unfamiliar package that was raising unexpected NO DATA FOUND errors.

After working through several calls to the procedure and monitoring the results I isolated the problem to code very similar to the following.

CREATE OR REPLACE PROCEDURE abc AS
  v_a VARCHAR2(1);

    PROCEDURE def IS
      v_b VARCHAR2(1);
    BEGIN
      SELECT NULL
        INTO v_b
        FROM another_table
       WHERE 1 = 2;
   END;

BEGIN

  -- get row from a_table
   BEGIN
     SELECT NULL
       INTO v_a
       FROM a_table
      WHERE 1 = 2;
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
     -- if row not found then run def procedure
     def;
   END;

END;
/

A simple piece of code right? Nope. The call to the DEF procedure in the EXCEPTION handler was raising the NO DATA FOUND error which was not handled.

This caused the calling code to fail.

This brings up one of the things I don’t like to use in PL/SQL – implicit cursors. I agree they are simpler to type than explicit ones but when their associated EXCEPTION handlers are used for┬áprogram flow it can quickly get unruly. I much prefer explicit cursors such as in this example:

CREATE OR REPLACE PROCEDURE abc AS

  v_a VARCHAR2(1);

  CURSOR curs_a IS
  SELECT NULL
    FROM a_table
   WHERE 1 = 2;

  CURSOR c_another IS
  SELECT NULL
    FROM another_table
   WHERE 1 = 2;

    PROCEDURE def IS
      v_b VARCHAR2(1);
    BEGIN
      OPEN c_another;
      FETCH c_another INTO v_b;
      CLOSE c_another;
    END;

BEGIN

  -- self documenting!?
  -- easy to follow!
  OPEN curs_a;
  FETCH curs_a INTO v_a;
  IF curs_a%NOTFOUND THEN
    def;
  END IF;
  CLOSE curs_a;

END;

Alright that’s enough ranting for today.

Thanks for reading.