Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Tom.

Asked: May 09, 2005 - 10:24 am UTC

Last updated: March 18, 2009 - 8:17 am UTC

Version: 10.1.0

Viewed 10K+ times! This question is

You Asked

Hi,

Some time ago I came across a situation where I needed to duplicate a single row N times on an otherwise empty table, whilst incrementing a "manual" sequence number (The database is from a legacy system and does not employ Oracle Sequences).

Say the table is of the format:

CREATE TABLE t (seqno NUMBER, a VARCHAR2(5), b VARCHAR2(5));

And has one row:

INSERT INTO t VALUES (1, 'X', 'X');

Which I want to duplicate to create rows:
(2, 'X', 'X')
(3, 'X', 'X')
...
(N+1, 'X', 'X')

The approach I came up with at the time was using a Cartesian join to an arbitrary table:

INSERT INTO t (seqno, a, b)
SELECT ROWNUM+1, a, b
FROM t, XXX
WHERE ROWNUM <= &N

Where XXX represents any table populated with at least than N rows.

At that time I began to wonder whether there was a table like DUAL that, instead of being "magically" treated by the optimiser as having one row, was "magically" treated as having an infinite number of rows.

Anyway, recently I was reading about the DUAL table on asktom.oracle.com and came across this:

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

I was intrigued by the solution for a way of selecting multiple rows from DUAL.

I thought maybe this would work:

INSERT INTO t (seqno, a, b)
SELECT ROWNUM+1, a, b
FROM t,
(
SELECT NULL
FROM dual
GROUP BY CUBE(1,2,3,4,5,6,7,...)
)
WHERE ROWNUM <= &N

Unfortunately, the solution given here seems to break down if you require more than 100 rows - as CUBE seems to give 2,4,8,16,32,64 rows for 1 to 6 arguments respectively, then repeatedly gives 100 for > 6 args.

My question is:

Is there a way of Cartesian joining to a "magic" table of theoretically infinite rows?

Regards,

Tom

and Tom said...

select level from dual connect by level < :N



ops$tkyte@ORA10G> select *
2 from (select level l from dual connect by level < 10)
3 /

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

9 rows selected.


Rating

  (99 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Cool!

A reader, May 09, 2005 - 11:56 am UTC


Thanks

Tom Denley, May 09, 2005 - 12:24 pm UTC

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


Tom Kyte
May 09, 2005 - 1:25 pm UTC

they are out there -- we didn't make that one up this morning :)

Weird results

Jay, May 09, 2005 - 12:41 pm UTC

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.
 

Tom Kyte
May 09, 2005 - 2:38 pm UTC

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


no clue on toad.

3) where does it say it MUST have it. we don't need to refer to the parent row, so we need not use prior.

4) it has so far been the fastest method, especially in 10g with FAST DUAL

5) just imagine "connect by prior 1 = 1 and level < :n" There is just one "tree" here -- root -> child -> child -> child

8i

DaPi, May 09, 2005 - 12:47 pm UTC

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

Tom Kyte
May 09, 2005 - 2:38 pm UTC

correct, the connect by level < :n does not work in 8i and before.

Other versions

DaPi, May 09, 2005 - 1:10 pm UTC

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>

SQL*Plus bug ?

Jay, May 09, 2005 - 2:50 pm UTC

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 Kyte
May 09, 2005 - 4:01 pm UTC

1) yes, i believe it is filed
2) sqlplus saw "dual" -- not sure of the details, just know it happened.

TAOD & SQL*Plus different

DaPi, May 09, 2005 - 3:10 pm UTC

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?

cui honorem honorem

Matthias Rogel, May 09, 2005 - 3:41 pm UTC

and you forgot to say

thank you to mikito harakiri for this nice query

or am I wrong ?

Tom Kyte
May 09, 2005 - 4:09 pm UTC

Do I have to attribute everything I write to anyone that ever gave me the idea?

I attributed and thanked him the first times,

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

for example,  I've done it in talks as well (eg: reference 
http://www.oreillynet.com/pub/wlg/6627 <code>
)

I cannot do it everytime. Else (my 1st grade teacher) I (kindergarten) would (2nd grade) not (I forget) .... be able to write anything....




Neeto

