SQL & PL/SQL

select * from 
(select * from dual connect by level <= 1000)

select rownum
 from dual
connect by level <= 10

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.

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.

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. :-)

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?

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.

that's pretty easy. Thx to you all

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;

This article shows both the MODEL and DUAL examples, but for speed, a pipelined function is pretty good too...

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

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

nice link! thanks for sharing this ;-)

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

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

Here you'll find a whole lot of ways for generating integer series along with perfomance measurements:

Integer Series Generator

Thanks for sharing SnippetyJoe ;)

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.

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

:-)

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. :-) )

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.

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.

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.

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

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.

&#91;url=http://www.yourserver.com/....] some friendly text &#91;/url]

;)

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

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

> 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.

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>

Good point padders. I re-ran my tests using 100,000 rows instead of 100 and found the performance metrics looked quite different. For 100,000 rows CONNECT BY LEVEL not only ran faster, but it used fewer latches than MODEL. As far as the CUBE method goes, I had to kill that test after 10 minutes because it just kept running. I'll post these additional results on SQL Snippets in my next upload.

Guess I'll have to take back that statement about MODEL being faster. Thanks for pointing that out. Using CONNECT BY without PRIOR still makes me nervous though. ;-)

Anthony Wilson wrote:
I had a look at that paper you linked to. Ouch! My head hurts just thinking about it.

In case you're interested, here's what I wrote about it on the Tom Kyte blog post that asked for feedback on that paper.

"I've never done the kind of pattern matching described in the paper, so excuse my ignorance if I get anything wrong here, but it sounds to me like this new feature would effectively provide the ability to do regular expression pattern matching over rows of values.

If that's the case then, instead of reinventing the regular expression wheel why not simply aggregate a column of pattern symbols into a single string and then use existing regular expression functions to search the string for specific patterns?

It's too long to show here, but I have some examples of how to do this on SQL Snippets at Drafts: Pattern Matching Over Rows. Finding a "W" pattern in a series of stock prices boils down to a query like this.

select stock_symbol
from stock_price_patterns
where day = 11
and regexp_like( pattern_string_uda, 'D+U+D+U+' ) ;

Assuming I'm not missing something, then I think Oracle's efforts would be better spent developing a built in string aggregation function than adding another feature for doing pattern matching.

HTH."


Message was edited by: SnippetyJoe - fixed typo

Avinash, your solution is great .

SELECT Row_Number() over(order by 1) FROM DUAL GROUP BY CUBE(1,2,3,4,5,6,7,8);
SELECT Row_Number() over(order by 1) FROM DUAL GROUP BY Rollup(1,2,3,4,5,6,7,8);

SELECT Row_Number() over(order by 1)
  FROM DUAL
GROUP BY CUBE(1,2,3,4,5,6,7,8,9,10)
having grouping_ID(1,2,3,4,5,6,7,8,9,10) <= 123-1;

Well as the previous poster has re-awoken this thread with an edit (not sure what) I've had a re-read and I'm still pondering how:

SQL> select integer_value
  2  from   dual
  3  where  1=2
  4  model
  5    dimension by ( 0 as key )
  6    measures     ( 0 as integer_value )
  7    rules upsert ( integer_value[ for key from 1 to 10 increment 1 ] = cv(key) )
  8  ;

INTEGER_VALUE
-------------
            1
            2
            3
            4
            5
            6
            7
            8
            9
           10

10 rows selected.

SQL>

Can actually produce results as the clause:

where 1=2

should surely negate any results from being produced, regardless of the model clause being there.

?

BluShadow,

When using the model clause you have to think of the where clause as specifying what data goes in the model, so in this case no data. Then the model generates new cells using the for construct, which are translated back as rows when done modelling.

Hope this helps.

Regards,
Rob.

The query result would be the same as you put RETURN UPDATED ROWS clause.
But there was some perfomance difference, If I'm not mistaken.

Yes, there is. The difference would be that Oracle would now have 1 row to put into the model initially and it would have to administer which rows where original and which ones are new. The query as is starts blank, generates 10 cells, and converts them back as rows. A little less work than using the RETURN UPDATED ROWS clause.

But, as padders has showed, the "connect by dual" number generator is a much more performant alternative, so this one shouldn't be used anyway.

Regards,
Rob.

select * from
(select * from dual connect by level <=1000)

what is you of connect and
what is usage of level in this
syntax.

Rob,
Thanks, that's enlightened my understanding of the model clause a little further. Makes sense now. ;)

SQL> ed
Wrote file afiedt.buf

1 SELECT rownum
2 FROM Dual
3* CONNECT BY rownum <= 10
SQL> /

ROWNUM
------
1

It showing only 1 not 1 to 10

Well, If I fully understand what you mean - I can't agree.
Cause when you use e.g. WHERE 1=2 in the model clause it would return not only inserted values, but also updated - so the process would be quite the same - it also have to recognize what values were updated and inserted, and what values - were not touched.

