Multi-Rows from DUAL

494018

Re. "the value 0 runs without error, except, that 1 row is returned", I guess it's a matter of semantics (though to be fair, I did say the query "will not work as expected", not "it will generate an error"). In my mind returning 1 row when you ask for 0 rows is an error. It won't throw an ORA error, but its an error in logic. If your application can tolerate that then fine, as long as you're aware of the inconsistency.

494018

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.

494018

You're welcome michaels. Thanks for the plug.

BTW, what syntax did you use to insert the link into your message? I can't seem to figure out how to do that on this forum.

Gerd Volberg

oh, I read to fast. I thought it'll raise an error.

Rob van Wijk

Methinks you ain't seen nuthin' yet...

:-)

Great paper, don't you think Anthony ;-)

I think it is a sign that Oracle is complete now, given that these kind of things get invented now ...

Nice feature though.

Regards,
Rob.

MichaelS

BTW, what syntax did you use to insert the link into your message?

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

21205

nice feature?... my head hurts! ... ;-)

290833

I think it is a sign that Oracle is complete now,

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

William Robertson

> I think it is a sign that Oracle is complete now, given that these kind of things get invented now ...

LOL - yeah right. It'll be closer to being finished when they've fixed FORALL and object types, though since the campaign to fix DBMS_OUTPUT took 10 years I'm not holding my breath. They are unchanged in 11g from what I've heard.

94799

As an added bonus, the MODEL query runs faster than the CONNECT BY LEVEL query.

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>