Re: Puzzle n°00 - Row generator * [message #640208 is a reply to message #640207] |
Thu, 23 July 2015 10:16 |
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 #673111 is a reply to message #672703] |
Tue, 06 November 2018 14:17 |
Solomon Yakobson
Messages: 3273 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 |
Solomon Yakobson
Messages: 3273 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 |
Solomon Yakobson
Messages: 3273 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: Row generator [message #686891 is a reply to message #291171] |
Sun, 29 January 2023 19:35 |
|
mathguy
Messages: 107 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.
|
|
|