Closed database and WITH subquery

Here’s an interesting issue I found when running a query using WITH subquery factoring when database was not open (it was in NOMOUNT mode in current case).

As you probably know you can query DUAL table when database is not open, but in this case the actual query is made against X$DUAL as seen below:

SQL> select * from dual;

ADDR           INDX    INST_ID DUM
-------- ---------- ---------- ---
051ED14C          0          1 X

SQL>

When you have above fields when querying from DUAL then you know your database is probably not open.

So lets select something from dual:

SQL> select 'blah' x from dual;

X
------------
blah

It works.

Now lets run an equivalent query using subquery factoring:

SQL> with subquery as (select 'blah' x from dual) select * from subquery;
with subquery as (select 'blah' x from dual) select * from subquery
                                                           *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

Hmm, even though I’m really accessing the same X$DUAL table which worked ok just before, I can’t run that query.

I used to think that this kind of checking is done at database object level, so that when query would have resolved to base objects properly, Oracle would have realized it needs to access DUAL only and there is no such physical table like “subquery”. However, by now I realize that one can’t do database object level checking when database is closed as there is no means to access OBJ$ table itself. Chicken and egg problem (which is why the bootstrap segment exists btw).

Anyway, I decided to do a little test and to my surprise it worked!

SQL> with v$instance as (select 'blah' x from dual) select * from v$instance;

X
------------
blah

SQL>
 

Note that I am not selecting from v$instance v$ view but I just name my subquery alias to string “v$instance”! And apparently Oracle query execution engine is fine with it, as long as you select from an “object” which name matches one of the hardcoded ones…

This entry was posted in Cool stuff, Oracle and tagged . Bookmark the permalink.

10 Responses to Closed database and WITH subquery

  1. Fun, but it works only in one direction :mrgreen:

    with dual as (select 'x' from v$instance) select * from dual;
    with dual as (select 'x' from v$instance) select * from dual
    *
    ERROR at line 1:
    ORA-32035: unreferenced query name defined in WITH clause

  2. tanelp says:

    yep, as DUAL table name is not in the hardcoded fixed table list. If you replace DUAL with X$DUAL in your query, it works.

  3. tanelp says:

    DUAL is another special case btw, when you select from it when DB is closed, then internally the name is converted to X$DUAL..

  4. yes, quite special :)

    SQL> with x$dual as (select ’1′ from dual) select * from x$dual;
    ORA-32031: illegal reference of a query name in WITH clause

  5. tanelp says:

    Yep it looks like the DUAL -> X$DUAL replacement is done only in the main query body and not in the factored subquery.

    This for example works:

    SQL> with x$dual as (select 1 x from v$instance) select * from x$dual;

    X
    ———-
    1

    These two work ok too:

    SQL> select * from (select * from dual);

    ADDR INDX INST_ID D
    ——– ———- ———- -
    0C60FA34 0 1 X

    SQL> select * from dual where dummy in (select dummy from dual);

    ADDR INDX INST_ID D
    ——– ———- ———- -
    0C60FA34 0 1 X

    So it looks like this issue is specific to subquery factoring.

  6. Nilendu says:

    This is because Oracle is trying to build a global temporary table with database still not open.

    Try this hint, this should work –

    SQL> with /*+ inline */ subquery as (select ‘blah’ x from dual) select * from subquery;

  7. Nilendu says:

    Oops! I mean –

    SQL> with subquery as (select /*+ inline */ ‘blah’ x from dual) select * from subquery;

  8. tanelp says:

    Hi Nilendu,

    Did you test out your claims first?

    SQL> with subquery as (select /*+ inline */ ‘blah’ x from dual) select * from subquery;
    with subquery as (select /*+ inline */ ‘blah’ x from dual) select * from subquery
    *
    ERROR at line 1:
    ORA-01219: database not open: queries allowed on fixed tables/views only

    This error is not due view materialization into GTT, if it was the error message would have been different as well.

  9. hashim says:

    with subquery as (select /*+ inline */ ‘blah’ x from dual) select * from subquery;

  10. Tanel Poder says:

    that query doesnt work as shown above

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>