tag:blogger.com,1999:blog-7395977411859619892.post5579480982875952614..comments2024-01-30T09:21:06.579+01:00Comments on About Oracle: Choosing between SQL and PL/SQLRob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.comBlogger16125tag:blogger.com,1999:blog-7395977411859619892.post-87288566590263140512013-06-10T13:19:33.399+02:002013-06-10T13:19:33.399+02:00nice piece of information, I had come to know abou...nice piece of information, I had come to know about your internet site from my friend vinay, delhi,i have read atleast 12 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new post, once again hatsAnonymoushttps://www.blogger.com/profile/17313661190126567875noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-5958070777965862692009-04-09T22:26:00.000+02:002009-04-09T22:26:00.000+02:00Hi Joel,Well said. Maybe this book can convince yo...Hi Joel,<BR/><BR/>Well said. Maybe this book can convince you that business analysis easily translates to predicate calculus:<BR/>http://www.amazon.com/Applied-Mathematics-Database-Professionals-Experts/dp/1590597451/ref=pd_sxp_f_pt<BR/><BR/>Although I have to admit I don't usually use predicate calculus when solving some problem. Chris Date's seminar will probably show you how horribly wrong Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-43378748041832337252009-04-06T23:21:00.000+02:002009-04-06T23:21:00.000+02:00I think there is a large inherent component of the...I think there is a large inherent component of the suitability of various languages for various tasks, well beyond simple familiarity issues.<BR/><BR/>In a nutshell, set based languages are going to be a lot easier for solving set based problems.<BR/><BR/>You get the pretzel logic when you try to use set based language for problems that are complicated to express in a set that the language Joel Garryhttps://www.blogger.com/profile/13325061229393838224noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-9153704040015825572009-03-24T17:32:00.000+01:002009-03-24T17:32:00.000+01:00> You can't place them at the top for easy ...> You can't place them at the top for easy reading/referencing.<BR/><BR/>That's the syntactical difference I meant.<BR/><BR/>> And it's cumbersome to give the inline view a readable name since you will be using that same name as an alias.<BR/><BR/>I hadn't though of that - although this can be easily mitigated by having appropriate comments. :-)<BR/><BR/>> And using roberthttps://www.blogger.com/profile/01724179181550310220noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-53049219721380444342009-03-24T17:23:00.000+01:002009-03-24T17:23:00.000+01:00Robert,That's true. Inline views show some similar...Robert,<BR/><BR/>That's true. Inline views show some similarities with the with-clause, but also differences:<BR/><BR/>You can't place them at the top for easy reading/referencing. And it's cumbersome to give the inline view a readable name since you will be using that same name as an alias.<BR/><BR/>And using subquery factoring, the cost based optimizer has the extra possibility to materialize Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-28133526674253846422009-03-24T16:54:00.000+01:002009-03-24T16:54:00.000+01:00Rob,I'd just like to add that most (all?) of the m...Rob,<BR/><BR/>I'd just like to add that most (all?) of the modularizations that you can do with WITH since 9i can be done in 8i as well: you just need to use an inline view for this. Actually, since Oracle's subquery factoring is not recursive (as MS SQL Server's Common Table Expressions) the difference between it and inline views are just syntactical and performance wise (different plans) - roberthttps://www.blogger.com/profile/01724179181550310220noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-56342933933166568782009-03-23T11:29:00.000+01:002009-03-23T11:29:00.000+01:00Rob,You will make your code a Rob-code. With lots ...Rob,<BR/><BR/>You will make your code a Rob-code. With lots of comments, your successor will maybe understand how it works, but will he be able to modify it ... What about code reusability? <BR/><BR/>It is great to use new features to enhance the performance. However I do not support integrating a new syntax because it exists unless it provides a ***significant*** advantageLaurent Schneiderhttps://www.blogger.com/profile/13833048234373264017noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-90928263160237606992009-03-23T10:53:00.000+01:002009-03-23T10:53:00.000+01:00Rob,I completely agree with you. Even I am of the ...Rob,<BR/><BR/>I completely agree with you. Even I am of the opinion that if I am writing the same code, say using 8i in the past, and same code, say 10g today, I am almost certainly not evolving. I am happy that my code (and thought process) today looks much better that the one in 2005. But I still find at least 10 "Oracle Database Developers" who will start (and end) with using PL/SQL to solve Narendrahttps://www.blogger.com/profile/14645699853364658640noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-51410198376072621672009-03-21T23:18:00.000+01:002009-03-21T23:18:00.000+01:00Hi Laurent,I guess we have to agree to disagree.If...Hi Laurent,<BR/><BR/>I guess we have to agree to disagree.<BR/><BR/>If some SQL construct is going to help me to achieve my desired result set in a performant and readable way, I'm going to use that and add sufficient comments for my successor.<BR/><BR/>And if that means using a model clause to do string aggregation, or a hierarchical query to split comma separated string or whatever, then that'sRob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-86422635276320482152009-03-21T18:00:00.000+01:002009-03-21T18:00:00.000+01:00> advanced XML-functions Oh yes! it will just d...> advanced XML-functions <BR/>Oh yes! it will just degrade your mind to use XML functions instead of PL/SQL. <BR/><BR/>I just had a session on XML this week and, when someone asked if it is performant, I showed that SELECT * FROM XMLTABLE('1 to 10') takes about 10 seconds to complete! <BR/><BR/> I love the challenge and congratulate you on your solutions. But please: use hierarchical Laurent Schneiderhttps://www.blogger.com/profile/13833048234373264017noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-88971474573231546792009-03-20T22:33:00.000+01:002009-03-20T22:33:00.000+01:00Thanks Erik, Laurent, Narendra and Mironm all very...Thanks Erik, Laurent, Narendra and Mironm all very much for sharing your opinion here.<BR/><BR/>Some reactions:<BR/><BR/>Laurent: you certainly cannot be accused of being less skilled in SQL, so it's interesting to see you'd use PL/SQL. And do you have the same opinion if it wasn't a model clause, but - say - a hierarchical query or a query containing advanced XML-functions and/or regular Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-36030550067177351402009-03-20T14:48:00.000+01:002009-03-20T14:48:00.000+01:00Good post. IMHOAll you need is solve your problem ...Good post. <BR/><BR/>IMHO<BR/>All you need is solve your problem (task) well. All another - method. The main thing - what do we mean saying "well"?Mironmhttps://www.blogger.com/profile/17277841950253348053noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-55573758235563542792009-03-20T12:02:00.000+01:002009-03-20T12:02:00.000+01:00Rob,I completely agree with all of the 3 reasons t...Rob,<BR/><BR/>I completely agree with all of the 3 reasons that you mentioned. And these are the exact reasons, especially the first, for my comment earlier. In our profession, we generally work in teams. Now I think it is almost always the case that not every team member has same skill-level, especially in SQL. When we write code, we should also consider whether the code will be "maintainable". Narendrahttps://www.blogger.com/profile/14645699853364658640noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-31954242905906983972009-03-20T09:50:00.000+01:002009-03-20T09:50:00.000+01:00well, maybe it was a bit hard words against model,...well, maybe it was a bit hard words against model, model is fine for the purpose it has been designed for, worksheet functionality...Laurent Schneiderhttps://www.blogger.com/profile/13833048234373264017noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-20162262892938057962009-03-20T09:25:00.000+01:002009-03-20T09:25:00.000+01:00Hey Rob,Even if I am a big fan of Model, I would *...Hey Rob,<BR/>Even if I am a big fan of Model, I would ***DEFINITELY*** use plsql or java to solve this issue. <BR/><BR/>Why? Because it will be way more performant! And also because, frankly, model is unmaintenable, almost unsupported (did you ever try to chase bugs with model with metalink), completly unknown.<BR/><BR/>I definitely second Steven Feuerstein : I do take hallucinogen before solvingLaurent Schneiderhttps://www.blogger.com/profile/13833048234373264017noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-34917650879271233112009-03-20T07:14:00.000+01:002009-03-20T07:14:00.000+01:00Rob,excellent post.The pl/sql variant is much more...Rob,<BR/>excellent post.<BR/>The pl/sql variant is much more comfortable to me. You are absolutely right in your first point:<BR/><BR/><I>"So when saying that straight SQL is harder to maintain than PL/SQL, I guess you are really saying that your PL/SQL skills are very good, but your SQL skills are, well, somewhat less than very good."</I><BR/><BR/>I am not comfortable with the model clause (yet)Erik Trip - Darwin IT Professionalshttps://www.blogger.com/profile/17829205033288938089noreply@blogger.com