SQL> with t as (select 1 num from dual union all
  2             select 2 from dual union all
  3             select 3 from dual union all
  4             select 4 from dual)
  5             --
  6             select * from t
  7  --            where 1=2
  8              model
  9               return updated rows
 10               dimension by (num rn)
 11               measures(num)
 12                rules(num[1]=0,
 13                      num[3]=0,
 14                      num[5]=-1,
 15                      num[6]=-1)
 16  /

        RN        NUM
---------- ----------
         1          0
         3          0
         6         -1
         5         -1

SQL> 
SQL> with t as (select 1 num from dual union all
  2             select 2 from dual union all
  3             select 3 from dual union all
  4             select 4 from dual)
  5             --
  6             select * from t
  7              where 1=2
  8              model
  9  --             return updated rows
 10               dimension by (num rn)
 11               measures(num)
 12                rules(num[1]=0,
 13                      num[3]=0,
 14                      num[5]=-1,
 15                      num[6]=-1)
 16  /

        RN        NUM
---------- ----------
         6         -1
         5         -1
         3          0
         1          0

SQL> 

That's because you are using an earlier version of Oracle.

Try:

SELECT rn FROM (SELECT rownum rn FROM DUAL CONNECT BY ROWNUM <= 10);

;)

Ooops, realized that I'm wrong!
They are all four rows inserted - you are right, Rob.

If we use symbolic reference - we can easily see the difference:

SQL> with t as (select 1 num from dual union all
  2             select 2 from dual union all
  3             select 3 from dual union all
  4             select 4 from dual)
  5             --
  6             select * from t
  7  --            where 1=2
  8              model
  9               return updated rows
 10               dimension by (num rn)
 11               measures(num)
 12                rules(num[rn=1]=0,
 13                      num[rn=3]=0,
 14                      num[5]=-1,
 15                      num[6]=-1)
 16  /

        RN        NUM
---------- ----------
         1          0
         3          0
         6         -1
         5         -1

SQL> 
SQL> with t as (select 1 num from dual union all
  2             select 2 from dual union all
  3             select 3 from dual union all
  4             select 4 from dual)
  5             --
  6             select * from t
  7              where 1=2
  8              model
  9  --             return updated rows
 10               dimension by (num rn)
 11               measures(num)
 12                rules(num[rn=1]=0,
 13                      num[rn=3]=0,
 14                      num[5]=-1,
 15                      num[6]=-1)
 16  /

        RN        NUM
---------- ----------
         6         -1
         5         -1

SQL> 

The reason I used that condition is explained in the section labelled "WHERE 1=2" at SQL Snippets: Integer Series Generators - MODEL Method.

Volder's right about RETURN UPDATED ROWS though. It would produce the same results as using WHERE 1=2.

--
Joe Fuda
SQL Snippets

The best solution is CONNECT BY as other said,

But if you forget it , one simple trick is just select rownum from dba_tables where rownum<10000.

Regards
Helio Dias
http://heliodias.com

No, Joe, I wasn't right. And I gave an example in my post previous to your post :)

Sorry Volder, I don't follow. I was saying that this

select integer_value
from   dual
-- where 1=2
model
  RETURN UPDATED ROWS
  dimension by ( 0 as key )
  measures     ( 0 as integer_value )
  rules upsert ( integer_value[ for key from 1 TO 3 increment 1 ] = cv(key) )
;

INTEGER_VALUE
-------------
            1
            2
            3

gives the same results as this

select integer_value
from   dual
where 1=2
model
--  RETURN UPDATED ROWS
  dimension by ( 0 as key )
  measures     ( 0 as integer_value )
  rules upsert ( integer_value[ for key from 1 TO 3 increment 1 ] = cv(key) )
;

INTEGER_VALUE
-------------
            1
            2
            3

As an aside, for many cases we don't need either clause. The following version works fine for queries with hardcoded limits that always return one or more rows.

select integer_value
from   dual
model
  dimension by ( 1 as key )            -- use "1" here instead of "0"
  measures     ( 1 as integer_value )  -- use "1" here instead of "0"
  rules upsert ( integer_value[ for key from 1 TO 3 increment 1 ] = cv(key) )
;

INTEGER_VALUE
-------------
            1
            2
            3

Personally I prefer the WHERE 1=2 approach because it seems cleaner and safer to start with an empty set right before the MODEL rules are applied. That's just me though.

--
Joe Fuda
SQL Snippets

SELECT Row_Number() over(order by 1) FROM DUAL CONNECT BY ROWNUM <11;

Ok, so you've dragged up an old old thread for what reason?

There's absolutely no benefit in using the analytical function row_number() to get row numbers out of that query when rownum itself will do the job. Using that function will only serve to decrease performance.