May | 2016 | Mark Hoxey

SQL>CREATE TABLE table_a 2 (val NUMBER(38,0)) 3 / Table created. SQL>CREATE TABLE table_b 2 (val NUMBER(39,0)) 3 / (val NUMBER(39,0)) * ERROR at line 2: ORA-01727: numeric precision specifier is out of range (1 to 38)

SQL>CREATE TABLE table_c 2 (val NUMBER) 3 / Table created. SQL>INSERT INTO table_c 2 VALUES (99999999999999999999999999999999999999) 3 / 1 row created. SQL>INSERT INTO table_c 2 VALUES (999999999999999999999999999999999999999) 3 / 1 row created. SQL>INSERT INTO table_c 2 VALUES (9999999999999999999999999999999999999999) 3 / 1 row created. SQL>INSERT INTO table_c 2 VALUES (99999999999999999999999999999999999999999) 3 / 1 row created. SQL>INSERT INTO table_c 2 VALUES (999999999999999999999999999999999999999999) 3 / 1 row created. SQL>COMMIT 2 / Commit complete.

SQL>COLUMN VAL FORMAT 9999999999999999999999999999999999999999999 SQL>SELECT val 2 , LENGTH(TO_CHAR(val)) AS length_val 3 FROM table_c 4 / VAL LENGTH_VAL -------------------------------------------- ---------- 99999999999999999999999999999999999999 38 999999999999999999999999999999999999999 39 9999999999999999999999999999999999999999 40 100000000000000000000000000000000000000000 40 1000000000000000000000000000000000000000000 40

Following on from some recent questions this post goes back to basics with regard to what Oracle terms “multi-versioning read consistency”. I have posted on this subject before but as it’s so critical to how applications using Oracle will behave it’s worth another post, with emphasis on a very important point at the end…

“Multi-versioning read consistency” might appear to be just a rather grand sounding name (or another bit of techno-jargon) so here’s a brief outline of what it means:

The data reurned by a query is based on what the data in the underlying tables contained when the query commenced
By extension of the above point, the query is not impacted by changes to the table data over the time taken to execute and retrieve the query data

It’s time for an example to illustrate this. First we’ll set up a simple table, TAB, and insert a single row:

CREATE TABLE tab
   (id  NUMBER(6) NOT NULL)
/

INSERT INTO tab
VALUES (1);

COMMIT;

Next we’ll open a cursor that returns the data in TAB but we won’t actually retrieve the data yet.

VARIABLE rc1 REFCURSOR

BEGIN
   OPEN :rc1
   FOR
   SELECT *
   FROM   tab
   ORDER  BY id;
END;
/

Let’s head back to our table and do some data changes. We’ll do these changes as autonomous transactions so as to simulate an external process coming in and making these changes, removed from the session with the open cursor:

DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   UPDATE tab
   SET id = 2;

   INSERT INTO tab
   VALUES (2);

   COMMIT;
END;
/

… and once again open a new cursor onto our table:

VARIABLE rc2 REFCURSOR

BEGIN
   OPEN :rc2
   FOR
   SELECT *
   FROM   tab
   ORDER  BY id;
END;
/

Lastly, just to reinforce things, we’ll do the whole thing yet again followed by a last update:

DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   UPDATE tab
   SET id = 3;

   INSERT INTO tab
   VALUES (3);

   COMMIT;
END;
/

VARIABLE rc3 REFCURSOR

BEGIN
   OPEN :rc3
   FOR
   SELECT *
   FROM   tab
   ORDER  BY id;
END;
/

DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   UPDATE tab
   SET id = 4;

   INSERT INTO tab
   VALUES (4);

   COMMIT;
END;
/

At the end of all this we have performed 4 inserts and 3 updates to our data. If we display the contents of our table as it exists at the end of the changes we get:

SQL> SELECT *
  2  FROM   tab
  3  ORDER BY id;

        ID
----------
         4
         4
         4
         4

Now let’s retrieve the data from our cursors and see what they contain:

SQL> PRINT rc1

        ID
----------
         1

SQL> PRINT rc2

        ID
----------
         2
         2

SQL> PRINT rc3

        ID
----------
         3
         3
         3

Even though the data in the table had changed and was committed after we opened the cursors Oracle still returned the data as it was at the point in time we opened the cursor, not what the table contained when we read from the cursor. This is Oracle’s multi-versioning read consistency in action.

Now for that very important point that I mentioned at the start…

The above exercise also highlights another aspect of Oracle:

Readers and writers don’t block each other.

If you look closely at the example, whilst we were holding open cursors to the table we were still able to update existing rows as well as insert new ones. No locks on the table data were being held by the cursors to prevent this… yet the data returned by the cursors were still as the table looked when the cursor was opened. This is a fundamental of how Oracle works. If you want to build a system that is performant under high levels of concurrent activity this is exactly the behaviour you will require.

Mark Hoxey

Musings on Oracle Database Development

Monthly Archives: May 2016

Maximum NUMBER Precision

Multi-versioning read consistency… again