Sokrates on Oracle

Small Read Consistency Oddity

Posted by Matthias Rogel on 20. January 2012

Here is a small oddity concerning read consistency.

I doubt it is relevant at all, however, it’s quite amusing.

Statement level read consistency guarantees that every statement sees exactly the data as of the point in time when the statement was opened.

Consider following example:

sokrates@11.2 > select table_name from user_tables where table_name = 'NOT_YET_EXISTENT';

no rows selected

Hence, we expect, that the following CTAS , which I call (T)

sokrates@11.2 > create table NOT_YET_EXISTENT
as
select table_name from user_tables where table_name = 'NOT_YET_EXISTENT';

Table created.

creates a table with no rows in it, since at the point in time when (T) started, there was no table in our schema named NOT_YET_EXISTENT.

sokrates@11.2 > select count(*) from NOT_YET_EXISTENT;

COUNT(*)
----------
0

As expected, (T) respected read consistency.

Now, let’s repeat the same experiment with one small variation.

sokrates@11.2 > drop table NOT_YET_EXISTENT;

Table dropped.

sokrates@11.2 > select object_name from user_objects where object_type = 'TABLE' and object_name = 'NOT_YET_EXISTENT';

no rows selected

So, the following CTAS called (O)

sokrates@11.2 > create table NOT_YET_EXISTENT
as
select object_name from user_objects where object_type = 'TABLE' and object_name = 'NOT_YET_EXISTENT';

Table created.

should also create an empty table.

sokrates@11.2 > select count(*) from NOT_YET_EXISTENT;

COUNT(*)
----------
1

!

Amusing, when (O) started, NOT_YET_EXISTENT was not yet existent, however, its select-part falsely returned one row (which only existed at the point in time when this SQL was finished !)

Obviously, read consistency was violated by (O).

Advertisements

2 Responses to “Small Read Consistency Oddity”

  1. I think that read consistency is not ensured for queries on data dictionary and dynamic views..
    Very interesting example. I wondered why the results are different for those 2 queries, so I looked at the plans and they are different. Execution plan of the second query contains additional union all operation (on sum$ table). It would be interesting to trace that sessions.

  2. Radoslav,

    “…I think that read consistency is not ensured for queries on data dictionary and dynamic views.. …”

    Chapter 6 “Data Dictionary and Dynamic Performance Views” of Oracle® Database Concepts 11g Release 2 (11.2),
    see
    http://docs.oracle.com/cd/E11882_01/server.112/e25789/datadict.htm
    says
    “… Dynamic performance views are based on virtual tables built from database memory structures. Thus, they are not conventional tables stored in the database. Read consistency is not guaranteed for the views because the data is updated dynamically. ”

    Hence, I expect the data dictionary to respect read consistency.

    The difference does not depend on the execution plan:
    SQL> select
    2 (select count(*) from user_tables) cot,
    3 (select count(*) from user_objects where object_type=’TABLE’) coo
    4 from dual;

    COT COO
    ———- ———-
    0 0

    SQL> create table t
    2 as
    3 select
    4 (select count(*) from user_tables) cot,
    5 (select count(*) from user_objects where object_type=’TABLE’) coo
    6 from dual;

    Table created.

    SQL> select * from t;

    COT COO
    ———- ———-
    0 1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: