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
-
Re. "the value 0 runs without error, except, that 1 row is returned", I guess it's a matter of semantics (though to be fair, I did say the query "will not work as expected", not "it will generate an error"). In my mind returning 1 row when you ask for 0 rows is an error. It won't throw an ORA error, but its an error in logic. If your application can tolerate that then fine, as long as you're aware of the inconsistency.
-
Re. "this article shows both the MODEL and DUAL examples, but for speed, a pipelined function is pretty good too" and "here one more method which can be used ... SELECT ... CUBE", there are even more methods to choose from, using a collection type constructor for example. As user "michaels" kindly pointed out, I discuss 8 different approaches in my "Integer Series Generators" tutorial series at http://www.sqlsnippets.com/en/topic-11833.html. This series covers these topics.
Integer Table Method
MODEL Method
ROWNUM + a Big Table Method
CONNECT BY LEVEL Method
CUBE Method
Type Constructor Expression Method
Type Constructor + Cartesian Product Method
Pipelined Function Method
The performance comparison chart at the end of this series shows that, while pipelined functions may be fast, they won't scale as well as MODEL. -
You're welcome michaels. Thanks for the plug.
BTW, what syntax did you use to insert the link into your message? I can't seem to figure out how to do that on this forum. -
oh, I read to fast. I thought it'll raise an error.
-
Methinks you ain't seen nuthin' yet...Great paper, don't you think Anthony ;-)
:-)
I think it is a sign that Oracle is complete now, given that these kind of things get invented now ...
Nice feature though.
Regards,
Rob. -
BTW, what syntax did you use to insert the link into your message?[url=http://www.yourserver.com/....] some friendly text [/url]
-
nice feature?... my head hurts! ... ;-)
-
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 -
> 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. -
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>
This discussion has been closed.