Alternative to all_objects in Oracle 9i and Up to generate rows

---

As Oracle guru Tom Kyte says of his daily Oracle experiences, I have learned something new about Oracle today. This trick works in Oracle databases 9i and up (like a fine wine this one will actually get better as you move to newer versions).

From a fantastic post on asktom.oracle.com entitled Can there be an infinite DUAL? I found a great alternative to using all_objects to generate X number of rows in a table.

Oftentimes, say for a Pivot Table, we will want to generate a table with X number of rows which we will Cartesian Product with some other table. To do this using all_objects (or some other table that is guaranteed to have a sufficiently large number of rows) it would look like this:

select rownum rn
from all_objects
where rownum < 10;

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

9 rows selected

Now using a cool trick with 9i and up we can produce the same thing like so:

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


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

9 rows selected

Why do we care if the results are the same? The consitent gets for the second approach are much lower and they scale fantastically. Guru Kyte says that this will be even faster in 10g thanks to something called FAST DUAL. If you don't believe me try it for 10,000 rows and check out the trace for both approaches.

Very cool stuff. Also did you know that Tom Kyte is blogging now at tkyte.blogspot.com? Very interesting to see a different side of this technology wizard.