Forum Stats

  • 3,741,301 Users
  • 2,248,408 Discussions
  • 7,861,732 Comments

Discussions

Multi-Rows from DUAL

2»

Comments

  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    BTW, what syntax did you use to insert the link into your message?
    [url=http://www.yourserver.com/....] some friendly text [/url]

    ;)
  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    nice feature?... my head hurts! ... ;-)
  • 290833
    290833 Member Posts: 691
    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
    William Robertson Member Posts: 9,564 Bronze Crown
    > 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
    94799 Member Posts: 2,208
    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
    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,088 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,088 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
  • 572471
    572471 Member Posts: 984 Green Ribbon
    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
    BluShadow Member, Moderator Posts: 41,088 Red Diamond
    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
    572471 Member Posts: 984 Green Ribbon
    edited Aug 16, 2007 8:14AM
    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
    494018 Member Posts: 227
    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
    Helio Dias Member Posts: 543 Blue Ribbon
    edited Aug 19, 2007 10:38AM
    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
    572471 Member Posts: 984 Green Ribbon
    edited Aug 19, 2007 5:30AM
    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
    494018 Member Posts: 227
    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
    816641 Member Posts: 1
    SELECT Row_Number() over(order by 1) FROM DUAL CONNECT BY ROWNUM <11;
  • BluShadow
    BluShadow Member, Moderator Posts: 41,088 Red Diamond
    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.
This discussion has been closed.