Re: Senior Oracle DBA now Learning InformixbyRequestofCompany

Ian Michael Gumby wrote: > > Mark, > > I don't know why I'm not seeing your response via the gateway... > >> Speaking of Oracle ... PL/SQL is clunky. Heck ACE was a lot better... >> > PL/SQL is actually pretty sublime as a language. I don't think the > comparison to ACE is accurate. I think ACE was more like the SQL*PLus > language extensions in Oracle (which are a little clunky). > > SQL*Plus appears to be the interpreter. But PL/SQL is chunky as a language. > > Granted I'm not an expert by any means, but there are some aspects to > the language that are just plain ugly. Having written both languages I can simply say ... they are roughly identical. Your inability to understand the difference between a command-line tool and a language indicates a clear lack of working experience with Oracle. The following "clunky" code: CREATE OR REPLACE PROCEDURE test IS TYPE myarray IS TABLE OF parent%ROWTYPE; l_data myarray; CURSOR r IS SELECT part_num * 10, part_name FROM parent; BEGIN OPEN r; LOOP FETCH r BULK COLLECT INTO l_data LIMIT 1000; FORALL i IN 1..l_data.COUNT INSERT INTO child VALUES l_data(i); EXIT WHEN r%NOTFOUND; END LOOP; COMMIT; CLOSE r; END test; / fetches, modifies, and inserts 100,000 records on my T43 ThinkPad in 0.28 seconds. If you have some aethetically pleasing code that will run more efficiently on the same hardware I'd sincerely like to see it. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org

DA Morgan said: > If you have some aethetically pleasing code that will > run more efficiently on the same hardware I'd sincerely like to see it. 10 PRINT "Oracle sucks" 20 GOTO 10 -- Bye now, Obnoxio "I don't read newspapers anymore except the local rag which I do weekly to cheer myself trying to see if anyone I hate has been stabbed." -- Horribilis XVI -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

Obnoxio The Clown wrote: > DA Morgan said: >> If you have some aethetically pleasing code that will >> run more efficiently on the same hardware I'd sincerely like to see it. > > 10 PRINT "Oracle sucks" > 20 GOTO 10 I'm not interested in your religion or your elegant coding. www.dice.com www.monster.com www.hotjobs.com Only artists can afford to starve for their art. <g> -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org

DA Morgan said: >> DA Morgan said: >>> If you have some aethetically pleasing code that will >>> run more efficiently on the same hardware I'd sincerely like to see it. >> > I'm not interested in your religion or your elegant coding. So why did you ask, then? -- Bye now, Obnoxio "I don't read newspapers anymore except the local rag which I do weekly to cheer myself trying to see if anyone I hate has been stabbed." -- Horribilis XVI -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

DA Morgan wrote: > Ian Michael Gumby wrote: >> >> Mark, >> >> I don't know why I'm not seeing your response via the gateway... >> >>> Speaking of Oracle ... PL/SQL is clunky. Heck ACE was a lot better... >>> >> PL/SQL is actually pretty sublime as a language. I don't think the >> comparison to ACE is accurate. I think ACE was more like the SQL*PLus >> language extensions in Oracle (which are a little clunky). >> >> SQL*Plus appears to be the interpreter. But PL/SQL is chunky as a >> language. >> >> Granted I'm not an expert by any means, but there are some aspects to >> the language that are just plain ugly. > > Having written both languages I can simply say ... they are roughly > identical. Your inability to understand the difference between a > command-line tool and a language indicates a clear lack of working > experience with Oracle. > > The following "clunky" code: > > CREATE OR REPLACE PROCEDURE test IS > > TYPE myarray IS TABLE OF parent%ROWTYPE; > l_data myarray; > > CURSOR r IS > SELECT part_num * 10, part_name FROM parent; > > BEGIN > OPEN r; > LOOP > FETCH r BULK COLLECT INTO l_data LIMIT 1000; > FORALL i IN 1..l_data.COUNT > INSERT INTO child VALUES l_data(i);> EXIT WHEN r%NOTFOUND; > END LOOP; > COMMIT; > CLOSE r; > END test; > / > > fetches, modifies, and inserts 100,000 records on my T43 ThinkPad > in 0.28 seconds. If you have some aethetically pleasing code that will > run more efficiently on the same hardware I'd sincerely like to see it. *blink* Isn't that the same as INSERT INTO child SELECT part_num * 10, part_name FROM parent I'm on record for claiming that Oracle is the best procedural DBMS there is. If you are telling us that the half a page of procedural code beats a straight insert from select in Oracle you prove my case. And yes, this code is clunky. It's special purpose. E.g. FORALL and BULK COLLECT work only on individual statements and specific ones at that. An array is an SQL standard data type. Allowing it to aggregate within SQL will scale better and work anywhere in SQL, not just for bind-out. Instead of FORALL an SQL Standard UNNEST operator should be used. It is again more versatile. E.g. you can use multiple UNNEST in one statement, plug them into any SQL statements etc. The PL/SQL way is to keep SQL stupid and tune in PL/SQL. Therefore all the investment in bulk-bind-in/out. But PL/SQL cannot be parallelized. It's in the nature of procedural languages. In short, bulk-processing solves a home made problem. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab WAIUG Conference http://www.iiug.org/waiug/present/Forum2006/Forum2006.html

Obnoxio The Clown wrote: > DA Morgan said: >>> DA Morgan said: >>>> If you have some aethetically pleasing code that will >>>> run more efficiently on the same hardware I'd sincerely like to see it. >> I'm not interested in your religion or your elegant coding. > > So why did you ask, then? Hoping next year when I'm in the Cotswolds you'll join me for that scotch. <g> -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org

Serge Rielau wrote: > And yes, this code is clunky. > It's special purpose. E.g. FORALL and BULK COLLECT work only on > individual statements and specific ones at that. Yeah ... statements containing INSERT, UPDATE, and DELETE. Rather rarified in your world perhaps? <g> -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org

Serge Rielau wrote: > DA Morgan wrote: >> Ian Michael Gumby wrote: >>> >>> Mark, >>> >>> I don't know why I'm not seeing your response via the gateway... >>> >>>> Speaking of Oracle ... PL/SQL is clunky. Heck ACE was a lot better... >>>> >>> PL/SQL is actually pretty sublime as a language. I don't think the >>> comparison to ACE is accurate. I think ACE was more like the SQL*PLus >>> language extensions in Oracle (which are a little clunky). >>> >>> SQL*Plus appears to be the interpreter. But PL/SQL is chunky as a >>> language. >>> >>> Granted I'm not an expert by any means, but there are some aspects to >>> the language that are just plain ugly. >> >> Having written both languages I can simply say ... they are roughly >> identical. Your inability to understand the difference between a >> command-line tool and a language indicates a clear lack of working >> experience with Oracle. >> >> The following "clunky" code: >> >> CREATE OR REPLACE PROCEDURE test IS >> >> TYPE myarray IS TABLE OF parent%ROWTYPE; >> l_data myarray; >> >> CURSOR r IS >> SELECT part_num * 10, part_name FROM parent; >> >> BEGIN >> OPEN r; >> LOOP >> FETCH r BULK COLLECT INTO l_data LIMIT 1000; >> FORALL i IN 1..l_data.COUNT >> INSERT INTO child VALUES l_data(i);>> EXIT WHEN r%NOTFOUND; >> END LOOP; >> COMMIT; >> CLOSE r; >> END test; >> / >> >> fetches, modifies, and inserts 100,000 records on my T43 ThinkPad >> in 0.28 seconds. If you have some aethetically pleasing code that will >> run more efficiently on the same hardware I'd sincerely like to see it. > *blink* Isn't that the same as > INSERT INTO child > SELECT part_num * 10, part_name FROM parent> > I'm on record for claiming that Oracle is the best procedural DBMS there > is. If you are telling us that the half a page of procedural code beats > a straight insert from select in Oracle you prove my case. > > And yes, this code is clunky. > It's special purpose. E.g. FORALL and BULK COLLECT work only on > individual statements and specific ones at that. > An array is an SQL standard data type. Allowing it to aggregate within > SQL will scale better and work anywhere in SQL, not just for bind-out. > Instead of FORALL an SQL Standard UNNEST operator should be used. It is > again more versatile. > E.g. you can use multiple UNNEST in one statement, plug them into any > SQL statements etc. > The PL/SQL way is to keep SQL stupid and tune in PL/SQL. Therefore all > the investment in bulk-bind-in/out. > But PL/SQL cannot be parallelized. It's in the nature of procedural > languages. > In short, bulk-processing solves a home made problem. > > Cheers > Serge One more thing Serge ... if you have non-clunky code that will equal its performance I'd really like to see it. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org