Read Inconsistency?

8

Riddle me this: Can an object exist in all_objects before it is created?
Well no, of course not.
I was under the assumption that if you create a table with a select-statement, a CTAS – Create Table As Select – that the select was executed prior to the creation of the table. Read Consistency dictates that the result set returned by a Select statement, returns the values as of that particular moment in time.

....

To demonstrate this Tom Kyte has an example similar to this:

SQL&gt; var rc refcursor<br />SQL&gt; create table t<br />  2  as<br />  3  select *<br />  4    from dual<br />  5  /<br /><br />Table created.<br /><br />SQL&gt; exec open :rc for select * from t<br /><br />PL/SQL procedure successfully completed.<br /><br />SQL&gt; delete from t<br />  2  /<br /><br />1 row deleted.<br /><br />SQL&gt; commit<br />  2  /<br /><br />Commit complete.<br /><br />SQL&gt; print<br /><br />D<br />-<br />X<br /><br />SQL&gt;<br />

The shortest possible script to demonstrate Read Consistency. The result you see after the Delete followed by a Commit, are returned by the Ref Cursor, which was executed prior to the delete.

Odd or at least Remarkable?

Isn’t this odd then?

SQL&gt; drop table t purge<br />  2  /<br /><br />Table dropped.<br /><br />SQL&gt; select owner, object_name<br />  2    from all_objects<br />  3   where owner = 'ALEX'<br />  4     and object_name = 'T'<br />  5  /<br /><br />no rows selected<br /><br />SQL&gt; create table t<br />  2  as<br />  3  select *<br />  4    from all_objects<br />  5  /<br /><br />Table created.<br /><br />SQL&gt; select owner, object_name<br />  2    from t<br />  3   where owner = 'ALEX'<br />  4     and object_name = 'T'<br />  5  /<br /><br />OWNER                          OBJECT_NAME<br />------------------------------ -----------<br />ALEX                           T<br /><br />SQL&gt;<br />

How is it possible that an entry is alread present in the newly created Table T, which should be a copy of ALL_OBJECTS? Apparently, when you create a table, an entry in the datadictionary is created prior to executing the Select-statement…. strange, don’t you think?
That goes to show it, the datadictionary is "special".

Share.

About Author

8 Comments

  1. Dear Sir

    If possible would you please reply my query?

    We want to develop a web based application and we would incorporate a GUI (Graphical User Interface) in our application just like oracle 11i application UI (User interface). I would like to know that technology behind to develop oracle 11i Web User Interface (Presentation Layer) only.

    Thanks

    Sunil Gidwani
    Indore (Madhya Pradesh) – INDIA

  2. Create table is DDL; so for that part read consistency is not required. First the table is created; does that part do an implicit commit? Then the query is started read consistency starts at that point in time?

  3. Edwin van Meerendonk on

    Always nice to see these kind of mind-breakers.
    Too bad you haven’t put the
    create table B as select * from all_tables equivalent as step 1 in it.

    No data found.

    So we have an object and we have no table..

  4. Add on to that thought, if the SELECT fails, the CREATE TABLE fails and is ‘rolled back’.
    So what you are actually seeing in T is uncommited changes in the system tables underlying the data dictionary views. If the SELECT ran for long enough, and you were doing a simultaneous SELECT from all_objects in another session, the ‘T’ object shouldn’t be there.

  5. I don’t think it’s special.

    You can hardly expect the Select to execute before the table is created – where would the data go?

    I’ve always assumed the steps are:
    1. Parse
    2. Create new table
    3. Open cursor

  6. If you look at DBMS_SQL, you’ll find that BEFORE executing a query, you’ll need to FIRST define the result set (DEFINE_COLUMN). In other words, Oracle can create the table before actually executing the query, apparenlty becasue this is inherent to SQL processing.