Chuck Jolley, May 09, 2005 - 5:04 pm UTC

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. ;)


Tom Kyte
May 09, 2005 - 5:51 pm UTC

*really*

Now that answers alot. (about toad)

Weird results

Jay, May 09, 2005 - 5:45 pm UTC

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'


select level from dual connect by level < 10; - NOT SQL*Plus issue

Gary, May 10, 2005 - 1:54 am UTC

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

 

Tom Kyte
May 10, 2005 - 8:11 am UTC

it is the oci calls that sqlplus is using, it does not affect all environments, it is a sqlplus issue in the way the apis are being used.

when using the inline view or the with clause, the issue (the oci calls) do not impact it.

mv $ORACLE_HOME/bin/sqlplus $ORACLE_HOME/bin/sqlminus

Matthias Rogel, May 10, 2005 - 2:39 am UTC

'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

Weird results and such

Mark A. Williams, May 10, 2005 - 1:41 pm UTC

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. 

Tom Kyte
May 10, 2005 - 2:28 pm UTC

it would appear to be fixed in the supported patchsets (i had 9205 on my machine)

To Mark

A reader, May 10, 2005 - 3:52 pm UTC

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? 

From Mark

Mark A. Williams, May 10, 2005 - 4:33 pm UTC

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 

SQL*Plus

A reader, May 11, 2005 - 11:26 am UTC

"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

Tom Kyte
May 11, 2005 - 12:25 pm UTC

could be -- i should have jotted the bug number down, shouldn't I have.

How high can we go?

Mark J. Bobak, May 11, 2005 - 3:33 pm UTC

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

;-)

Can there be an infinite DUAL

Chuck Jolley, May 11, 2005 - 4:59 pm UTC

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.

Pipeline function?

Loz, May 11, 2005 - 7:15 pm UTC

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.


Tom Kyte
May 12, 2005 - 7:26 am UTC

yes it can be.

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

SQL*socalledPlus is not innocent

Matthias Rogel, May 12, 2005 - 6:03 am UTC

<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. 

Tom Kyte
May 12, 2005 - 8:00 am UTC

suggest you use one of the other tools out there since you do so seem to not enjoy sqlplus.

Matthias...

Mark A. Williams, May 12, 2005 - 10:26 am UTC

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 

Mark ..

Matthias Rogel, May 12, 2005 - 1:04 pm UTC

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, Part 2

Mark A. Williams, May 12, 2005 - 2:36 pm UTC

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

Tom Kyte
May 12, 2005 - 3:40 pm UTC

see note Note 185438.1, there is some code that just "knows" (erroneously) that select * from dual should return at most one row.

gotcha...

Mark A. Williams, May 12, 2005 - 3:48 pm UTC

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

- Mark

I like magic

DaPi, May 12, 2005 - 5:18 pm UTC

"(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.



Tom Kyte
May 13, 2005 - 8:47 am UTC

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>


 

RE: Pipeline function?

A reader, May 12, 2005 - 7:27 pm UTC

<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>

Tom Kyte
May 13, 2005 - 9:05 am UTC

that has nothing to do with this question as far as I can see?

you normally do not do dml in the pipelined function and if you do, you must commit before piping a row back (which in hindsight makes obvious and complete sense to me).



It would be a poor world without any magic.

Matthias Rogel, May 13, 2005 - 2:50 am UTC

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

Whodunnit: SQL*Plus or Oracle Server ?

Jay, May 13, 2005 - 10:35 am UTC

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) ?
 

Tom Kyte
May 13, 2005 - 10:48 am UTC

I believe the "bug" to be in the OCI layer of which there are two pieces -- client and server and depending on how you write the OCI application you may or may not see it.

and it all boils down to "dual is magic unfortunately", putting it in the inline view with level or rownum (or using with subquery factoring) breaks the magic spell.

Whodunnit: SQL*Plus or Oracle Server ?

Jay, May 13, 2005 - 12:18 pm UTC

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 ?

Tom Kyte
May 13, 2005 - 12:45 pm UTC

yup, there is bits of it all over the place. plsql is a client of the database.

it isn't the same oci you and i have access to (little lower) but it is all the same..

Loop detection defeated

mikito, May 13, 2005 - 2:47 pm UTC

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.

