{"id":83,"date":"2011-08-05T20:21:52","date_gmt":"2011-08-05T19:21:52","guid":{"rendered":"http:\/\/www.psinke.nl\/wordpress\/?p=83"},"modified":"2013-11-20T11:39:56","modified_gmt":"2013-11-20T10:39:56","slug":"optimizing-oracle-sql-performance-using-an-inline-view","status":"publish","type":"post","link":"https:\/\/www.psinke.nl\/blog\/optimizing-oracle-sql-performance-using-an-inline-view\/","title":{"rendered":"Optimizing Oracle SQL performance using an inline view!"},"content":{"rendered":"<div class=\"afa2b3b851443650502c43e85c746c1f\" data-index=\"1\" style=\"float: none; margin:0px;\">\n<script type=\"text\/javascript\"><!--\r\ngoogle_ad_client = \"ca-pub-4223201538046844\";\r\n\/* standaardblok *\/\r\ngoogle_ad_slot = \"9522527129\";\r\ngoogle_ad_width = 320;\r\ngoogle_ad_height = 50;\r\n\/\/-->\r\n<\/script>\r\n<script type=\"text\/javascript\"\r\nsrc=\"http:\/\/pagead2.googlesyndication.com\/pagead\/show_ads.js\">\r\n<\/script>\n<\/div>\n<p>In our production environment, we have a process that runs for about 30 minutes, and may even take hours on busy days. Way too long of course!<br \/>\nI&#8217;ve been rewriting this function this week, and finally brought it back to a matter of minutes by using a materialized view and a more efficient query.<br \/>\nStill, I think nowadays most data should be available instantanious, if possible. So, a few minutes is way too long.<\/p>\n<p>I have 2 tables, tabel A with 2.4 million records, materialized view B with 82000 records. The materialized view is a joined subset of 3 tables containing about 240.000 records each.<\/p>\n<p>Here&#8217;s the culprit:<\/p>\n<pre>SELECT last_name,\r\n   date_of_birth ,\r\n    bsn \r\n  FROM mat_view\r\n    JOIN table_a \r\n  ON mat_view.fk_id = table_a.fk_id\r\nWHERE mat_view.fk2_id = :BIND<\/pre>\n<h2>optimizing Oracle SQL performance using an inline view<\/h2>\n<p>Not too bad I thought. Relatively small view, Oracle knows how to handle this! The result was lots of full table scans and hash joins, and of course a high cost. After giving it some thought, I remembered the thing called inline views. Maybe they would bring Oracle to some other thoughts about the execution path.<\/p>\n<p>As you see in the query below, I&#8217;ve rewritten the join with the materialized view + bind variable to a join with an inline view. I now force Oracle to first fetch a tiny subset of the materialized view. This subset is joined on foreign keys to the large table, which performs like a breeze.<\/p>\n<pre>SELECT last_name,\r\n date_of_birth ,\r\n bsn\r\n FROM\r\n ( SELECT * FROM mat_view WHERE fk2_id =:BIND\r\n ) gpl\r\n JOIN table_a\r\n ON gpl.fk_id = table_a.fk_id<\/pre>\n<p>Fortunately, this did the trick. Believe it or not, this query gives instantanious result, instead of in minutes. The drawback is that I needed to add some extra, quite complex clauses, so the responsetime is now about 2-3 seconds. In the end, not bad compared to the 30 minutes we started with!<\/p>\n<p>You can read more about Oracle inline views <a title=\"Inline views and subqueries\" href=\"http:\/\/docs.oracle.com\/cd\/B19306_01\/server.102\/b14200\/queries007.htm\" target=\"_blank\">here<\/a>. That page also describes the subtle difference between subqueries and inline views. Also check Tom Kyte&#8217;s thoughts on\u00a0<a title=\"Tom Kyte on inline views and with clause.\" href=\"http:\/\/asktom.oracle.com\/pls\/asktom\/f?p=100:11:0::::P11_QUESTION_ID:4423923392083\" target=\"_blank\">the With clause<\/a>, which is similar but even more powerful.<\/p>\n<!--CusAds0-->\n<div style=\"font-size: 0px; height: 0px; line-height: 0px; margin: 0; padding: 0; clear: both;\"><\/div>","protected":false},"excerpt":{"rendered":"<p>In our production environment, we have a process that runs for about 30 minutes, and may even take hours on busy days. Way too long of course! I&#8217;ve been rewriting this function this week, and finally brought it back to a matter of minutes by using a materialized view and a more efficient query. Still,&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[308,87,86,307,6,85,89,7,88],"class_list":["post-83","post","type-post","status-publish","format-standard","hentry","category-oracle-blog","tag-execution-path","tag-inline-view","tag-materialized-view","tag-optimizer","tag-oracle","tag-performance","tag-report","tag-sql","tag-tuning"],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/posts\/83","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/comments?post=83"}],"version-history":[{"count":0,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/posts\/83\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/media?parent=83"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/categories?post=83"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/tags?post=83"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}