{"id":610,"date":"2014-04-03T05:30:00","date_gmt":"2014-04-03T05:30:00","guid":{"rendered":"http:\/\/bar-solutions.com\/weblog\/?p=610"},"modified":"2014-03-06T07:09:59","modified_gmt":"2014-03-06T06:09:59","slug":"","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=610","title":{"rendered":"PL\/SQL vs SQL"},"content":{"rendered":"<p align=\"left\"><img loading=\"lazy\" decoding=\"async\" title=\"plsql_logo\" style=\"border-top: 0px; border-right: 0px; background-image: none; border-bottom: 0px; float: right; padding-top: 0px; padding-left: 0px; margin: 10px; border-left: 0px; display: inline; padding-right: 0px\" border=\"0\" alt=\"plsql_logo\" src=\"\/weblog\/img\/plsql_logo.png\" width=\"150\" align=\"right\" height=\"150\" \/>There is a &#8216;rule&#8217;, I think it was created by <a title=\"Ask Tom\" href=\"http:\/\/asktom.oracle.com\" rel=\"nofollow\" target=\"_blank\">Tom Kyte<\/a>, stating: <a title=\"TECHNOLOGY: Ask Tom: On Cursors, SQL, and Analytics\" href=\"http:\/\/www.oracle.com\/technetwork\/issue-archive\/2007\/07-mar\/o27asktom-084983.html\" rel=\"nofollow\" target=\"_blank\">If you can do it in SQL, do it in SQL<\/a>. I came across some code the other day that makes perfect sense to do then you are running an <a title=\"Oracle\" href=\"http:\/\/www.oracle.com\/\" rel=\"nofollow\" target=\"_blank\">Oracle<\/a> 10g (or earlier) instance. I rewrote the code to use only the EMP and DEPT tables to protect the suspects and maybe innocent.<\/p>\n<div align=\"left\">\n<p><!--more--><\/p>\n<\/div>\n<p align=\"left\">The function defined is something like this:<\/p>\n<p align=\"left\">\n<style type=\"text\/css\">\n<p>.code, .code pre\n{\n  font-size: small;\n  color: black;\n  font-family: consolas, \"Courier New\", courier, monospace;\n  background-color: #ffffff;\n  \/*white-space: pre;*\/\n}\n.code pre { margin: 0em; }\n.code .rem { color: #ff0000; }\n.code .kwrd { color: #008080; }\n.code .str { color: #0000ff; }\n.code .op { color: #0000c0; }\n.code .preproc { color: #cc6633; }\n.code .asp { background-color: #ffff00; }\n.code .html { color: #800000; }\n.code .attr { color: #ff0000; }\n.code .alt\n{\n  background-color: #f4f4f4;\n  width: 100%;\n  margin: 0em;\n}\n.code .lnum { color: #606060; }<\/style>\n<\/p>\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">FUNCTION<\/span> getenames(deptno_in <span class=\"kwrd\">IN<\/span> emp.deptno%type) <span class=\"kwrd\">RETURN<\/span> <span class=\"kwrd\">VARCHAR<\/span>2\r\n<span class=\"kwrd\">IS<\/span>\r\n  <span class=\"kwrd\">TYPE<\/span> enames_aa <span class=\"kwrd\">IS<\/span> <span class=\"kwrd\">TABLE<\/span> <span class=\"kwrd\">OF<\/span> emp.ename%type <span class=\"kwrd\">INDEX<\/span> <span class=\"kwrd\">BY<\/span> pls_integer;\r\n  l_returnvalue <span class=\"kwrd\">VARCHAR2<\/span>(<span class=\"str\">32767<\/span>) := '<span class=\"str\"><\/span>';\r\n  l_enames enames_aa;\r\n<span class=\"kwrd\">BEGIN<\/span>\r\n  <span class=\"kwrd\">SELECT<\/span> e.ename\r\n    <span class=\"kwrd\">BULK<\/span> <span class=\"kwrd\">COLLECT<\/span> <span class=\"kwrd\">INTO<\/span> l_enames\r\n    <span class=\"kwrd\">FROM<\/span> emp e\r\n   <span class=\"kwrd\">WHERE<\/span> e.deptno = deptno_in\r\n   <span class=\"kwrd\">ORDER<\/span> <span class=\"kwrd\">BY<\/span> e.ename <span class=\"kwrd\">ASC<\/span> <span class=\"kwrd\">NULLS<\/span> <span class=\"kwrd\">FIRST<\/span>;\r\n  <span class=\"kwrd\">IF<\/span> l_enames.count &gt; 0 <span class=\"kwrd\">THEN<\/span>\r\n    <span class=\"kwrd\">FOR<\/span> indx <span class=\"kwrd\">IN<\/span> 1 .. l_enames.count <span class=\"kwrd\">LOOP<\/span>\r\n      l_returnvalue := l_returnvalue || l_enames(indx) || '<span class=\"str\">,<\/span>';\r\n    <span class=\"kwrd\">END<\/span> <span class=\"kwrd\">LOOP<\/span>;\r\n  <span class=\"kwrd\">END<\/span> <span class=\"kwrd\">IF<\/span>;\r\n  l_returnvalue := rtrim(l_returnvalue, '<span class=\"str\">,<\/span>');\r\n  <span class=\"kwrd\">RETURN<\/span> l_returnvalue;\r\n<span class=\"kwrd\">END<\/span>;<\/pre>\n<\/div>\n<p align=\"left\">and the query executed is something like this:<\/p>\n<p align=\"left\">\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">SELECT<\/span> d.dname, getenames(d.deptno) enames\r\n  <span class=\"kwrd\">FROM<\/span> dept d<\/pre>\n<\/div>\n<p align=\"left\">The result of this query is:<\/p>\n<div class=\"code\" align=\"left\">\n<pre>DNAME          ENAMES\r\n-------------- --------------------------------------------------------------------------------\r\nACCOUNTING     CLARK,KING,MILLER\r\nRESEARCH       ADAMS,FORD,JONES,SCOTT,SMITH\r\nSALES          ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD\r\nOPERATIONS     <\/pre>\n<\/div>\n<p align=\"left\">The Oracle database performs a lot of (not with these demo tables, though) Context Switches between the SQL Engine and the PL\/SQL Engine. And even worse, it performs Context Switches in the PL\/SQL code back to the SQL Engine. The PL\/SQL code has already been optimized using Bulk Processing, but still. The fastest way of doing something is by not doing it at all.<\/p>\n<p align=\"left\">The SQL engine in Oracle 11G has been enhanced with functionality that does exactly what is now being done by the PL\/SQL Function.<\/p>\n<p align=\"left\">The query has to be rewritten a bit (well, actually a lot) but the result is just SQL so no more Context Switching:<\/p>\n<p align=\"left\">\n<div class=\"code\" align=\"left\">\n<pre><span class=\"kwrd\">SELECT<\/span> d.dname, (<span class=\"kwrd\">SELECT<\/span> listagg(e.ename, ',') <span class=\"kwrd\">WITHIN<\/span> <span class=\"kwrd\">GROUP<\/span>(<span class=\"kwrd\">ORDER<\/span> <span class=\"kwrd\">BY<\/span> e.ename)\r\n                   <span class=\"kwrd\">FROM<\/span> emp e\r\n                  <span class=\"kwrd\">WHERE<\/span> 1=1\r\n                    <span class=\"kwrd\">AND<\/span> e.deptno = d.deptno\r\n                ) enames\r\n  <span class=\"kwrd\">FROM<\/span> dept d<\/pre>\n<\/div>\n<p align=\"left\">\n<p align=\"left\">If you run this statement you&#8217;ll get the exact same result as before, but with execution time. With these demo tables it is probably not so obvious that this approach is faster, but if you have real-world examples with real-world data then you will probably benefit from the time spent in the query.<\/p>\n<p align=\"left\">Bottom line is, the Oracle SQL Engine gets more powerful every release and it may be so powerful it can take care of PL\/SQL solutions you came up with in the past. And even though it is hard to say goodbye to working code, using SQL can dramatically speed up the execution of your code.<\/p>\n<p>This post has been cross-posted to <a href=\"http:\/\/technology.amis.nl\/2014\/04\/03\/plsql-vs-sql-2\/\">technology.amis.nl<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>There is a &#8216;rule&#8217;, I think it was created by Tom Kyte, stating: If you can do it in SQL, do it in SQL. I came across some code the other day that makes perfect sense to do then you are running an Oracle 10g (or earlier) instance. I rewrote the code to use only [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,5,9],"tags":[],"class_list":["post-610","post","type-post","status-publish","format-standard","hentry","category-oracle","category-plsql","category-sql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/610","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=610"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/610\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=610"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=610"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=610"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}