Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Multi-Rows from DUAL
Comments
-
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.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...
:-)
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. -
Can actually produce results as the clause:The query result would be the same as you put RETURN UPDATED ROWS clause.
where 1=2
should surely negate any results from being produced,
regardless of the model clause being there.
?
But there was some perfomance difference, If I'm not mistaken. -
The query result would be the same as you put RETURNYes, 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.
UPDATED ROWS clause.
But there was some perfomance difference, If I'm not
mistaken.
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,When using the model clause you have to think of theThanks, that's enlightened my understanding of the model clause a little further. Makes sense now.
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. -
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.