Number of rows restricted in TOAD

Jay, May 13, 2005 - 3:10 pm UTC

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.

RE: loop detection defeated

Mark A. Williams, May 13, 2005 - 3:43 pm UTC

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> 

RE: loop detection defeated

mikito, May 13, 2005 - 6:28 pm UTC

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 the meaning of "magic"

A reader, May 16, 2005 - 3:35 pm UTC

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

Tom Kyte
May 16, 2005 - 5:03 pm UTC

buggy, as we all know, is really called a "product issue"

dual is magic.

so how to optimize ...

Matthias Rogel, June 17, 2005 - 10:33 am UTC

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
]

Tom Kyte
June 17, 2005 - 3:53 pm UTC

http://www.phpbbserver.com/phpbb/viewtopic.php?t=25&start=0&mforum=dizwellforum#196

(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
 
 

Primes

Jonathan Lewis, June 17, 2005 - 5:47 pm UTC

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)
/




Tom Kyte
June 17, 2005 - 7:06 pm UTC

running with 1000 5000 and 10000 and the CBO *(that is important, my query with RBO bests this one, but the CBO with your query best mine :)*


select n1.l * n2.l
from n n1, n n2
where n1.l <= n2.l
and n1.l <= (select sqrt(:bound) from dual)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.22 0.43 46 742 12 168
total 48 0.84 0.85 8 712 15 669
total 85 2.51 2.51 16 1820 23 1229


with t
as
(select level+1 id from dual connect by level < :bound )
select id from t x1
where not exists
(
select 1 from t x2 where
mod(x1.id , x2.id) = 0
and x2.id>1
and x2.id<ceil(x1.id/2)+1
) and x1.id<10000 and x1.id>0

total 15 0.25 0.32 2 3192 11 168
total 48 4.28 4.27 8 19737 15 669
total 85 15.41 15.42 16 48531 23 1229




and in 9i:

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)

total 15 0.20 0.19 2 136 3 168
total 48 2.32 2.29 8 715 4 669
total 85 6.77 6.64 16 1823 4 1229

with t
as
(select level+1 id from dual connect by level < :bound )
select id from t x1
where not exists
(
select 1 from t x2 where
mod(x1.id , x2.id) = 0
and x2.id>1
and x2.id<ceil(x1.id/2)+1
) and x1.id<10000 and x1.id>0

total 15 0.43 0.45 2 3186 3 168
total 48 8.22 8.02 8 19737 4 669
total 85 29.96 29.24 16 48531 4 1229



that was the same machine, same configurations



No join required

Mikito Harakiri, June 17, 2005 - 8:00 pm UTC

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

....


Tom Kyte
June 17, 2005 - 9:02 pm UTC

umm? huh?

can you apply that to the primes we were talking about or anything on this page?

antijoin is still there, though

Mikito Harakiri, June 17, 2005 - 10:27 pm UTC

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...

If can't compete in performance, challenge in obfuscation!

Mikito Harakiri, June 17, 2005 - 11:20 pm UTC

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;

Inquisitive...

Abu Areeb, June 18, 2005 - 2:33 am UTC

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

thank you

Matthias Rogel, June 18, 2005 - 3:09 pm UTC

tom, jonathan and mikito for answers


Weird......!

Abu Areeb, June 23, 2005 - 4:10 am UTC

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

Tom Kyte
June 23, 2005 - 6:27 pm UTC

no credentials needed, especially when a fully formed response is given.

In search for primes ...

Gabe, June 24, 2005 - 11:36 am UTC

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.


connect by loop

Laurent Schneider, November 07, 2005 - 4:33 am UTC

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?

Tom Kyte
November 07, 2005 - 8:54 am UTC

No, you have never needed PRIOR in a connect by.

This will not lead to a loop.

re: connect by loop

Laurent Schneider, November 07, 2005 - 10:08 am UTC

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.


Tom Kyte
November 07, 2005 - 12:02 pm UTC

but that is not a connect by loop, a connect by loop is an infinite loop in the data.

if empno = 1 has a mgr = 2
and
empno = 2 has a mgr = 1

that is a connect by loop. In 10g there is a new clause NOCYCLE that can be used to actually process this data.


