Re: Materialize hint

SQL> with myrows as (select rownum i from all_objects@p1 where rownum < 10)
  2  select a.*,b.* from myrows a,myrows b;

         I          I
---------- ----------
         1          1
         2          1
         3          1
         4          1
         5          1
         6          1
         7          1
         8          1
         9          1
         1          2
         2          2

...

SQL> select * from v$version;

BANNER
---------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE    9.2.0.6.0       Production
TNS for Solaris: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production


--- jaromir nemec <jaromir@xxxxxxxxxxxx> wrote:

> Hi,
> 
> > In reverse order, the "with" clause  names
> > and defines a subquery before its use in
> > a query - a bit like a macro in C.
> 
> 
> 
> I'd see very valuable a constellation with "*distributed* subquery
> factoring". I found no restriction on this in documentation, but have no
> luck using it (on 9.2.0.5)
> 
> 
> 
> 
> 
> SQL> with myrows as (select rownum i from dba_objects@win where rownum <
> 1000)
> 
>   2  select a.*,b.* from myrows a,myrows b;
> 
> with myrows as (select rownum i from dba_objects@win where rownum < 1000)
> 
>                                                     *
> 
> ERROR at line 1:
> 
> ORA-00604: error occurred at recursive SQL level 1
> 
> ORA-00933: SQL command not properly ended
> 
> 
> 
> Is it a bug or a restriction?
> 
> 
> 
> Regards
> 
> 
> 
> 
> 
> Jaromir D.B. Nemec
> 
> http://www.db-nemec.com
> 
> ----- Original Message ----- 
> From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
> To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
> Sent: Monday, December 27, 2004 6:41 PM
> Subject: Re: Materialize hint
> 
> 
> 
> 
> --
> http://www.freelists.org/webpage/oracle-l
> 


=====
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
ISBN: 1590592174

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@xxxxxxxxx

Coming Soon! "Oracle Insight - Tales of the OakTable"

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, 
and...he will sit in a boat and drink beer all day"

------------------------------------------------------------

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 
--
http://www.freelists.org/webpage/oracle-l

Other related posts: