|
-
The simple solution to find missing numbers is using cube:
SQL> select * from t1;
ID
----------
10
13
14
17
19
20
6 rows selected.
SQL>
1 select rn
2 from (select /*+ no_merge */ rownum as rn
3 from ( select 1 from dual group by cube (1,1,1,1,1)) a,
4 ( select 1 from dual group by cube (1,1,1,1,1,1)) b
5 )
6 where rn >= (select min(id) from t1) and
7 rn <= (select max(id) from t1)
8 minus
9* select id from t1
SQL> /
RN
----------
11
12
15
16
18
Technically you can generate millions of millions of rows using cube.
On my IBM P690 unix AIX machine (8 cpus with 1500 MH spped, 16 GB RAM), I generated 4 million rows table in 110 seconds.
If you want to generate more number of rows, just add 1s in the cube function.
Tamil
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|