I shall file a documentation bug to have the documentation updated to accurately reflect reality.

It could be said that "in general"...

another magic behaviour

Matthias Rogel, November 07, 2005 - 10:57 am UTC

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 ... 

Tom Kyte
November 07, 2005 - 8:30 pm UTC

"dual" is magic, has been, will be...

it just knew to stop in your release (probably 9i?)

Some comments...

Andrew Max, November 07, 2005 - 11:25 am UTC

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.

Tom Kyte
November 08, 2005 - 9:22 pm UTC

we let you do this too:


select count(*)
from all_objects, all_objects, all_objects, all_objects, all_objects, all_objects;

that will not only consume tons of cpu - but probably tons of temp...


Not a valid argument to me - I can always code:


begin loop null; end loop; end;
/

as well.

Re: some comments...

Laurent Schneider, November 07, 2005 - 11:47 am UTC

> 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.


re: re: connect by loop

Laurent Schneider, November 07, 2005 - 12:52 pm UTC

> 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

Tom Kyte
November 08, 2005 - 9:26 pm UTC

but there is a hierarchy - every parent has a child

I'll file it as a doc bug and we'll see what happens with it.

Re: "another magic behaviour"

Gabe, November 07, 2005 - 1:18 pm UTC

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>

Two comments should be enough for me ...

Gabe, November 07, 2005 - 3:42 pm UTC

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.


Fertile grounds?

Duke Ganote, November 07, 2005 - 4:45 pm UTC

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>

What is the doc?

Laurent Schneider, November 08, 2005 - 2:56 am UTC

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?


Tom Kyte
November 08, 2005 - 9:59 pm UTC

why doesn't it make sense?



Laurent, Duke ...

Gabe, November 08, 2005 - 3:51 pm UTC

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.



thanks for your patience

Laurent Schneider, November 09, 2005 - 7:58 am UTC

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. 

Cute ... but not that cute ...

Gabe, November 09, 2005 - 10:34 am UTC

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.


really the last ...

Gabe, November 09, 2005 - 12:33 pm UTC

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.



doc bug

Laurent Schneider, November 11, 2005 - 10:05 am UTC

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.



pipeline table

laurent schneider, November 15, 2005 - 9:32 am UTC

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 Kyte
November 15, 2005 - 11:42 am UTC

That is your option, I've no problem with that. I like this with clause personally.

Weird Results (bug?)

djb, November 15, 2005 - 2:13 pm UTC

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.


Tom Kyte
November 15, 2005 - 4:10 pm UTC

looks like view merging and the query is being rewritten as


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


you can try no_merge or materialize hints - or throwing an order by/rownum on the query in the with clause (to achieve the materialize)

the execution

Mikito Harakiri, November 15, 2005 - 5:09 pm UTC

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


Can I post html?

Mikito Harakiri, November 15, 2005 - 5:11 pm UTC

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

Tom Kyte
November 16, 2005 - 8:29 am UTC

you cannot post html.

it is all about ascii art and courier fonts here.

How about this?...

Andrew Max, November 17, 2005 - 6:32 am UTC

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.

does it help ?

A reader, November 21, 2005 - 4:50 am UTC

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).  

Tom Kyte
November 21, 2005 - 8:45 am UTC

with as_many_as_you_want
as
(select level l
from dual
connect by level <= :x)
select * from as_many_as_you_want;

i_want_infinitely_many

A reader, November 21, 2005 - 9:20 am UTC

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)

 

Tom Kyte
November 21, 2005 - 9:52 am UTC

then you'll be using a pipelined function - or putting the predicate the way I demonstrated - it is "infinite"


with as_many_as_you_want
as
(select level l
from dual
connect by level <= :x)
select * from as_many_as_you_want;




ok

A reader, November 21, 2005 - 10:30 am UTC

thx

Can we generate permutations for N numbers with recursive query?

Naresh, February 05, 2006 - 8:00 am UTC

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

Generating permutations

Naresh, February 05, 2006 - 9:02 am UTC

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 

Mikito's book

Sokrates, June 01, 2006 - 10:14 am UTC

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 ?



Generate permutations for N numbers with recursive query

Frank Zhou, November 15, 2006 - 4:42 pm UTC

     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. 

