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…
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
Comment by laurentschneider — July 15, 2008 @ 5:52 pm
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.
Comment by tanelp — July 15, 2008 @ 6:21 pm
DUAL is another special case btw, when you select from it when DB is closed, then internally the name is converted to X$DUAL..
Comment by tanelp — July 15, 2008 @ 6:22 pm
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
Comment by laurentschneider — July 16, 2008 @ 1:13 pm
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.
Comment by tanelp — July 16, 2008 @ 3:02 pm
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;
Comment by Nilendu — July 18, 2008 @ 1:21 pm
Oops! I mean –
SQL> with subquery as (select /*+ inline */ ‘blah’ x from dual) select * from subquery;
Comment by Nilendu — July 18, 2008 @ 1:24 pm
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.
Comment by tanelp — July 22, 2008 @ 10:27 pm