Hi Tom,

Thanks very much for your fast response. This problem had a few of us struggling for a sensible solution.

I've embedded your solution into my demo insert statement like this:

INSERT INTO t (seqno, a, b)
SELECT LEVEL + 1, a, b
FROM dual, t
CONNECT BY LEVEL <= &N

and it works really well. I'm pleased that it is so speedy too - even for large N!

I must admit that I was quite surprised not to find any existing asktom questions similar to my own.

Anyway, thanks again,

Tom

I am getting some wierd results on your solution above. I am there is a rational explanation for it.

1. In Sql*Plus (9.2.0.4 EE):

SQL> select *
  2  from (select level from dual connect by level < 10);

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

9 rows selected.

SQL> select level from dual connect by level < 10;

     LEVEL
----------
         1

Why do these 2 SQl's return different results in SQL*Plus ?
In TOAD both the SQl's above return 9 rows.

2. In TOAD, the SQL 'select level from dual connect by level < :N' returns max of 250 rows even if N > 251, but the SQL 'select *
from (select level from dual connect by level < :N)' returns N-1 rows in TOAD. Why does the first SQL return max of 250 rows ?

3. Also where does it say in the documentation that connect by need not have a prior clause ? 
Got this from the Oracle 9i SQL reference manual:
'CONNECT BY Clause
Specify a condition that identifies the relationship between parent rows and child rows of the hierarchy. The connect_by_condition can be any condition as described in Chapter 6, "Conditions". However, it must use the PRIOR operator to refer to the parent row.'. 
The above excerpt does seem to indicate that the PRIOR operator is required.

4. Is there a performance downside to using this technique for generating 1..N-1 rows ?

5. Also what is the logic behing this query ? Is it that the absence of a prior clause makes every row (in this case 1) a child row of any other row in a cascading fashion.
 

I'd add that it doesn't work under 8i at all.

For those without 10g: we discussed this elsewhere - with some timing data included (thanks padders). </code> http://www.dbasupport.com/forums/showthread.php?s=&threadid=44529 <code>

On Tom's comment 'sqlplus does that -- that is why I "hid" dual in the inline view'

1. Shouldn't this be classified as a bug in SQL*Plus ?
2. Just curious - How does this happen in SQL*Plus ?
Does SQL*Plus assume that there will at most be only one row of output (because the from clause contains only dual) and hence does not attempt to display beyond the first row.

Tom said: "no clue on toad."

Judging by the error messages I get in 8i, TOAD is taking the given query as an in-line view (I guess something to do with how it displays the results). Hence "hiding" it?

and you forgot to say

thank you to mikito harakiri for this nice query

or am I wrong ?

for example,  I've done it in talks as well (eg: reference 

I just love those little tricks.
(even when I don't understand why they work)

As to the toad thing:
View -> Options -> Schema Browser -> Data -> Limit grid fetch -> checkbox&number.
One of the reasons trying to casually test timing in toad can be REALLY inaccurate. ;)

In my TOAD settings:
View -> Options -> Schema Browser -> Data -> Limit grid fetch -> <IS BLANK>
hence no limit on the number of rows fetched.

Also as I mentioned earlier, the sql
'select * from (select level from dual connect by level < :N)' displays N-1 rows in TOAD even with N set to as high as 10,000.
So it is not the above mentioned option in TOAD that is causing this limit of 250 rows for the sql 'select level from dual connect by level < :N'

SQL> select level from dual connect by level < 10;

     LEVEL
----------
         1

"2) sqlplus saw "dual" -- not sure of the details, just know it happened. "

That doesn't appear to be the case. Firstly, SQL*Plus sees dual but shouldn't assume that it is the 'magic' SYS.DUAL that one has one row.
Secondly,
SQL> create view y as select * from dual;

View created.

SQL> select level from y connect by level < 10;

     LEVEL
----------
         1

It suggests that this is happening at the server level, which explains why TOAD and others suffer a similar issue (I get a maximum of 25 rows in TOAD, 20 in PL/SQL Developer, 20 in Forms if I generate a recordgroup on that query - anyone game to try it in Pro*C ? PL/SQL behaviour shown below for a 9.2.0.4 database). 

Setting ARRAYSIZE in SQL*Plus doesn't seem to affect it, but I suspect it is something along the lines of the client requests the first 'batch' and the server returns it, and when the client requests the next batch the server is returning no_data_found (possibly because, as a select from dual involving no other tables, it thinks any subsequent request must be a no_data_found).

Given that there is a quirky bug in this feature, I'd be wary about using in production code (ie. The performance benefits would have to be significant, and I'd want to test it whenever a patch is applied).

Final test in PL/SQL

SQL> l
  1  DECLARE
  2    TYPE tab_num IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  3    t_num tab_num;
  4    CURSOR c_1 IS
  5    SELECT LEVEL FROM dual CONNECT BY LEVEL < 1000;
  6  BEGIN
  7    dbms_output.ENABLE(100000);
  8    SELECT LEVEL
  9    BULK COLLECT INTO t_num
 10    FROM dual CONNECT BY LEVEL < 1000;
 11    dbms_output.put_line(t_num.COUNT);
 12    OPEN c_1;
 13    FETCH c_1 BULK COLLECT INTO t_num LIMIT 750;
 14    dbms_output.put_line(t_num.COUNT);
 15    FETCH c_1 BULK COLLECT INTO t_num LIMIT 500;
 16    dbms_output.put_line(t_num.COUNT);
 17    CLOSE c_1;
 18* END;
SQL> /
999
750
0

 

'sqlplus does that -- that is why I "hid" dual in the inline view'

so this is clearly a bug
of course we are not sure of the details
as we haven't the sources

another argument to join the club voting that sqlplus should be renamed into sqlminus

to Gary:
since Oracle offers - always offered - great APIs (OCI, JDBC, ...) for accessing SQL it is easy to write sql-tools better than the delivered so-called "sqlplus"
I mainly use my own tool.

with that (currently my preferred one is written in Delphi using DOA, built on OCI),
select level from dual connect by level < 10
gives 9 rows as it should
my 'sqlplus' version on the same machine against the same DB
gives only one row

Is it because I'm patched or just not doing it right, but it seems OK for me in all versions of SQL*Plus...

SQL*Plus: Release 8.1.7.0.0 - Production on Tue May 10 12:38:52 2005

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

SQL> connect /
Connected.
SQL> select level from dual connect by level < 10;

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

9 rows selected.

SQL*Plus: Release 9.2.0.6.0 - Production on Tue May 10 12:39:39 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect /
Connected.
SQL> select level from dual connect by level < 10;

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

9 rows selected.

SQL*Plus: Release 10.1.0.4.0 - Production on Tue May 10 12:40:01 2005

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> connect /
Connected.
SQL> select level from dual connect by level < 10;

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

9 rows selected.

SQL*Plus: Release 10.2.0.0.0 - Beta on Tue May 10 12:40:20 2005

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL> connect /
Connected.
SQL> select level from dual connect by level < 10;

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

9 rows selected. 

SQL*Plus: Release 9.2.0.6.0 - Production on Tue May 10 15:51:29 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> select level from dual connect by level < 10;

     LEVEL
----------
         1

My 9.2.0.6 results are different than yours? 

I did a 'connect /' in each version of SQL*Plus and my current default database on my laptop (which is Windows XP Pro) is a 10g R2 beta database. If I connect to a 10g R1 or a 10g R2 beta database, I get the results as posted. If I connect to a 9.2.0.6.0 or 8.1.7.4.19 database (the other 2 databases on my laptop) I get:

SQL> select level from dual connect by level < 10;

     LEVEL
----------
         1

In reading the posts I was thinking the version of SQL*Plus is what mattered. Using any version of SQL*Plus connecting to any 10g database works. Using any version of SQL*Plus connecting to any version prior to 10g produces the result above.

- Mark 

"Using any version of SQL*Plus connecting to any 10g database works. Using any version of SQL*Plus connecting to any version prior to 10g produces the result above"

So, doesnt this mean that SQL*Plus is innocent? [We seem to have said earlier on in this thread that sqlplus is the buggy component and a bug has been filed, etc].

Turns out it is the database itself that has the bug and it is fixed in 10g?

Thanks

Try:
select * from (select level from dual connect by level < level+1);

;-)

Possibly that's why there isn't a formalized "IDUAL"
leaving us with strange looking but practical
all_objects queries. ;)

PS To Jay
I found a 10g database to connect to here and toad
8.0.0.47 returns the correct number of rows using
the 8i drivers on my pc.
Don't know why your limit is 250.

Tom,
Could this be achieved using an infinite or large loop in a pipelined function? What would be the benefits or drawbacks of doing so compared with this?
Thanks.

<quote areader>
So, doesnt this mean that SQL*Plus is innocent? [We seem to have said earlier on 
in this thread that sqlplus is the buggy component and a bug has been filed, 
etc].

Turns out it is the database itself that has the bug and it is fixed in 10g?
</quote>

<quote tom>
could be ...
</quote>

no, as I demonstrate

$ sqlminus xxxxx/yyyyy

SQL*Plus: Release 9.2.0.5.0 - Production on Thu May 12 11:54:03 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

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 Linux: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production


SQL> create or replace and compile java source named sqlsocalledplus_is_not_innocent as
  2  import java.sql.*;
  3
  4  public class t
  5  {
  6     public static void testit() throws SQLException
  7     {
  8        Connection conn = (new oracle.jdbc.OracleDriver()).defaultConnection();
  9
 10        Statement stmt = conn.createStatement();
 11        ResultSet rset = stmt.executeQuery("select level from dual connect by level < 10");
 12
 13        while (rset.next())
 14           System.out.println(rset.getInt(1));
 15
 16        rset.close();
 17        stmt.close();
 18
 19     }
 20  }
 21  /

Java wurde erstellt.

SQL> create or replace procedure testit as language java name 't.testit()';
  2  /

Prozedur wurde angelegt.

SQL> set serverout on size 999999
SQL> exec dbms_java.set_output(999999)

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

SQL> exec testit
1
2
3
4
5
6
7
8
9

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

SQL> select level from dual connect by level < 10;

     LEVEL
----------
         1


q.e.d. 

You've demonstrated that SQL*Plus has an issue when used against a 9.2.0.5.0 database. My results using a 9.2.0.6.0 database show the same. However, when using the same 9i version of SQL*Plus against a 10g database all works as expected:

C:\oracle\9.2\bin>sqlplus /nolog

SQL*Plus: Release 9.2.0.6.0 - Production on Thu May 12 09:13:25 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect /@lt9i
Connected.
SQL> select level from dual connect by level < 10;

     LEVEL
----------
         1

1 row selected.

SQL> connect /@lt10g
Connected.
SQL> select level from dual connect by level < 10;

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

9 rows selected.

SQL>

I'm not trying to convince anyone to use any particular tool and so forth, however, in the interest of disclosure, perhaps I should say I like and use SQL*Plus. Rather than that, what I am trying to do here is understand what the real underlying issue is.

In lieu of running your Java test, I did this and it works as well:

C:\oracle\9.2\bin>sqlplus /nolog

SQL*Plus: Release 9.2.0.6.0 - Production on Thu May 12 09:17:47 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> connect /@lt9i
Connected.
SQL> variable v_rc refcursor
SQL> exec open :v_rc for select level l from dual connect by level < 10;

PL/SQL procedure successfully completed.

SQL> print v_rc

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

9 rows selected.

SQL>

I am having difficulty accepting that it is a pure SQL*Plus issue when I can run the same statement against 10g with no errors at all using the 9i version of SQL*Plus. I am also not convinced that it is a pure OCI issue since the same OCI libraries would have been used with the 9i and 10g database.

- Mark 

Thank you.

I am convinced it is not an OCI issue since I have a
program using OCI where the query is working, whereas
using sqlplus on the same machine against the same database
it is not working. (as i mentioned before)

you convinced me that it is not a pure SQL*Plus issue

so, all in all
something magic seems to go on here (?)