Dates that do no exist

a reader, December 05, 2006 - 10:57 am UTC

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


Tom Kyte
December 05, 2006 - 10:11 pm UTC

no create table
no insert into table
no lookie

Sorry. Here's more info

A reader, December 06, 2006 - 5:45 am UTC

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

Tom Kyte
December 07, 2006 - 8:24 am UTC

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.
 

Curious join

Duke Ganote, February 28, 2007 - 5:42 pm UTC


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.

Fault in SQLPlus or in Backend?

ralferic, March 22, 2007 - 4:00 am UTC

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.

Another MODEL Solution

SnippetyJoe, March 30, 2007 - 4:43 pm UTC


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

connect by level not working in 9.2.0.6.0 ?

A reader, May 07, 2007 - 5:45 pm UTC

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

Tom Kyte
May 10, 2007 - 7:42 pm UTC

use subquery factoring or inline views

as demonstrated over and over above

http://asktom.oracle.com/pls/asktom/f?p=100:232:2443460180102040::::P232_QUESTIONID:40476301944675#40490066762235

ORA-01436: CONNECT BY loop in user data

Yogesh Purabiya, June 15, 2007 - 1:44 am UTC

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

Tom Kyte
June 15, 2007 - 7:45 am UTC

because you are not in 9ir2 and above.

on a aside

A reader, June 15, 2007 - 1:46 am UTC

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 AND infinite dual

Yogesh Purabiya, June 15, 2007 - 2:07 am UTC


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 ?

?

Another Reader, June 15, 2007 - 11:17 pm UTC

Please remove the comment about the Japanese Culture.

Tropashko <-> Harakiri

A reader, June 19, 2007 - 8:51 pm UTC

"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..."



SnippetyJoe

A reader, June 20, 2007 - 8:15 am UTC

thanx for that SnippetyJoe

Harakiri?

A Reader, June 21, 2007 - 12:55 am UTC

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

It seems that they are different people

Doc Bug # re. hierarchical queries must have PRIOR

SnippetyJoe, July 26, 2007 - 11:23 am UTC

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.

Harakiti & Tropashko

A reader, September 19, 2007 - 8:27 am UTC

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

Tom Kyte
September 19, 2007 - 12:54 pm UTC

I guess the first person you see doing something becomes the 'inventor' :)

11g

Laurent Schneider, September 19, 2007 - 1:53 pm UTC

alternative 

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

11g + bind

Laurent Schneider, September 19, 2007 - 2:10 pm UTC

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

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

thx Laurent

Sokrates, September 20, 2007 - 3:11 am UTC

improvement:

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

d'accord ?

what does

Sokrates, September 20, 2007 - 3:14 am UTC

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 ?

11g N number

Laurent Schneider, September 20, 2007 - 4:07 am UTC

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

thx again

Sokrates, September 20, 2007 - 5:34 am UTC

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 ?

connect by dual

Laurent Schneider, September 20, 2007 - 9:26 am UTC

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 !!!

I wouldn't use a select count(*)

Sokrates, September 20, 2007 - 11:20 am UTC

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)




Ho to do in oracle 8i

karthick pattabiraman, September 21, 2007 - 12:55 am UTC

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.
Tom Kyte
September 26, 2007 - 7:38 am UTC

you cannot, it requires 9ir2 and above for the dual trick to work.

in 8i you can do a

Sokrates, September 21, 2007 - 5:54 am UTC

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

which comes very near to what you want

I dont get it

karthick pattabiraman, September 24, 2007 - 8:07 am UTC

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.
Tom Kyte
September 26, 2007 - 1:37 pm UTC

he was pointing out your query was an infinite loop.

About Cube and Level

Cristi Boboc, December 08, 2008 - 7:23 am UTC

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).

CONNECT BY LEVEL

Rahinur Rahaman, March 18, 2009 - 3:22 am UTC

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
Tom Kyte
March 18, 2009 - 8:17 am UTC

that was a sqlplus'ism in old releases. From the last century.


with data
as
(select level l from dual connect by level < 10)
select * from data;

select * from (select level l from dual connect by level < 10);

should both work in plus in that really old release.

Riyaz, March 06, 2014 - 5:21 am UTC

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;