Home » SQL & PL/SQL » SQL & PL/SQL » Row generator  () 1 Vote
Re: Puzzle n°00 - Row generator * [message #640208 is a reply to message #640207] Thu, 23 July 2015 10:16 Go to previous messageGo to next message
laurentschneider
Messages: 6
Registered: March 2007
Location: Switzerland
Junior Member
and if you need one million row, use clob
SELECT count(*) 
FROM JSON_TABLE(
  '['||REPLACE(LPAD(to_clob('1'),999999,1),1,'1,')||1||']',
  '$[*]'
  COLUMNS(n for ordinality, x NUMBER PATH'$'));
  COUNT(*)
----------
   1000000
Re: Puzzle n°00 - Row generator * [message #640209 is a reply to message #640208] Thu, 23 July 2015 10:23 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
/forum/fa/2115/0/

I learnt something new!
Re: Puzzle n°00 - Row generator * [message #672703 is a reply to message #640209] Thu, 25 October 2018 03:46 Go to previous messageGo to next message
neiljakson
Messages: 1
Registered: October 2018
Junior Member
Good advice here. I have begun to redraft older posts just this week, so was happy to see it listed here.
Re: Puzzle n°00 - Row generator * [message #673111 is a reply to message #672703] Tue, 06 November 2018 14:17 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
What version are you on? Works fine for me. And you don't need x in COLUMNS unless you plan to use it:

SQL> select version from v$instance;

VERSION
-----------------
12.2.0.1.0

SQL> SELECT n FROM JSON_TABLE('['||REPLACE(LPAD(1,5,1),1,'1,')||1||']','$[*]'COLUMNS(n for ordinality, x NUMBER PATH'$'));

         N
----------
         1
         2
         3
         4
         5
         6

6 rows selected.

SQL> SELECT n FROM JSON_TABLE('['||REPLACE(LPAD(1,5,1),1,'1,')||1||']','$[*]'COLUMNS(n for ordinality));

         N
----------
         1
         2
         3
         4
         5
         6

6 rows selected.

SQL> 

SY.
Re: Puzzle n°00 - Row generator * [message #685898 is a reply to message #673111] Sun, 17 April 2022 12:36 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
As you said, real multiplicator is LPAD and generating loooong string isn't very efficient. XMLTABLE + FLWOR (which isn't most efficient row generator either) is more efficient (at least for larg(er) N):

WITH T AS (
           SELECT 5 FACTOR FROM DUAL
          )
SELECT  ROWNUM N
  FROM  T,
        XMLTABLE(
                 '1 to xs:integer(.)'
                 PASSING FACTOR
                )
/

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

SQL>
SY.
Re: Puzzle n°00 - Row generator * [message #685899 is a reply to message #685898] Sun, 17 April 2022 13:56 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
And for multiple row source table:

WITH T AS (
           SELECT 1 ID,5 FACTOR FROM DUAL UNION ALL
           SELECT 2,3 FROM DUAL
          )
SELECT  T.ID,
        X.N
  FROM  T,
        XMLTABLE(
                 '1 to xs:integer(.)'
                 PASSING FACTOR
                 COLUMNS
                   N FOR ORDINALITY
                ) X
/

 ID          N
--- ----------
  1          1
  1          2
  1          3
  1          4
  1          5
  2          1
  2          2
  2          3

8 rows selected.

SQL>
SY.
Re: Puzzle n°00 - Row generator * [message #685906 is a reply to message #462302] Thu, 21 April 2022 01:05 Go to previous messageGo to next message
khawja_bilalahmed
Messages: 7
Registered: August 2008
Location: KHI
Junior Member
Very good techniques explained and presented
Re: Row generator [message #686891 is a reply to message #291171] Sun, 29 January 2023 19:35 Go to previous message
mathguy
Messages: 106
Registered: January 2023
Senior Member
The most common way to generate rows, and the one that works in old versions, is to select ROWNUM or LEVEL from DUAL, connecting by LEVEL <= &N. This has already been mentioned in this thread.

An important improvement if we need a large number of rows, which I haven't seen here, is to generate a small view in this manner (with 100 to 1000 rows), and then to select ROWNUM from a cartesian product. This will work faster, and it will allow generating more rows than the simple form of the idea (without cartesian joins).

If N is too large, SELECT ROWNUM FROM DUAL CONNECT BY LEVEL <= &N fails with ORA-30009, Not enough memory for CONNECT BY operation. On an old machine running Oracle Linux 7.9 and Oracle 12.2.0.1, this happens (for me) for N close to 1.6 million. On a very new Windows 11 machine running Oracle 19.3.0.0.0 it fails for N close to 7.3 million. I am not smart enough to be able to tell if the difference is the Oracle version, or simply more memory being available; but in any case, at some point the row-generating process breaks up. Not so if we use a cartesian product, something like this:

with   g as (select rownum from dual connect by level <= 1e3)
select rownum from g cross join g cross join g
where  rownum <= 2e8;
(assuming we want to generate 200 million rows).

Moreover, even when the "simple" row-generating process does work, the cartesian product approach is non-trivially faster. For example, comparing the following queries:

select sum(rownum) from dual connect by level <= 1.5e6;

with g as (select level from dual connect by level <= 1e3)
  select sum(rownum) from g cross join g cross join g
  where  rownum <= 1.5e6
;
the latter runs three times faster on my machine. While the times are relatively short, this may be important if the row-generating process must be performed repeatedly.
Previous Topic: Getting same sequence number in select query
Next Topic: How to use [code] tags and make your code easier to read
Goto Forum:
  


Current Time: Thu Mar 28 06:22:42 CDT 2024