Forum Stats

  • 3,873,503 Users
  • 2,266,595 Discussions


Multi-Rows from DUAL



  • 494018
    494018 Member Posts: 227
    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
    494018 Member Posts: 227
    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 This series covers these topics.

    Integer Table Method
    MODEL Method
    ROWNUM + a Big Table 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
    494018 Member Posts: 227
    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
    Gerd Volberg Member Posts: 4,558 Blue Ribbon
    oh, I read to fast. I thought it'll raise an error.
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    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.

  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    BTW, what syntax did you use to insert the link into your message?
    [url=] some friendly text [/url]

  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    nice feature?... my head hurts! ... ;-)
  • 290833
    290833 Member Posts: 691
    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.

  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown
    > 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
    94799 Member Posts: 2,208
    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 - Production
      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.');
     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.
This discussion has been closed.