Forum Stats

  • 3,758,161 Users
  • 2,251,347 Discussions
  • 7,870,076 Comments

Discussions

Multi-Rows from DUAL

124

Comments

  • 494018
    494018 Member Posts: 227
    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
    494018 Member Posts: 227
    edited Apr 13, 2007 1:05PM
    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
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Aug 16, 2007 2:50AM
    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
    BluShadow Member, Moderator Posts: 41,383 Red Diamond
    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
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    edited Aug 16, 2007 7:36AM
    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
    572471 Member Posts: 984 Green Ribbon
    edited Aug 16, 2007 7:13AM
    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
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    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
    589132 Member Posts: 203
    select * from
    (select * from dual connect by level <=1000)

    what is you of connect and
    what is usage of level in this
    syntax.
  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond
    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
    589132 Member Posts: 203
    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
This discussion has been closed.