Re: Materialize hint

From: Connor McDonald <hamcdc_at_yahoo.co.uk>
Date: Thu, 30 Dec 2004 01:15:04 +0000 (GMT)
Message-ID: <20041230011504.79902.qmail@web86901.mail.ukl.yahoo.com>

SQL> with myrows as (select rownum i from all_objects_at_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_at_db-nemec.com> 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_at_win where rownum <
> 1000)
>
> 2 select a.*,b.* from myrows a,myrows b;
>
> with myrows as (select rownum i from dba_objects_at_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_at_jlcomp.demon.co.uk>
> To: "oracle-l" <oracle-l_at_freelists.org>
> 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_at_yahoo.com

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

Received on Wed Dec 29 2004 - 19:10:12 CST