Multi-Rows from DUAL

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