(sorry, as much as I like this site - it is really one of
my favourites in www - as much do I hate it when Tom
starts using this word "magic"
nothing is magic
it is all software, isn't it)

Matthias,

I agree - it is just software which is why I'd like to know just what is happening. Here is some interesting information perhaps:

select level l from dual connect by level < 10;

Produces:

select level l
from
dual connect by level < 10


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 7 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 7 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 62

Rows Row Source Operation
------- ---------------------------------------------------
1 CONNECT BY WITH FILTERING
1 NESTED LOOPS
1 TABLE ACCESS FULL DUAL
1 TABLE ACCESS BY USER ROWID DUAL
1 NESTED LOOPS
1 BUFFER SORT
1 CONNECT BY PUMP
1 FILTER
1 TABLE ACCESS FULL DUAL

In a tkprof.

variable v_rc refcursor
exec open :v_rc for select level l from dual connect by level < 10;

Produces:

SELECT LEVEL L
FROM
DUAL CONNECT BY LEVEL < 10


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 7 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 7 0 9

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 62 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
9 CONNECT BY WITH FILTERING
1 NESTED LOOPS
1 TABLE ACCESS FULL DUAL
1 TABLE ACCESS BY USER ROWID DUAL
1 NESTED LOOPS
1 BUFFER SORT
1 CONNECT BY PUMP
1 FILTER
1 TABLE ACCESS FULL DUAL

The "CONNECT BY WITH FILTERING" step is producing a different number of rows with a "raw" statement versus a ref cursor... hmm... More to investigate...

- Mark

...and thanks to the pointer to the doc.

- Mark

"(sorry, as much as I like this site - it is really one of
my favourites in www - as much do I hate it when Tom
starts using this word "magic"
nothing is magic it is all software, isn't it)"

- It would be a poor world without any magic.

- If you've not soldered NAND-gates, seen a "bit" on an oscilloscope and measured cable lengths in nano-seconds, then software is magic. Come to think of it, if you haven't studied quantum mechanics and solid-state physics, NAND-gates are magic too.

And in this regards, it is magic in the way

o sysdba or internal is magic.  try set transaction read only as those guys and see what magic happens (don't do anything rash here -- do a simple test on a test machine)

o dba is magic. there are things that happen quite simply because the role dba is named dba.  create a role with everything dba has and you still won't be DBA.

o dual is magic.


ops$tkyte@ORA9IR2> select dummy, count(*) from dual group by dummy;
 
D   COUNT(*)
- ----------
X          1
 
ops$tkyte@ORA9IR2> insert into dual values ( 'Y' );
 
1 row created.
 
ops$tkyte@ORA9IR2> select dummy, count(*) from dual group by dummy;
 
D   COUNT(*)
- ----------
X          1
 
ops$tkyte@ORA9IR2> select count(*) from dual;
 
  COUNT(*)
----------
         2
 
ops$tkyte@ORA9IR2> select * from dual;
 
D
-
X
 
ops$tkyte@ORA9IR2> select * from (select * from dual );
 
D
-
X
 
ops$tkyte@ORA9IR2> select * from (select * from dual where rownum > 0 );
 
D
-
X
Y
 
ops$tkyte@ORA9IR2>


there are bits that "just know" dual returns a single row and they stop -- unless we prevent the mashing of dual into the outer query (which rownum did)

 
ops$tkyte@ORA9IR2> select * from (select dummy, count(*) from dual group by dummy );
 
D   COUNT(*)
- ----------
X          1
 
ops$tkyte@ORA9IR2> select * from (select /*+ NO_MERGE */ dummy, count(*) from dual group by dummy );
 
D   COUNT(*)
- ----------
X          1
Y          1
 
ops$tkyte@ORA9IR2>


 

<quote>
Tom,
Could this be achieved using an infinite or large loop in a pipelined function?
What would be the benefits or drawbacks of doing so compared with this?
Thanks.



Followup:
yes it can be.

the major drawback, for whatever reason people don't want to create the types
needed to support it.
</quote>

But then there is this issue with pipeline functions as well.. </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:4447489221109#40748093515309 <code>

magic lies in the eyes of the observer

to me, Bailey-Borwein-Plouffe is magic
for example

not software and especially not oracle software
that is nice and well done but not magic

tom: thx for pointing out 185438.1
that was what we were looking for

A. Consider this example (Oracle Server 9.2.0.4 EE, SQL*Plus 9.2.0.1):

SQL> var rc refcursor
SQL> exec open :rc for select level from dual connect by level < 20;

PL/SQL procedure successfully completed.

SQL> print rc

     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13
        14
        15

15 rows selected.

SQL> show arraysize
arraysize 15
SQL> set arraysize 25
SQL> exec open :rc for select level from dual connect by level < 20;

PL/SQL procedure successfully completed.

SQL> print rc

     LEVEL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19

19 rows selected.

So using refcursors passed back to SQL*Plus, the number of rows returned depends on SQL*Plus arraysize parameter.
So is this a SQL*Plus or Oracle Server bug ?

B. Consider another example (Oracle Server 9.2.0.4 EE, SQL*Plus 9.2.0.1):

SQL> declare 
  2   cnt number := 0;
  3  begin
  4    for i in (select level from dual connect by level < 102) loop
  5      cnt := cnt + 1;
  6    end loop;
  7    dbms_output.put_line('Loop counter: '||cnt);
  8  end;
  9  /
Loop counter: 1

PL/SQL procedure successfully completed.

So anonymous PL/SQL blocks with cursor FOR loops, return only one row. Point to note here is that PL/SQL cursor FOR loops fetch 1 row at a time in 9i (as opposed to 100 rows at a time in 10g). So the arraysize here is 1.

C. In Gary's test (a few posts above), in an anonymous PL/SQL block the second 'fetch bulk collect'  did not return any rows. In this case the arraysize is specified as: FETCH <cursor> BULK COLLECT INTO <collection> LIMIT [arraysize];

So in all 3 cases, when the client is either SQL*Plus or PL/SQL, explicit cursors (i.e. ref cursors or (loosely) PL/SQL cursor FOR loops or named cursors in PL/SQL) only return a max of [arraysize] number of rows.
Does this indicate that it is a bug in the Server and not in the client (SQL*Plus or PL/SQL) ?
 

As demonstrated in my earlier post, PL/SQL within the database using explicit cursors suffers from this problem. Does PL/SQL in the database use OCI to communicate with the SQL engine ?

Here is a version that might be less version dependent.

select rownum, level from dual
connect by prior dbms_random.value()*dbms_random.value() >= 0 and rownum < 100;

I wonder if it works in 8i.

Now TOAD (7.4.0.1) against Oracle 9.2.0.4 EE returns max of 25 rows for 'select level from dual connect by level < :N'. My tests earlier (with the same TOAD executable and same database) showed this max to be 250. Not sure why this is fluctuating. Another one to be classified "magic" I guess.

C:\>cd \oracle\8.1\bin

C:\oracle\8.1\bin>sqlplus /@lt8i

SQL*Plus: Release 8.1.7.0.0 - Production on Fri May 13 14:41:05 2005

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production

SQL> select rownum, level  from dual
  2  connect by prior dbms_random.value()*dbms_random.value() >= 0  and rownum < 100;

    ROWNUM      LEVEL
---------- ----------
         1          1

1 row selected.

SQL> 

Then it's a bug (fixed in later versions by completely rewriting connect by in 9i, and further enhancing it in 10g).

The execution steps:
1. As there is no "start with" clause, take all the records from dual.
2. Consider those records as basis for prior pseudocolumn calculation. The predicate doesn't really depend on those values, but still, select all the records from dual that meet "connect by" criteria. As the predicate evaluates to true, there should be one more record!
3 Continue on.

now we know:
when Tom says "magic" he means "buggy"

with n as (
select level+1 l from dual connect by level <= :bound
)
select l as prime from n
minus
select n1.l * n2.l
from n n1, n n2

?

[
returns primes up to :bound,
coincidentally ordered naturally
]

(funny, it was deja vu all over again)

ops$tkyte@ORA9IR2> variable bound number
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec :bound := 1000
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.01
ops$tkyte@ORA9IR2> set timing on
ops$tkyte@ORA9IR2> set autotrace traceonly statistics;
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> with t
  2  as
  3  (select level+1 id from dual connect by level < :bound )
  4  select id from t x1
  5  where not exists
  6  (
  7  select 1 from t x2 where
  8  mod(x1.id , x2.id) = 0
  9  and x2.id>1
 10  and x2.id<ceil(x1.id/2)+1
 11  ) and x1.id<10000 and x1.id>0
 12  /
 
168 rows selected.
 
Elapsed: 00:00:00.32
 
Statistics
----------------------------------------------------------
          4  recursive calls
          7  db block gets
       3190  consistent gets
          2  physical reads
        520  redo size
       2662  bytes sent via SQL*Net to client
        620  bytes received via SQL*Net from client
         13  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
        168  rows processed
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> with n as (
  2  select level+1 l from dual connect by level <= :bound
  3  )
  4  select l as prime from n
  5  minus
  6  select n1.l * n2.l
  7  from n n1, n n2
  8  /
 
168 rows selected.
 
Elapsed: 00:00:03.24
 
Statistics
----------------------------------------------------------
          4  recursive calls
          7  db block gets
       4017  consistent gets
          2  physical reads
        520  redo size
       2665  bytes sent via SQL*Net to client
        620  bytes received via SQL*Net from client
         13  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
        168  rows processed
 
 

Cute bit of SQL.
Here's a couple of optimisation touches which I think are valid - it might be possible to improve on the sqrt() one.

with n as (
select level+1 l from dual connect by level <= :bound
)
select l as prime from n
minus
select n1.l * n2.l
from n n1, n n2
where n1.l <= n2.l
and n1.l <= (select sqrt(:bound) from dual)
/

I think generating pairs without join would be faster.

Here is exerpt from my book (shameless plug;) scheduled May next year:

Enumerating Pairs
-----------------

Enumerating pairs seems easy. Just build a Cartesian product of the two integers relations

select i1.num x, i2.num y
from Integers i1, Integers i2

With finite integers relation (bounded by an arbitrary upper limit) there shouldn’t be any surprises. What if it is infinite, can we drop the limit predicate on the server side and leverage the pipelining idea? The answer depends on a join method employed by your SQL execution engine. Since both hash join and sorted merge join materialize their argument relations, the execution plan involving any of those methods is never pipelined. Nested loops join iterates via the outer relation and for each row finds matching rows from the inner relation. In case of the Cartesian product, all the rows from the inner relation match. Therefore, the execution would be stuck scanning the inner table and would never be able to get past the first row in the outer table. In other words, the nested loop join fails to deliver pipelined set of integer pairs as well.

Let’s approach the problem from another angle. When a mathematician says “enumerating” she really means a [one-to-one ] mapping some set of objects into the integers. Figure 2 shows a nearly ubiquitous mapping of integer pairs into integers.

0,0 -> 0,1 0,2
/ /
/ /
/ /
1,0 1,1
/
/
/
2,0

Figure 1: Enumerating all the pairs of integers. Each pair is assigned an integer which is the length of path to the origin. The origin (0,0) is mapped into 0, (0,1) is mapped into 1, and so on.

Without further ado, here are the formulas for the (x,y) -> n mapping

...

and the reverse n -> (x,y) mapping

...

Translating the above formulas into SQL is straightforward

select n,n-(xplusy+1)*xplusy/2 x,
xplusy-n+(xplusy+1)*xplusy/2 y
from (
select FLOOR((SQRT(8*(rownum-1)+1)-1)/2) xplusy,
rownum-1 n from dual connect by 1=1
)
N X Y
0 0 0
1 0 1
2 1 0
3 0 2
4 1 1
5 2 0
6 0 3
7 1 2
8 2 1
9 3 0
… … …

Even though the implementation side varies from vendor to vendor, it is instructive to see the execution statistics
OPERATION OUTPUT ROWS

VIEW
10

COUNT
10

CONNECT BY
10

FAST DUAL
1

Ignoring fancy operator names in the execution statistics above, we see that the very first operator produces one row. This row performs a basis for connect by iteration that starts producing rows one-by-one. Each row is pipelined through 2 more levels of processing to the top. The client receives each row and, after getting the 10th row, loses any interest continuing further. The execution statistics is a snapshot at that moment.

---------------------------soapbox----------------------
Pipelined Operators
When executing a SQL statement RDBMS engine represents it internally as a tree of operators. Each operator performs a relatively simple task; the complexity lies in the way the operators are combined together. Each operator works as a black box. It consumes a relation as an input, massages it, and outputs the result to the other operator. If the operator is able to start outputting rows as soon as it consumed one or several rows, it is called pipelined. Pipelined operator isn’t blocking the execution flow; whereas blocking operators have to consume the whole input relation before it is able to output even a single row. In case of integer relation, blocking operator smells a disaster. The operators above would have to wait forever until blocking operator processes the entire integers list.
------------------------end soapbox----------------------

Admittedly, using the above pipelined integer pairs implementation inside other queries as inner view or subquery would only raise eyebrows. Those square root expressions are better be hidden behind a named view
create view IntegerPairs as

select n,n-(xplusy+1)*xplusy/2 x,
xplusy-n+(xplusy+1)*xplusy/2 y
from (
select FLOOR((SQRT(8*(rownum-1)+1)-1)/2) xplusy,
rownum-1 n from dual connect by 1=1
)

With this pipelining idea that we push forward in integer pairs implementation did we achieve anything at all?

Consider a query
Find positive integers X and Y satisfying both x + y = 10 and x – y = 2 equations
With IntegerPairs view the solution is immediate

select x,y from IntegerPairs
where x+y=10 and x-y=2

....

Well, i can save little buffer gets with

with IntegerPairsGE2 as (
select x, y from (
select n,n-(xplusy+1)*xplusy/2+2 x,
xplusy-n+(xplusy+1)*xplusy/2+2 y
from (
select FLOOR((SQRT(8*(rownum-1)+1)-1)/2) xplusy,
rownum-1 n from dual connect by rownum < 2000000
)
) where x*x<1000 ), IntegersGE2 as (
select rownum+1 x from dual connect by rownum < 1000
)
select x from IntegersGE2
minus
select x*y from IntegerPairsGE2;

The execution time is disastrous, however. Admittedly, I don't know if I would be able to pipeline antijoin or minus operator...

Here is a method that is able to calculate primes up to 20(!)

with IntegersGE2 as (
select rownum+1 x from dual connect by rownum < 20
), IntegerPairs as (
select x, y from (
select n,n-(xplusy+1)*xplusy/2 x,
xplusy-n+(xplusy+1)*xplusy/2 y
from (
select FLOOR((SQRT(8*(rownum-1)+1)-1)/2) xplusy,
rownum-1 n from dual connect by rownum < 80000
-- numeric overflow if > 90000
)
) where x<20
-- numeric overflow if not limiting x either
), IntegerSetsGE2 as (
select y N, x+2 i from IntegerPairs
where bitand(power(2,x),y)>0
), CompositeNumbers as (
select round(exp(sum(ln(i)))) x from IntegerSetsGE2
group by N
having count(1)>1
)
select x from IntegersGE2
minus
select x from CompositeNumbers;

Hi Tom,

Who this guy 'Mikito Harakiri' is?
It seems he got some extra brains, especially in mathematics! I need to know about him.

Could Tom or 'He' like to explain a little...

Thanks

tom, jonathan and mikito for answers

Mikito are you shy to give your educational and career details, as 'others' are........!

Assuming the requirement was to “generate primes up to and including the value of :bound” then the MINUS-based solutions are incomplete.

flip@FLOP> exec :bound := 2;

PL/SQL procedure successfully completed.

flip@FLOP> -- MR's
flip@FLOP> with n as (
2 select level+1 l from dual connect by level <= :bound
3 )
4 select l as prime from n
5 --where l <= :bound
6 minus
7 select n1.l * n2.l
8 from n n1, n n2
9 /

PRIME
----------
2
3

flip@FLOP> -- JL's
flip@FLOP> with n as (
2 select level+1 l from dual connect by level <= :bound
3 )
4 select l as prime from n
5 --where l <= :bound
6 minus
7 select n1.l * n2.l
8 from n n1, n n2
9 where n1.l <= n2.l
10 and n1.l <= (select sqrt(:bound) from dual)
11 /

PRIME
----------
2
3

flip@FLOP> -- TK's
flip@FLOP> with t as (select level+1 id from dual connect by level < :bound )
2 select id from t x1
3 where not exists
4 (
5 select 1 from t x2 where
6 mod(x1.id , x2.id) = 0
7 and x2.id>1
8 and x2.id<ceil(x1.id/2)+1
9 ) and x1.id<10000 and x1.id>0
10 /

ID
----------
2

flip@FLOP> exec :bound := 3;

PL/SQL procedure successfully completed.

flip@FLOP> -- MR's
flip@FLOP> with n as (
2 select level+1 l from dual connect by level <= :bound
3 )
4 select l as prime from n
5 --where l <= :bound
6 minus
7 select n1.l * n2.l
8 from n n1, n n2
9 /

PRIME
----------
2
3

flip@FLOP> -- JL's
flip@FLOP> with n as (
2 select level+1 l from dual connect by level <= :bound
3 )
4 select l as prime from n
5 --where l <= :bound
6 minus
7 select n1.l * n2.l
8 from n n1, n n2
9 where n1.l <= n2.l
10 and n1.l <= (select sqrt(:bound) from dual)
11 /

PRIME
----------
2
3
4

flip@FLOP> -- TK's
flip@FLOP> with t as (select level+1 id from dual connect by level < :bound )
2 select id from t x1
3 where not exists
4 (
5 select 1 from t x2 where
6 mod(x1.id , x2.id) = 0
7 and x2.id>1
8 and x2.id<ceil(x1.id/2)+1
9 ) and x1.id<10000 and x1.id>0
10 /

ID
----------
2
3

flip@FLOP> exec :bound := 10;

PL/SQL procedure successfully completed.

flip@FLOP> -- MR's
flip@FLOP> with n as (
2 select level+1 l from dual connect by level <= :bound
3 )
4 select l as prime from n
5 --where l <= :bound
6 minus
7 select n1.l * n2.l
8 from n n1, n n2
9 /

PRIME
----------
2
3
5
7
11

flip@FLOP>
flip@FLOP> -- JL's
flip@FLOP> with n as (
2 select level+1 l from dual connect by level <= :bound
3 )
4 select l as prime from n
5 --where l <= :bound
6 minus
7 select n1.l * n2.l
8 from n n1, n n2
9 where n1.l <= n2.l
10 and n1.l <= (select sqrt(:bound) from dual)
11 /

PRIME
----------
2
3
5
7
11

flip@FLOP> -- TK's
flip@FLOP> with t as (select level+1 id from dual connect by level < :bound )
2 select id from t x1
3 where not exists
4 (
5 select 1 from t x2 where
6 mod(x1.id , x2.id) = 0
7 and x2.id>1
8 and x2.id<ceil(x1.id/2)+1
9 ) and x1.id<10000 and x1.id>0
10 /

ID
----------
2
3
5
7

Uncommenting those “where l <= :bound” predicates fixes the problem.

Out of the doc, I can read :
If the CONNECT BY condition results in a loop in the hierarchy, then Oracle returns an error.
</code> http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/queries003.htm#sthref2603 <code>

Do not you think that connect by without prior rely on an undocumented mechanism?

Hi Tom,
Thanks very much for answering my question.

You said :
> This will not lead to a loop.

But the objective of this construct is to create an infinite loop (infinite dual), is not it?

Is it really legal to generate a loop in a hierarchical query?

In the doc again
</code> http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/operators004.htm#sthref970 <code>

In a hierarchical query, one expression in the CONNECT BY condition must be qualified by the PRIOR operator.

SQL> create table temp as select * from dual;

Tabelle wurde angelegt.

SQL> select level from temp connect by prior 1=1;
ERROR:
ORA-01436: CONNECT BY loop in user data



Es wurden keine Zeilen ausgewõhlt

REM BUT

SQL> select level from dual connect by prior 1=1;

     LEVEL
----------
         1


 

don't know what this tells us ... 

Hi Tom,

Ironically, I've been discussing the same issue on the other forum couple minutes ago. Some guys over there got really upset and opined this CONNECT BY behavior should be considered a bug. I mean queries like this:

select rownum from
(select null
from dual connect by 1 = 1);

Theoretically we can fetch resulting rows forever (let’s not forget about UGA/PGA consumption due to this recursion, however).

Now, what is claimed to be a bug? They asked me: what if someone decided to run a query like this one? (CAUTION for all: *DO NOT RUN IT* unless you are on a test system):

select count(*) from
(select null
from dual connect by 1 = 1);

This query will "hang" forever consuming a lot of CPU and other resources. The only way to escape now is to kill a server process.

It’s just for example: we could do a SELECT ... FROM <endless_subquery> ORDER BY ... or something else. And they claimed that since we can use it to concoct a DoS attack of some kind -- it’s therefore should be considered a bug.

Truthfully -- I failed to see a problem here. Least privileged users (CREATE SESSION privilege only) are able to inflict a DoS attack in a variety of ways: for example, run anonymous PL/SQL block with endless loop or turn CURSOR_SCHARING to EXACT and parse a huge bunch of unique senseless queries to kill shared pool and so on.

But after all, we have user profiles functionality as well as quotas, built-in audit and other stuff. So, technically I don’t see any problem here.

I don’t know what to say about documentation. Indeed, as Laurent cited:
"In a hierarchical query, one expression in the CONNECT BY condition must be qualified by the PRIOR operator." -- this can be found in Oracle 10g SQL reference.

"... MUST be qualified... "? Hmm... I would remove it.


And what do you think about it?
Thanks in advance and sorry for taking your time.

Cheers,
Andrew.

> I would remove it
I would not. What is stated in the doc should not be removed to enable users to write exotic queries

Oracle 7:
</code> http://download-uk.oracle.com/docs/cd/A57673_01/DOC/server/doc/SQL73/ch4a.htm#index2297 <code>
This condition can be any condition as defined by the syntax description of condition ; however, some part of the condition must use the PRIOR operator to refer to the parent row.
Same statement in 8.0, 8i and 9iR2.

> I shall file a documentation bug

Dear Tom,
The row generator is not documented, there is no "connect by without prior" example in the doc. And the usage of connect by without prior seems, as far as I understand, to be illegal by reading the docs 7,8,9,10...
The fact that it works -perfectly- in some oracle versions is certainly fascinating me. However, how can I be sure it will not generate a loop-error in a next release? Are you going to file a bug in metalink if it stops working in 11gR2?

In your opinion, a loop is refering to the "prior" clause, so when there is no "prior", there is no loop. But how would you name this? A hierarchical query without hierarchy, where there is no parent and no child, but with an infinite number of levels?

Thanks a lot for the followup

Matthias,

I believe what you observed is due to SQL*Plus "knowing" about dual:

SQL*Plus: Release 9.2.0.6.0 - Production on Mon Nov 7 13:08:12 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

flip@FLOP> select level from dual connect by prior 1=1;

LEVEL
----------
1

flip@FLOP> create table temp as select * from dual;

Table created.

flip@FLOP> select level from temp connect by prior 1=1;
ERROR:
ORA-01436: CONNECT BY loop in user data



no rows selected

flip@FLOP> select * from (select level from dual connect by prior 1=1);
ERROR:
ORA-01436: CONNECT BY loop in user data



no rows selected

flip@FLOP>

Laurent,

“Hierarchical Query” is a misnomer. The documentation has had, as far back as I remember, the picture of the inverted tree … at some point though, whether from the beginning or sometime later, they just lost it. What they essentially have now should be called “Directed Graph Query”.

There is a precise definition of that a Hierarchy is. Things like LEVEL, LEAF, ROOT only make sense in a Hierarchy. Things like CYCLE do not make sense in a Hierarchy … after all, a Hierarchy or Tree is a “Connected Directed ACYCLIC Graph”.

This, under the heading “Hierarchical Queries”:

<quote> If a table contains hierarchical data, then you can select rows in a hierarchical order using the hierarchical query clause …</quote>

has got to be the poorest definition ever.

Any lawyer would just toy with this and prove just about anything they wanted. For instance, there is no clear, anambigous definition of what “hierarchical data” actually is [in passing: I know, “a picture can say 1000 words” … well, it can also say 2000 or 3000 or more too … what they need here is not just a picture that can say any number of words, they need something saying a succint, yet complete “number of words”]; they don’t actually say a query would have to run against a hierarchical data set in order to qualify as “hierarchical” … only that the table should contain some ?hierarchical data? … by extension, since one can run a query against the non-hierarchical portion of the data in the table, it stands to reason that just about anything an Oracle query needs to have in order to be called “hierarchical” is a CONNECT BY clause. So, here you have it, get some non-hierarchical data, put a CONNECT BY clause, call the query “hierarchical”, throw in Levels, Leafs, Root, Cycles, Paths, Prior, Siblings and then wonder where all the confusion comes from.

Personally I don’t see how some documentation amendmants would suffice … they need an overhall because the fundamentals are clearly lacking. It is the imprecision of it all (what the feature actually is vs. what different people think/interpret it is) that makes the whole hierarchical query construct such a fertile ground for disagreements.

Gabe-- Perhaps that imprecise terminology has kept me from ever using the CONNECT BY syntax (it's on my "to learn" list). I only use it in a cookie-cutter approach to create sample data from an infinite dual, for example:
create table workflow_history
as select case mod(level,3)
when 0 then 'Delete'
when 1 then 'Add'
when 2 then 'Update'
end AS ACTION_TYPE,
sysdate-mod(level,365)-5 AS work_start_dtm,
sysdate-mod(level,365) AS work_end_dtm
from dual connect by level < 50000
/
but I don't even pretend to understand WHY it's doing what it does.

This "Port CONNECT BY to DB2" article shed some light on the topic for me...
</code> http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0510rielau/ <code>

Gabe,
Whether the doc defines how it should work (rules), or describe how it seems to work (learning material) is certainly an interresting discussion. If something works but is against the doc, either the doc is incorrect, or the software is incorrect because it should have reported an error.

I refer to doc not really as a book of law, but I use it to underline my miscomprehension of using connect by without prior.

> Things like LEVEL, LEAF, ROOT only make sense in a Hierarchy
Well, if you use LEVEL without PRIOR, than it does not make sense, does it?

Personally, I do not avoid the feature … I think it serves a very useful purpose and it is quite powerful. It is the clarity of the subject in the documentation where I think there is room for improvement.

[Note: when I say “Hierarchy” (initcap) I refer to the mathematical definition].

A search for “hierarchical query” will turn nothing in the Concepts manual. The hierarchical query is not explained through the prism of well-defined concepts … rather is is just the sum of all its implemented features and the algorithm it follows. If you follow Mikito’s explanation of his query generator somewhere above:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:40476301944675#40835479405933 <code>

you’ll see he doesn’t say “Here is a generic Hierarchy, and this is what happens” … what he does is explaining what happens when the hierarchical query goes through its well documented execution steps.

So, regarding this: <quote>Well, if you use LEVEL without PRIOR, than it does not make sense, does it?</quote> …

If you assume that “LEVEL” and “PRIOR” are artifacts that make sense only in a Hierarchy then you’re automatically implying a hierarchical query should work only with hierarchical data (where there is exactly one root, there are levels, leafs and no cycles, etc.) … and, as is, it doesn’t. That is, if you’re trying to visualize a Hierarchy when assessing the statement above then sure, you can dispute whether it makes sense or not. But, if you look at “LEVEL” and “PRIOR” as keywords for the hierarchical query feature that are used as inputs for its execution algorithm then, well, “LEVEL” and “PRIOR” are what they are: documented keywords [to exaggerate a bit, they could’ve called them X and Y and it would’ve been the same].

But these keywords (that is, their naming) makes perfect sense if you think that what comes out of a hierarchical query is hierarchical data. The Hierarchy is not the input, but the output.

So, the feature is what it is and it is actually quite useful; the keywords it uses make sense if you consider its output rather than its input (no special organization here, really); the documentation could be clearer [I think] … when introducing hierarchical queries, it should set the context in unambiguous terms in order to avoid having the readers interpreting it from their own perspective.

What it would be nice to a have are facilities in the database to model/implement Hierarchies. Something along the line of the Network Data Model in Spatial 10g, but of course, further constrained from Graphs/Networks to Hierarchies.

I have know a better understanding of what is a "hierarchy". 

I tried a hierarchical query without prior :

SQL> select sys_connect_by_path(deptno,' ') PATH
  2  from dept
  3  where level=3
  4  connect by level<4
  5  start with deptno=10;
PATH
----------
 10 10 10
 10 10 20
 10 10 30
 10 10 40
 10 20 10
 10 20 20
 10 20 30
 10 20 40
 10 30 10
 10 30 20
 10 30 30
 10 30 40
 10 40 10
 10 40 20
 10 40 30
 10 40 40

I now see "the picture" of the "tree" you described. 

Laurent,

You’re right … maybe I shouldn’t have gotten involved in this. I apologize for the long posts.

With your example, I don’t see why you were expecting a Hierarchy of deptno’s … the query defines no relation between deptno’s … the deptno’s are not _the_ nodes, they are an attribute of the nodes.

So, I’ll agree with you that maybe something is too loose. No relation (that is, no PRIOR) then no Hierarchy (it is hierarchical data but its nodes have no identity). So maybe this byproduct of the hierarchical query to “make up” nodes (with no identity) is just that, a byproduct; and unless documented as such, it is cute but not something to be relied upon.

Thank _you_ … for your patience.

Laurent,

One last thing regarding <quote>“the picture” of the “tree”</quote> … using your query, slightly altered not to chop the tree:

flip@FLOP> select level,lpad(' ', 2*level-1)|| sys_connect_by_path(deptno,' ') PATH, rownum identity
2 from dept
3 connect by level<4
4 start with deptno=10;

LEVEL PATH IDENTITY
---------- -------------------- ----------
1 10 1
2 10 10 2
3 10 10 10 3
3 10 10 20 4
3 10 10 30 5
3 10 10 40 6
2 10 20 7
3 10 20 10 8
3 10 20 20 9
3 10 20 30 10
3 10 20 40 11
2 10 30 12
3 10 30 10 13
3 10 30 20 14
3 10 30 30 15
3 10 30 40 16
2 10 40 17
3 10 40 10 18
3 10 40 20 19
3 10 40 30 20
3 10 40 40 21

21 rows selected.

Each instance of a row being returned is a Node, the Root is at level 1, the Leafs are there too … the identity is made up, hence not useful linking back to the input data. Depno is incidental (except for setting up the root). So, what’s the use of this hierarchical data? … none … except for generating rows, should you trust (true, there are risks) the method.

quite a lot of place are telling that connect by is describing a parent-child hierarchy:

</code> http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#sthref7637 <code>

CONNECT BY Clause

Specify a condition that identifies the relationship between parent rows and child rows of the hierarchy. The connect_by_condition can be any condition as described in Chapter 7, "Conditions". However, it must use the PRIOR operator to refer to the parent row.

tom,
I feel much more confident with the method you described in oracle magazine two years ago
</code> https://asktom.oracle.com/Misc/oramag/on-uniqueness-space-and-numbers.html <code>

It is very well documented, I know what I am doing, the dba who will come after me will be able to read and maintain the code, and there are good chance that it will still work in Oracle 11, 12, 13, because it is a documented functionality which cannot be removed without notice.

Kindest regards
Laurent

Tom, I was fooling around with the following query:

with a as (select trunc(dbms_random.value(5, 99), 4) rslt
from dual connect by level < 10)
select rslt
from a
where rslt < 10

I'm consistently getting results that are > 10 like:

RSLT
----------
31.1833
64.4857

Can you reproduce this? This is 10gR2.

The execution looks wierd:

.OPERATION LAST OUTPUT ROWS
.FILTER 3
. (Filter Predicates: TRUNC(DBMS_RANDOM.VALUE(5,99),4)<1 . CONNECT BY 9
. FAST DUAL 1

I have it nicely formatted html, and in text mode it shows really bad.

I've just found another way to generate a sequence of integers.
Works *only* in 10g because it uses SQL modeling feature:


select y from dual
model
dimension by (1 as x) measures (1 as y)
rules iterate (100 /* <= number or rows */)
(y[iteration_number + 1] = iteration_number + 1);


Have a fun ... :)

Cheers,
Andrew.

thank you Andrew.

pity:

you cannot parametrize it using Bind-Variable


SQL> variable x number
SQL> exec :x:=100

PL/SQL procedure successfully completed.

SQL> select y l from dual
  2  model
  3  dimension by (1 as x) measures (1 as y)
  4  rules iterate (:x)
  5  (y[iteration_number + 1] = iteration_number + 1)
  6  ;
rules iterate (:x)
               *
ERROR at line 4:
ORA-32607: invalid ITERATE value in MODEL clause



and then:

I still can't see how it helps answering the original question "Can there be an infinite dual ?"

I still think the answer is "No", for

create view infinite_dual
as select level n from dual connect by null is null;

SQL> select * from infinite_dual where n<10;
ERROR:
ORA-04030: out of process memory when trying to allocate 28 bytes (cursor work
he,cursor work heap)


Is there a way to achieve an infinite dual with Andrew's idea ?
(or will the answer to this question be always:
if you want an infinite dual, use a select-stmt, you
cannot use a view, since a view is different from a select-stmt).  

SQL> create view i_want_infinitely_many 
  2  as
  3  select level l
  4  from dual
  5  connect by level <= BINARY_DOUBLE_INFINITY;

View created.

SQL> select * from i_want_infinitely_many  where l<10;

ORA-04030: out of process memory when trying to allocate 28 bytes (kxs heap,cursor work heap)

 

thx

Tom,

Is there a way to generate permutations for N numbers using a recursive query?

e.g.

Select n1.n a, n2.n b, n3.n c, n4.n d
From (select level n from dual connect by level <= 4) n1,
(select level n from dual connect by level <= 4) n2,
(select level n from dual connect by level <= 4) n3,
(select level n from dual connect by level <= 4) n4
Where n1.n != n2.n
and n1.n != n3.n
and n1.n != n4.n
and n2.n != n3.n
and n2.n != n4.n
and n3.n != n4.n

N N N N
---------- ---------- ---------- ----------
4 3 2 1
3 4 2 1
4 2 3 1
2 4 3 1
3 2 4 1
2 3 4 1
4 3 1 2
3 4 1 2
4 1 3 2
1 4 3 2
3 1 4 2
1 3 4 2
4 2 1 3
2 4 1 3
4 1 2 3
1 4 2 3
2 1 4 3
1 2 4 3
3 2 1 4
2 3 1 4
3 1 2 4
1 3 2 4
2 1 3 4
1 2 3 4

24 rows selected.

This "works" but if we want to generate for say 8 numbers, the where clause would grow too long.

I was trying something like:

Select sys_connect_by_path(n,'|') from
(select level n from dual connect by level <= 4) n1
Connect by instr(prior sys_connect_by_path(n,'|'), n) = 0
level <= 4

but the sys_connect_by is not allowed in th connect by.

Thanks,
Naresh

I tried a bit further - here is a possible way:

SQL> l
  1  select * from (
  2  Select replace(sys_connect_by_path(n,'|'),'|', '') n_path from
  3  (select level n from dual connect by level <= 4) n1
  4  Connect by level <= 4)
  5  where length(n_path) = 4
  6    and not exists (select 'x' from
  7                  (select level n from dual connect by level <= 4) n2,
  8                  (select level n from dual connect by level <= 4) n3
  9                  where n2.n != n3.n
 10*                    and substr(n_path, n3.n, 1) = substr(n_path, n2.n, 1))
SQL> /
1234
1243
1324
1342
1423
1432
2134
2143
2314
2341
2413
2431
3124
3142
3214
3241
3412
3421
4123
4132
4213
4231
4312
4321

24 rows selected.


I tried with 6 rows, and it took 47 seconds.

When I instead used a table with 6 rows as

Create table num as (select level n from dual connect by level <= 6);

and

select * from (
Select replace(sys_connect_by_path(n,'|'),'|', '') n_path from
Num n1
Connect by level <= 6)
where length(n_path) = 6
  and not exists (select 'x' from
                num n2,
                num n3
                where n2.n != n3.n
                   and substr(n_path, n3.n, 1) = substr(n_path, n2.n, 1))
/

Elapsed: 00:00:22.87

So creating a "table" row source worked better.

But is there something simpler to achieve this than the

not exists (select 'x' from
                num n2,
                num n3
                where n2.n != n3.n
                   and substr(n_path, n3.n, 1) = substr(n_path, n2.n, 1))

???

Thanks,
Naresh 

refering to
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:40476301944675#42878905407514 <code>

Mikito (if you read this):
what about your book ?
it was scheduled for May 2006, is it finished ?

Tom: do you know more ?
we haven't heard much from Mikito during the last weeks
on this site, did we ?

     Here is a faster sql for naresh's question 
"Can we generate permutations for N numbers with recursive query? " 
Posted on  February 05, 2006 

Frank

SQL> select *
  2  from
  3  (select replace (sys_connect_by_path( n, ',' ) , ',' ) output
  4   from
  5   (select level n from dual connect by level <=4 )
  6    connect by nocycle n != prior n
  7  )
  8  where length(output) = 4;

1234                                                                            
1243                                                                            
1324                                                                            
1342                                                                            
1423                                                                            
1432                                                                            
2134                                                                            
2143                                                                            
2314                                                                            
2341                                                                            
2413                                                                            
2431                                                                            
3124                                                                            
3142                                                                            
3214                                                                            
3241                                                                            
3412                                                                            
3421                                                                            
4123                                                                            
4132                                                                            
4213                                                                            
4231                                                                            
4312                                                                            
4321                                                                            

24 rows selected. 

Hi Tom,
I have a table that looks like this:-

trdate intdays
---------------
29-NOV-06 1
30-NOV-06 1
01-DEC-06 3
04-DEC-06 1

The intdays for 01-DEC-06 is 3 b'cos 1st of Dec is a weekend and the next business day is 3 days later.

What I need is to be able to generate something like this:-
trdate intdays
------------------
29-NOV-06 1
30-NOV-06 1
01-DEC-06 3
02-DEC-06 3 --- add this
03-DEC-06 3 --- add this
04-DEC-06 1

Basically if a given calendar date doesn't exist in the table, I'd like my query to add the required number of days and return that. I've been trying for a query all morning without much luck. Any help would be greatly appreciated.

Thanks

Sorry about that. Here is the info you need.


create table int_dates
(trdate date,
intdays number);

insert into int_dates
values('29-NOV-06', 1);

insert into int_dates
values('30-NOV-06', 1);

insert into int_dates
values('01-DEC-06', 3);

insert into int_dates
values('04-DEC-06', 1);


select * from int_dates;

TRDATE INTDAYS
--------- ----------
29-NOV-06 1
30-NOV-06 1
01-DEC-06 3
04-DEC-06 1


What I'd like is something like

TRDATE INTDAYS
--------- ----------
29-NOV-06 1
30-NOV-06 1
01-DEC-06 3
02-DEC-06 3
03-DEC-06 3
04-DEC-06 1

Thanks

ops$tkyte%ORA10GR2> with data
  2  as
  3  (select level l from dual connect by level <= (select max(intdays) from int_dates) )
  4  select int_dates.trdate, int_dates.trdate+l-1
  5    from int_dates, data
  6   where int_dates.intdays >= data.l
  7   order by 2
  8  /

TRDATE    INT_DATES
--------- ---------
29-NOV-06 29-NOV-06
30-NOV-06 30-NOV-06
01-DEC-06 01-DEC-06
01-DEC-06 02-DEC-06
01-DEC-06 03-DEC-06
04-DEC-06 04-DEC-06

6 rows selected.
 

select * from v$version;

BANNER
-----------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

1 WITH t as ( select level t from dual connect by level < 2 )
2 select t.*, level lvl from t, dual connect by level < 2
3* order by t, level
/

T LVL
---------- ----------
1 1

ed
Wrote file afiedt.buf

1 WITH t as ( select level t from dual connect by level < 2 )
2 select t.*, level lvl from t, dual connect by level < 3
3* order by t, level
/

T LVL
---------- ----------
1 1
1 2

ed
Wrote file afiedt.buf

1 WITH t as ( select level t from dual connect by level < 3 )
2 select t.*, level lvl from t, dual connect by level < 3
3* order by t, level
/

T LVL
---------- ----------
1 1
1 2
1 2
2 1
2 2
2 2

6 rows selected.

ed
Wrote file afiedt.buf

1 WITH t as ( select level t from dual connect by level < 3 )
2 select t.*, level lvl from t, dual connect by level < 4
3* order by t, level
/

T LVL
---------- ----------
1 1
1 2
1 2
1 3
1 3
1 3
1 3
2 1
2 2
2 2
2 3
2 3
2 3
2 3

14 rows selected.

1 WITH t as ( select level t from dual connect by level < 3 )
2 , u as ( select t, level lvl from t, dual connect by level < 8 )
3* select count(*), lvl from u group by lvl order by lvl
/

COUNT(*) LVL
-------------------- ----------
2 1
4 2
8 3
16 4
32 5
64 6
128 7

7 rows selected.

I just wanted to come back to this query:
select level from dual connect by level < 2000
Comparing a 9i and a 10g Database with identical frontends (TOAD and SQLPlus), i suspect the error to be on the server-side. It all depends on how many rows you fetch initially. With 9i, you dont get any more rows after that. You can see this easily in TOAD: in my case, it fetches 500 rows after executing the query. Using a 10g server, it can fetch more rows after that. Using 9i it can not. If SQLPlus fetches one row initially, it will only see one row of the result. Set arraysize apparently does not affect the number of rows that SQLPlus fetches during the first roundtrip.

For the readers who were discussing MODEL clause techniques above (Andrew Max and "A Reader"), try it this way.

variable v_max_value number

execute :v_max_value := 3

select integer_value
from   dual
where  1=2
model
  dimension by ( 0 as key )
  measures     ( 0 as integer_value )
  rules upsert ( integer_value[ for key from 1 to :v_max_value increment 1 ] = cv(key) )
;

INTEGER_VALUE
-------------
            1
            2
            3

SQL*Plus: Release 10.2.0.1.0 - Production on Mon May 7 17:30:47 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> select level from dual connect by level < 10;

     LEVEL
----------
         1

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   CONNECT BY (WITH FILTERING)
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'DUAL'
   4    2       TABLE ACCESS (BY USER ROWID) OF 'DUAL'
   5    1     NESTED LOOPS
   6    5       BUFFER (SORT)
   7    6         CONNECT BY PUMP
   8    5       FILTER
   9    8         TABLE ACCESS (FULL) OF 'DUAL'




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        193  bytes sent via SQL*Net to client
        233  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

Oracle 8.1.7

Why this error ?

11:06:01 stores1@ >SELECT *
11:06:02 2 FROM (SELECT LEVEL l FROM dual CONNECT BY LEVEL < 10)
11:06:02 3 /

L
----------
1
2
ERROR:
ORA-01436: CONNECT BY loop in user data



real: 16
11:06:03 stores1@ >ed
Wrote file 0.sql

1 SELECT *
2* FROM (SELECT LEVEL l FROM dual CONNECT BY LEVEL < 10)
11:06:22 stores1@ >spo off

As far as I know (I may be wrong) mikito harakiri in Japanese is the god of suicide.
I am sure about "suicide".

ORA-01436: CONNECT BY loop in user data

This error did not occur on 10.1.0 - only on oracle 8.1.7.

(1) As such, you have already said "dual is magical" - but, still, I hope you would add somthing more to it

(2) In some other pages you have said *something like* "Analytics is the most interesting new thing (to / for you) after a long time".

I think that this infinite dual (self-join) also seems to be (internally) doing the similar thing (similar to analyitics, that is). Oracle made analytics based on that - is that thought proper ?

Please remove the comment about the Japanese Culture.

"Vadim Tropashko is known in Oracle community as Mikito Harakiri"
http://www.amazon.com/gp/pdp/profile/ACPDLULL3O2GQ/ref=cm_aya_pdp_profile/105-4805993-1897238
"Vadim Tropashko is the translator for The C++ Programming Language into Russian. He is a former C++ instructor at University of Radio and Electronics in Belarus and currently works for..."

thanx for that SnippetyJoe

I don't think so
Please check out
http://www.dbazine.com/oracle/or-articles/tropashko7

It seems that they are different people

Hi Tom,

On November 7, 2005 above you replied to Laurent with "I shall file a documentation bug to have the documentation updated to accurately reflect reality."

Can you give us the bug number? I can't seem to find it in Metalink.

Thx.

Compare
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:40476301944675#40503723845809
and
Laurent Schneider's comment on Tropashko's book, to wit:
"The author invented many techniques, the most famous is probably the row generator CONNECT BY LEVEL<10..."
http://www.amazon.com/SQL-Design-Patterns-Programming-Focus/dp/0977671542/ref=cm_cr-mr-title/102-8433556-4557742

alternative 

SQL> select * from xmltable('1 to 10');
COLUMN_VALUE
------------
1
2
3
4
5
6
7
8
9
10

the query above works in 10gR2 too, but the following with bind works only in 11g

select column_value from xmltable(:x||' to '||:y);

improvement:

select to_number(column_value) n from xmltable('1 to 10000')

d'accord ?

SQL> select * from xmltable('number(1 to 10000)');
select * from xmltable('number(1 to 10000)')
                                           *
ERROR at line 1:
ORA-19112: error raised during evaluation: oracle.xquery.XQException: XP0006:
During the evaluation phase, it is a type error if a value does not match a
required type as specified by the matching rules in 2.4.1.1 SequenceType
Matching.

wants to tell us ?

SQL> select * from xmltable('1 to 10' columns n for ordinality);
         N
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

Laurent.

however, the "from dual connect by level <= .." method
seems to be a bit more performant
(tested only on 10.2 - about 20% performance gain in comparison to the XQuery using "for ordinality")

questions:

a. does this still hold in 11 ?
b. where to find a good reference "XQuery in a nutshell" or so on this stuff ?
c. the "from dual connect by level" method will always
return the natural numbers in their default order.
does this also hold for the XQuery method ?

XQuery is not as fast.

SQL> select count(*) from xmltable('1 to 10000000' columns n for ordinality);
  COUNT(*)
----------
  10000000

Elapsed: 00:00:10.41
SQL> select count(*) from (select * from dual connect by level<10000001);
  COUNT(*)
----------
  10000000

Elapsed: 00:00:06.26


still the connect by requires one PRIOR clause according to the 11g doc. 
But it is faster than xquery.

there are many connect by without prior that are insane...

If you try something wrong, it may crash your server, it almost did when I tried

select n from (select rownum n from dual connect by 1=1) where n between 4 and 8;

again, there is no "connect by loop" if you start with the concept that a "connect by loop" exists only when using prior.

by the way, do not try the query above unless you are ready to reboot !!!

to compare performance:

SQL > select count(*) from xmltable('1 to 10000000' columns n for ordinality);

Elapsed: 00:00:16.71

and 
SQL > select count(*) from (select level from dual connect by level <= 10000000);

Elapsed: 00:00:06.85

BUT

sql > set autotr traceonly statistics
sql > select * from dual connect by level<=10000000;

10000000 rows selected.

Elapsed: 00:00:46.15

Statistics
----------------------------------------------------------
         24  recursive calls
          0  db block gets
          7  consistent gets
          2  physical reads
          0  redo size
  172000431  bytes sent via SQL*Net to client
    7333790  bytes received via SQL*Net from client
     666668  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
   10000000  rows processed

and

sql >  select * from xmltable('1 to 10000000' columns n for ordinality);

10000000 rows selected.

Elapsed: 00:03:11.16

Statistics
----------------------------------------------------------
         47  recursive calls
         32  db block gets
        197  consistent gets
          2  physical reads
          0  redo size
  192889380  bytes sent via SQL*Net to client
    7333790  bytes received via SQL*Net from client
     666668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
   10000000  rows processed


Especially, I wonder, where the difference in 
"bytes sent via SQL*Net to client"
come from (I expected exactly the same bytes)

How can achive the same in Oracle 8.1.7

I mean the select

select *
from (select level l from dual connect by prior 1=1)

gives error

ORA-00933: SQL command not properly ended

any work around in 8i.

exec raise_application_error(-20436, 'CONNECT BY loop in user data')

which comes very near to what you want

I was looking for some work around in 8i. Iam not sure how your answer is helpfull to me Sokrates. I dont want to raise an error. But want to avoide that error and achive the same thing in 8i.

Hello!

My name is Cristi Boboc. My email is cristi.boboc@gmail.com.

I want to tell you something very interesting about the subject of this post:

SELECT level FROM dual CONNECT BY level < 1000;
and
SELECT 1 FROM dual GROUP BY CUBE (1,2,3,4,5,6,7,8,9);

are both returning 100 lines on my database 9.2...

But
CREATE TABLE aaa AS SELECT level Id FROM dual CONNECT BY level < 1000;
and
CREATE TABLE aaa AS SELECT 1 Id FROM dual GROUP BY CUBE (1,2,3,4,5,6,7,8,9);

are both inserting the right number of lines in the aaa table.

The only difference I can see is that CUBE provides a number of lines allways as a power of 2 and also has to modify the SQL (it cannot use a bind variable).

In SQL*Plus CONNECT BY LEVEL gives single row. On the other hand, on TOAD editor first time give desired result but now an unknown reason it gives single row.

Thanks

The following code works in Oracle 10.2.0.4 and not working in 11.2.0.3.

Error:
ERROR:ORA-19112: error raised during evaluation: FODC0003, Function stability not defined

/* test case*/

declare
Type Folders is Table of VarChar2(256);
XMLFolders Folders := Folders('/public/temp/');
v_Return boolean;
myXML XMLType;
selectValue varchar(10);
begin
myXML := XMLType('<a id="2"><c>test</c></a>');
v_Return := DBMS_XDB.createFolder( XMLFolders(1) );
v_Return := DBMS_XDB.createResource(XMLFolders(XMLFolders.Last) || 'my-xml' || '.xml', myXML);
Select TIR.TestItemId into selectValue
From
(Select XMLType('<path value="' || '/public/temp/my-xml.xml' || '"/>') as ResourcePath From Dual) X,
XMLTable('for $mydata in fn:doc(xs:string($ResourcePath))/a/b/c
let $mysubdata := $mydata/../..
return <xyz id="{$mysubdata/@id}"/>'
Passing X.ResourcePath.extract('/path/@value') as "ResourcePath"
Columns TestItemId VarChar2(32) Path '@id') TIR;
DBMS_Output.put_Line('Result from XML - ' || selectValue);
end;