Sometimes, you would have a situation where you need to generate a table with N number of rows that you can use as the basis to join and restrict other tables.
For example, to get the 100 dates starting from a certain date as rows, a typical technique is to select ROWNUM from all_objects to generate 100 rows and come up with dates based on that. However, this technique would not work if the number of rows you need is huge. Say you needed 50,000 rows generated but your database does not have 50,000 objects, the other techniques are needed.
This is a simple yet powerful tip that even some seasoned database professionals are not aware of
SELECT ROWNUM seq FROM ( SELECT 1 FROM dual CONNECT BY LEVEL <= 100 )
Would generate 100 rows
Seq 1 2 3 . . 100
Expanding on the above example, to get the 100 dates from today, this would work
WITH days AS ( SELECT ROWNUM seq FROM (SELECT 1 FROM dual CONNECT BY LEVEL <= 100) ) SELECT SYSDATE + seq FROM days
8/21/2012 8/22/2012 8/23/2012 8/24/2012 8/25/2012 …. …. 11/28/2012
Now, only your imagination is the limit on how this trick can be used.