Multi-Rows from DUAL

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>

494018

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

494018

Anthony Wilson wrote:

Is it just me or is MODEL() evil though?

Methinks you ain't seen
[url=http://asktom.oracle.com/tkyte/row-pattern-rec
ogniton-11-public.pdf]nuthin' yet...

:-)

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

Aketi Jyuuzou

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;

BluShadow

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.

?

Rob van Wijk

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.

572471

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.

?

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.

Rob van Wijk

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.

589132

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

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

BluShadow

Rob,

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.

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

589132

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

572471

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.

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> 

BluShadow

SQL> ed
Wrote file afiedt.buf

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

ROWNUM
------
1
showing only 1 not 1 to 10

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

Try:

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

572471

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> 

494018

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

Helio Dias

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

572471

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

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

494018

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

816641

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

BluShadow

user12919849 wrote:
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.