Multi-Rows from DUAL

Re. "this article shows both the MODEL and DUAL examples, but for speed, a pipelined function is pretty good too" and "here one more method which can be used ... SELECT ... CUBE", there are even more methods to choose from, using a collection type constructor for example. As user "michaels" kindly pointed out, I discuss 8 different approaches in my "Integer Series Generators" tutorial series at http://www.sqlsnippets.com/en/topic-11833.html. This series covers these topics.

Integer Table Method
MODEL Method
ROWNUM + a Big Table Method
CONNECT BY LEVEL Method
CUBE Method
Type Constructor Expression Method
Type Constructor + Cartesian Product Method
Pipelined Function Method

The performance comparison chart at the end of this series shows that, while pipelined functions may be fast, they won't scale as well as MODEL.

[url=http://www.yourserver.com/....] some friendly text [/url]

Don't tell that to Larry, he might start worrying about how he's going to convince people to upgrade in the future.

Seriously though, I think there is still massive room for improvement in many areas, my main interest being the area of declaritive integrity. Not a trivial exercise though.

Cheers,
Anthony

Your tests do appear to indicate that MODEL uses relatively few latches, which implies that it would scale (i.e. run concurrently) effectively.

Note though that for large numbers of rows (e.g. 300K) CONNECT BY LEVEL appears to outperform MODEL by more than two orders of magnitude (unless this is a bug or Oracle is taking some shortcut here I am unaware of).

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
  2     v_row NUMBER := (10 ** 5) * 3;
  3     v_cnt NUMBER := 0;
  4     v_tme PLS_INTEGER := 0;
  5  BEGIN
  6     v_tme := DBMS_UTILITY.GET_TIME;
  7     SELECT COUNT (*)
  8     INTO   v_cnt
  9     FROM  (SELECT 1
 10            FROM   DUAL
 11            CONNECT BY LEVEL <= v_row);
 12     DBMS_OUTPUT.PUT_LINE ((DBMS_UTILITY.GET_TIME - v_tme) || ' hsecs elapsed.');
 13
 14     v_tme := DBMS_UTILITY.GET_TIME;
 15     SELECT COUNT (*)
 16     INTO   v_cnt
 17     FROM  (SELECT n
 18            FROM   dual
 19            WHERE  1 = 2
 20            MODEL
 21               DIMENSION BY (0 AS i)
 22               MEASURES     (0 AS n)
 23               RULES UPSERT (n [FOR i FROM 1 TO v_row INCREMENT 1] = CV (i)));
 24     DBMS_OUTPUT.PUT_LINE ((DBMS_UTILITY.GET_TIME - v_tme) || ' hsecs elapsed.');
 25  END;
 26  /
32 hsecs elapsed.
3531 hsecs elapsed.

PL/SQL procedure successfully completed.

SQL>