Forum Stats

  • 3,741,297 Users
  • 2,248,407 Discussions
  • 7,861,732 Comments

Discussions

Multi-Rows from DUAL

Gerd Volberg
Gerd Volberg Member Posts: 4,558 Blue Ribbon
edited Nov 19, 2010 11:02AM in SQL & PL/SQL
I need an easy select from DUAL, which gives me more than one row.

e.g. 1000 rows or 4500 rows....


I know that one of the best solutions was something with an CONNECT BY PRIOR and a WHERE ROWNUM <= 1000 e.g.


But what was the complete statement?

thx 4 solutions
Gerd
«1

Comments

  • 229023
    229023 Member Posts: 2,305
    select * from 
    (select * from dual connect by level <= 1000)
  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    select rownum
    from dual
    connect by level <= 10
  • 494018
    494018 Member Posts: 227
    Be careful when using this approach. There are a few things that can trip you up.

    1. If you use a bind variable in place of the value 1000 and your bind variable value can ever be 0 the query will not work as expected.

    2. People have reported bugs with these types of queries in various Oracle versions.

    3. There is some debate about whether the syntax is legal and whether it will continue to work in future versions.

    Given these risks you may want to consider using this alternative query instead (assuming you are running on 10g).
    select integer_value
    from   dual
    where  1=2
    model
      dimension by ( 0 as key )
      measures     ( 0 as integer_value )
      rules upsert ( integer_value[ for key from 1 to 10 increment 1 ] = cv(key) )
    ;
    
    INTEGER_VALUE
    -------------
                1
                2
                3
                4
                5
                6
                7
                8
                9
               10
    As an added bonus, the MODEL query runs faster than the CONNECT BY LEVEL query.

    You can find more details (including a fix for issue #1) at

    http://www.sqlsnippets.com/en/topic-11821.html (SQL Snippets: Integer Series Generators - CONNECT BY LEVEL Method)

    and

    http://www.sqlsnippets.com/en/topic-11979.html (SQL Snippets: Integer Series Generators - MODEL Method).

    Hope this helps.
  • William Robertson
    William Robertson Member Posts: 9,564 Bronze Crown
    Neat.

    Is it just me or is MODEL() evil though? I notice it gives the same results regardless of the values you put in the DIMENSION BY and MEASURES clauses, whatever they do. It just seems like a short step from MODEL to MDX or, heaven help us, XQuery.
  • 494018
    494018 Member Posts: 227
    MODEL Evil???? Oh no, no, no. Au contraire mon ami. MODEL is the best thing since sliced bread. It's my new favourite feature. It slices, it dices, it loops, it straggs ... it does all sorts of neat things. It's like having PL/SQL in your queries without the context switches.

    I think people may not appreciate MODEL's usefulness because of the learning curve associated with it. MODEL packs a lot of functionality into one little clause so it's hard to get your head around it unless you learn it the right way. That's one of the reasons I wrote a really easy tutorial series on MODEL that goes through each component one step at a time. Check it out at http://www.sqlsnippets.com/en/topic-11663.html and then tell me if you still think it's evil or simply a misunderstood little feature. :-)
  • William Robertson
    William Robertson Member Posts: 9,564 Bronze Crown
    It's not so much a "little feature" as a new language. It seems to have nothing to do with SQL. I did once hope SQL could have some sort of inline function facility, which just goes to show you should be careful what you wish for.

    I guess I'll have to buy a 500 page book on MODEL() one day. I'll see if I can work through your examples (thanks btw) over the next five years or so.
  • Model clause is like a mini rule based system, right? Here is an exercise, write a transitive closure of a graph. Those are just 2 rules:

    AllEdges(x,y) :- Edges(x,y)
    AllEdges(x,y) :- Edges(x,y) AllEdges(x,y)

    Can you do it with Model clause?
  • William Robertson
    William Robertson Member Posts: 9,564 Bronze Crown
    It takes four lines of deeply cryptic code involving "measures", "dimensions" and an "upsert" just to get the numbers from 1 to 10 out of it, so I imagine the transitive closure of a graph isn't going to be pretty.

    I'm guessing "transitive closure", "graph" and ":-" mean something to mathematicians.
  • Gerd Volberg
    Gerd Volberg Member Posts: 4,558 Blue Ribbon
    that's pretty easy. Thx to you all
  • Gerd Volberg
    Gerd Volberg Member Posts: 4,558 Blue Ribbon
    I've tested this version and the value 0 runs without error, except, that 1 row is returned

    SELECT Level LVL
    FROM Dual
    CONNECT BY Level <= maxValue;
  • ABB
    ABB Member Posts: 360
    This article shows both the MODEL and DUAL examples, but for speed, a pipelined function is pretty good too...
  • BluShadow
    BluShadow Member, Moderator Posts: 41,088 Red Diamond
    I personally use:
    SQL> ed
    Wrote file afiedt.buf
    
      1  SELECT rownum
      2  FROM Dual
      3* CONNECT BY rownum <= 10
    SQL> /
    
        ROWNUM
    ----------
             1
             2
             3
             4
             5
             6
             7
             8
             9
            10
    
    10 rows selected.
    
    SQL>
    I know people go on about it not being documented by Oracle etc. but I know that in 10.1.x versions of Oracle it had a bug where it returned 1 extra row (the above query would have given 11 rows), but in 10.2.x versions Oracle have fixed this so it now gives the correct result. I would assume that if they've gone to the trouble of fixing it, then it's something they intend to be there.

    ;)
  • 290833
    290833 Member Posts: 691
    edited Apr 4, 2007 7:17AM
    I personally choose to believe the documentation when it says that connect by MUST be followed by PRIOR, although it's really a personal choice whether you choose to believe the documentation or the software as to what is the "correct" behaviour. There's many examples either way, I am sure.

    I am more comfortable with the MODEL version since it's a documented, supported way to "create" rows which aren't present in the database.

    cheers,
    Anthony
  • Avinash Tripathi
    Avinash Tripathi Member Posts: 1,614 Bronze Badge
    Hi,
    Here one more method which can be used.
    SQL> ed
    Wrote file afiedt.buf

    1 SELECT rownum FROM (
    2* SELECT 1 FROM DUAL GROUP BY CUBE(1,2))
    SQL> /

    ROWNUM
    ----------
    1
    2
    3
    4

    SQL> ed
    Wrote file afiedt.buf

    1 SELECT rownum FROM (
    2* SELECT 1 FROM DUAL GROUP BY CUBE(1,2,3))
    SQL> /

    ROWNUM
    ----------
    1
    2
    3
    4
    5
    6
    7
    8

    8 rows selected.

    SQL>
    SQL> ed
    Wrote file afiedt.buf

    1 SELECT rownum FROM (
    2* SELECT 1 FROM DUAL GROUP BY CUBE(1,2,3,4))
    SQL> /

    ROWNUM
    ----------
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16

    16 rows selected.

    SQL>
    Regards
  • I personally choose to believe the documentation when
    it says that
    http://pages.citebite.com/e1k4e8r7q6wua
    nice link! thanks for sharing this ;-)

    MUST be followed by PRIOR
    well, this is the way to define the hierarchy. If you do not define a hierarchy, than you have a loop. Even if one may pretend in some versions of Oracle you can you level or rownum to make the loop non-infinite, it is still a loop and it should generate an ORA-01436: CONNECT BY loop in user data
  • Gerd Volberg
    Gerd Volberg Member Posts: 4,558 Blue Ribbon
    using CUBE is the slowest method I know.

    If you need 5000 records you have to write

    SELECT rownum FROM (
    SELECT 1 FROM DUAL GROUP BY CUBE(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1))
    WHERE ROWNUM <= 5000

    Execution time : 250 seconds



    SELECT Level LVL
    FROM Dual
    CONNECT BY Level <= 5000

    Execution time : 1 second
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Here you'll find a whole lot of ways for generating integer series along with perfomance measurements:

    Integer Series Generator

    Thanks for sharing SnippetyJoe ;)
  • 290833
    290833 Member Posts: 691
    Is it just me or is MODEL() evil though?
    Methinks you ain't seen nuthin' yet...

    :-)
  • 494018
    494018 Member Posts: 227
    Hmmm, looks like this thread has had quite a bit of activity over night. Lots to discuss. I'll post separate messages for each user to keep the conversations distinct.

    Re. "little feature" versus "new language", o.k., you're right William. I guess calling it a little feature is a bit of an understatement. Even if it is a new language though, I still think it's one worth learning given its power.

    Re. "four lines of cryptic code ... just to get the numbers from 1 to 10", I'd say it's no more cryptic than CONNECT BY LEVEL <= 10. If fact, once you get past learning a few new terms like "dimension" and "measure", my solution boils down to a simple loop. Let me take a stab at explaining it.

    First, the "SELECT ... from DUAL where 1=2" is simply a little trick I use to start with an empty result set. We all know that "SELECT * FROM DUAL WHERE 1=2" returns no rows.

    Next the MODEL clause kicks in. MODEL basically lets you treat the result set of the SELECT FROM DUAL as an array. To reference elements in the array you use syntax like "measure[dimension]". You can either use columns from the base table as measures and dimensions or you can roll your own columns. In my query I created one column called "key" and one called "integer_value". It's like creating columns based on expressions in a query, e.g. "select 0 as key, 0 as integer_value ...".

    Now that we have an empty set and we've told the SQL engine which column will act as the dimension in our array and which will act as the measure we use a simple FOR loop to create new array elements. UPSERT tells Oracle that, if any of the cells we change with our rule does not exist, insert the cell. In my query, since the set starts out empty, all the cells referenced in the rule will be inserted. That's how we get 10 rows in the final result set. Finally the "cv(key)" expression is simply a call to a function called CV(), "Current Value", which returns the current value of KEY in each loop iteration.

    O.k., I admit that's a lot of verbiage, but I think the underlying concepts are fairly simple once you know the basics of how MODEL works.
  • 494018
    494018 Member Posts: 227
    Re. "the transitive closure of a graph", sorry Vadim, haven't got a clue what that is. I'm just a simple little code monkey. (BTW, your new SQL Design Patterns book looks like a real gem. I'm going to have to get me a copy some day. :-) )
  • 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 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
    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.

    Regards,
    Rob.
This discussion has been closed.