{"id":11657,"date":"2019-10-28T14:01:55","date_gmt":"2019-10-28T14:01:55","guid":{"rendered":"https:\/\/ittutorial.org\/?p=11657"},"modified":"2020-11-13T12:20:23","modified_gmt":"2020-11-13T12:20:23","slug":"sql-tuning-tips-and-tricks-tutorial-oracle-3","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/sql-tuning-tips-and-tricks-tutorial-oracle-3\/","title":{"rendered":"SQL Tuning Tips and Tricks Tutorial in Oracle -3"},"content":{"rendered":"<p>I will continue to explain\u00a0Oracle SQL Tuning Tips and Tricks in this article.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-11609 size-large\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/SQL-Tuning-tutorial-1024x460.jpg\" alt=\"\" width=\"618\" height=\"278\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/SQL-Tuning-tutorial-1024x460.jpg 1024w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/SQL-Tuning-tutorial-300x135.jpg 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/SQL-Tuning-tutorial-768x345.jpg 768w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/SQL-Tuning-tutorial.jpg 1227w\" sizes=\"auto, (max-width: 618px) 100vw, 618px\" \/><\/p>\n<p><!--more--><\/p>\n<p>&nbsp;<\/p>\n<h2><span style=\"color: #ff0000;\">Oracle SQL Tuning Tips and Tricks<\/span><\/h2>\n<p>Read second article before this.<\/p>\n<blockquote class=\"wp-embedded-content\" data-secret=\"9RqEoRkCod\"><p><a href=\"https:\/\/ittutorial.org\/sql-tuning-tips-and-tricks-tutorial-oracle-2\/\">SQL Tuning Tips and Tricks Tutorial in Oracle -2<\/a><\/p><\/blockquote>\n<p><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;SQL Tuning Tips and Tricks Tutorial in Oracle -2&#8221; &#8212; IT Tutorial\" src=\"https:\/\/ittutorial.org\/sql-tuning-tips-and-tricks-tutorial-oracle-2\/embed\/#?secret=ISC3dCzXlT#?secret=9RqEoRkCod\" data-secret=\"9RqEoRkCod\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h3><span style=\"color: #ff0000;\">SQL Tuning Tips and Tricks<\/span><\/h3>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>21- Use Partitioning for Large Tables<\/strong><\/span><\/p>\n<p>If Slow query contains large tables, then you can use table partitioning to make faster query.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-9488\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/05\/Partitioning-Oracle.jpg\" alt=\"\" width=\"638\" height=\"359\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/05\/Partitioning-Oracle.jpg 638w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/05\/Partitioning-Oracle-300x169.jpg 300w\" sizes=\"auto, (max-width: 638px) 100vw, 638px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>You can read the following article to learn Partitioning detailed<\/p>\n<blockquote class=\"wp-embedded-content\" data-secret=\"vYLa2IEFRH\"><p><a href=\"https:\/\/ittutorial.org\/partitioning-types-range-list-hash-interval-in-oracle-database\/\">Partitioning Types ( Range , List, Hash, Interval .. ) in Oracle Database<\/a><\/p><\/blockquote>\n<p><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;Partitioning Types ( Range , List, Hash, Interval .. ) in Oracle Database&#8221; &#8212; IT Tutorial\" src=\"https:\/\/ittutorial.org\/partitioning-types-range-list-hash-interval-in-oracle-database\/embed\/#?secret=Dbv0vFIDf3#?secret=vYLa2IEFRH\" data-secret=\"vYLa2IEFRH\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>22-\u00a0Use CASE statements instead of DECODE<\/strong><\/span><\/p>\n<p>You can use CASE statements instead of DECODE for the\u00a0readability and performance of the query<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>23<\/strong><\/span><span style=\"color: #ff0000;\"><strong>&#8211; Use IN instead of OR<\/strong><\/span><\/p>\n<p>You should use IN instead of OR in the where clause. IN is used for\u00a0selective predicate is in the sub query,and\u00a0EXISTS\u00a0is used for\u00a0selective predicate is in the\u00a0parent query.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>24<\/strong><\/span><span style=\"color: #ff0000;\"><strong>&#8211;\u00a0Avoid Complex expressions in SQL<\/strong><\/span><\/p>\n<p>You should avoid using complex expression in SQL to prevent the\u00a0optimizer from assigning valid cardinality or selectivity estimates.<\/p>\n<p>&nbsp;<\/p>\n<pre>--Do not use:\r\nWHERE SUBSTR(a.id, INSTR(b.id, ',') - 1) = SUBSTR(b.id, INSTR(b.id, ',') - 1)\r\n\r\n--Use this instead of above:\r\nWHERE a.id = b.id\r\n\r\n\r\n\r\n--Avoid:\r\nWHERE id = NVL(:a1, id)\r\n\r\nWHERE NVL(id,-1) = ( 1, etc...)\r\n\r\nNVL (col1,-999) = \u2026.\r\n\r\n\r\nTO_DATE(), TO_NUMBER(), and so on\r\n\r\n\r\n\r\n\r\n<\/pre>\n<p><span style=\"color: #ff0000;\"><strong>25<\/strong><\/span><span style=\"color: #ff0000;\"><strong>&#8211;\u00a0Prefer INNER JOIN instead of OUTER JOIN<\/strong><\/span><\/p>\n<p>Inner join is recommended instead of Outer Join if possible. Because Outer join limits the database optimization options<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-11659\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/inner-join-vs-outer-join.jpg\" alt=\"\" width=\"1385\" height=\"780\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/inner-join-vs-outer-join.jpg 1385w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/inner-join-vs-outer-join-300x169.jpg 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/inner-join-vs-outer-join-768x433.jpg 768w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/inner-join-vs-outer-join-1024x577.jpg 1024w\" sizes=\"auto, (max-width: 1385px) 100vw, 1385px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p class=\"p\"><span style=\"color: #ff0000;\"><strong>26<\/strong><\/span><span style=\"color: #ff0000;\"><strong>&#8211; Avoid ORDER BY and GROUP BY If possible<\/strong><\/span><\/p>\n<p>it is recommended to avoid <strong>ORDER BY, GROUP BY<\/strong>\u00a0clause , if\u00a0using <strong>ORDER BY, GROUP BY<\/strong> clause is not mandatory for you. Because\u00a0<strong>ORDER BY, GROUP BY<\/strong>\u00a0causes sorting and result\u00a0in one of the most expensive operations in SQL execution.<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-11660\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/Group-By-vs-Order-By.jpg\" alt=\"\" width=\"365\" height=\"124\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/Group-By-vs-Order-By.jpg 365w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/Group-By-vs-Order-By-300x102.jpg 300w\" sizes=\"auto, (max-width: 365px) 100vw, 365px\" \/><\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>27<\/strong><\/span><span style=\"color: #ff0000;\"><strong>&#8211; Avoid Hint usage<\/strong><\/span><\/p>\n<p>Hint usage is not recommended instead\u00a0optimizing your code.<\/p>\n<p>Hint usage is the last thing you should consider for Performance tuning or it is a temporary solution.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-11661\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/oracle-hints-parallel.jpg\" alt=\"\" width=\"480\" height=\"360\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/oracle-hints-parallel.jpg 480w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/oracle-hints-parallel-300x225.jpg 300w\" sizes=\"auto, (max-width: 480px) 100vw, 480px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>28<\/strong><\/span><span style=\"color: #ff0000;\"><strong>&#8211; Drop Unused Index<\/strong><\/span><\/p>\n<p>You should drop unused index on tables, monitor all indexes and check them if they are unnecessary and unused, you should drop them.<\/p>\n<p>Most common mistake is using Index, Index is not good everytime. You should monitor it, If it is good for performance, you can keep it, if not you should drop it.<\/p>\n<p>Read the following article to learn Index detailed.<\/p>\n<p>&nbsp;<\/p>\n<blockquote class=\"wp-embedded-content\" data-secret=\"HQ6amig3SQ\"><p><a href=\"https:\/\/ittutorial.org\/what-is-index-and-why-should-we-use-index\/\">What is Index and Why Should We Use Index?<\/a><\/p><\/blockquote>\n<p><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;What is Index and Why Should We Use Index?&#8221; &#8212; IT Tutorial\" src=\"https:\/\/ittutorial.org\/what-is-index-and-why-should-we-use-index\/embed\/#?secret=qEji6hlXsF#?secret=HQ6amig3SQ\" data-secret=\"HQ6amig3SQ\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>29<\/strong><\/span><span style=\"color: #ff0000;\"><strong>&#8211;\u00a0Avoid using wildcard (%)<\/strong><\/span><\/p>\n<p>Using wildcard (%)\u00a0at the beginning of a predicate is\u00a0a known performance problem in all databases.<\/p>\n<p>Wildcard (%) usage<strong>\u00a0LIKE &#8216;%abc&#8217;<\/strong> causes full table scan, so avoid\u00a0Using wildcard (%).<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>30<\/strong><\/span><span style=\"color: #ff0000;\"><strong>&#8211;\u00a0 Use Hash Join for Large tables Join<\/strong><\/span><\/p>\n<p>You should examine execution plan carefully, If\u00a02 large tables are joined, If they don&#8217;t use Hash Join, use Hash Join hint. If a large table is joined with a small table,\u00a0\u00a0If they don&#8217;t use Nested loop Join, use Nested loop Join hint.<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-11662\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/nested-loop-join.jpg\" alt=\"\" width=\"1089\" height=\"491\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/nested-loop-join.jpg 1089w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/nested-loop-join-300x135.jpg 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/nested-loop-join-768x346.jpg 768w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/nested-loop-join-1024x462.jpg 1024w\" sizes=\"auto, (max-width: 1089px) 100vw, 1089px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h5><a href=\"https:\/\/ittutorial.org\/performance-tuning-and-sql-tuning-tutorial-in-the-oracle-database\/\" target=\"_blank\" rel=\"noopener noreferrer\"><span style=\"color: #ff0000;\">Do you want to learn Oracle Database Performance Tuning detailed, then Click this link.<\/span><\/a><\/h5>\n","protected":false},"excerpt":{"rendered":"<p>I will continue to explain\u00a0Oracle SQL Tuning Tips and Tricks in this article.<\/p>\n","protected":false},"author":1,"featured_media":11609,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1994,5085,6603],"tags":[6564,6580,6570,6572,6568,6567,6576,6566,6560,6562,6585,6549,6571,6561,6546,6593,6545,6563,6574,6875,6573,6569,10533,10535,6577,6579,6565,6550,6547,6534,6555,6554,6541,6533,6536,6535,6538,6532,6537,6594,6551,6590,6591,6876,10536,6588,6589,10534,6548,6557,6553,6539,6543,6542,6556,6586,6544,6583,6592,6874,6601,6602,6595,6584,6596,6587,6599,6582,6600,6581,6598,4530,6597,6575,6578,6540],"class_list":["post-11657","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle","category-performance-tuning","category-sql-tuning","tag-alter-system-set-cursor_sharing-12c","tag-bind-variable-in-java","tag-bind-variables-in-oracle-dynamic-sql","tag-bind-variables-in-oracle-forms","tag-bind-variables-in-oracle-pl-sql-examples","tag-cursor-in-oracle","tag-cursor-sharing-and-bind-variable-usage","tag-cursor-sharing-force-oracle","tag-cursor_sharing-explained","tag-cursor_sharing-performance-tuning","tag-database-optimization-techniques-sql-server","tag-how-to-create-index-in-oracle-to-improve-performance","tag-how-to-declare-bind-variable-in-oracle-sql-developer","tag-how-to-enable-adaptive-cursor-sharing-12c","tag-how-to-improve-database-performance-in-oracle-11g","tag-how-to-make-select-query-faster-in-oracle","tag-how-to-optimize-sql-query-performance-in-oracle","tag-how-to-reduce-hard-parsing-in-oracle-12c","tag-how-to-remove-bind-variable-in-oracle","tag-how-to-tune-the-query-in-oracle-using-explain-plan","tag-how-to-use-bind-variables-in-oracle-reports","tag-how-to-use-bind-variables-in-oracle-stored-procedure","tag-online-oracle-sql-query-performance-tuning","tag-oracle-12c-performance-tuning-tips","tag-oracle-bind-variables-view","tag-oracle-cursor-bind-variable-example","tag-oracle-cursor_sharing","tag-oracle-database-optimization-techniques","tag-oracle-improve-query-performance","tag-oracle-performance-tuning-12c","tag-oracle-performance-tuning-18c","tag-oracle-performance-tuning-19c","tag-oracle-performance-tuning-basics","tag-oracle-performance-tuning-blogs","tag-oracle-performance-tuning-interview-questions","tag-oracle-performance-tuning-pdf","tag-oracle-performance-tuning-scenarios","tag-oracle-performance-tuning-tips","tag-oracle-performance-tuning-tutorial","tag-oracle-pl-sql-performance-tuning-tips-techniques-pdf","tag-oracle-query-large-table-performance","tag-oracle-sql-performance-tuning","tag-oracle-sql-tricks","tag-oracle-sql-tuning-book","tag-oracle-tips","tag-performance-tuning-in-database","tag-performance-tuning-in-oracle","tag-performance-tuning-in-oracle-11g","tag-performance-tuning-in-oracle-12c-with-examples","tag-performance-tuning-in-oracle-19c","tag-performance-tuning-in-oracle-19c-with-examples","tag-performance-tuning-in-oracle-for-beginners","tag-performance-tuning-in-oracle-for-developer","tag-performance-tuning-in-oracle-interview-questions","tag-performance-tuning-in-oracle-tutorial","tag-performance-tuning-in-sql-server-2012-step-by-step","tag-pl-sql-performance-tuning","tag-query-optimization-in-sql-server-with-example","tag-query-optimization-techniques-in-oracle","tag-query-tuning-in-oracle-interview-questions","tag-sql-code-performance","tag-sql-performance-query","tag-sql-performance-tuning","tag-sql-performance-tuning-tutorial","tag-sql-query-optimization-techniques","tag-sql-query-optimization-tool","tag-sql-query-performance-tuning","tag-sql-query-performance-tuning-tips","tag-sql-select-performance","tag-sql-server-query-optimization-tips","tag-sql-server-query-optimizer","tag-sql-tuning-advisor","tag-sql-view-performance-optimization","tag-substitution-variable-in-pl-sql-example","tag-what-is-bind-variable-in-oracle-with-examples","tag-what-is-performance-tuning-in-oracle-with-examples"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/SQL-Tuning-tutorial.jpg","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/11657","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=11657"}],"version-history":[{"count":8,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/11657\/revisions"}],"predecessor-version":[{"id":32292,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/11657\/revisions\/32292"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/11609"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=11657"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=11657"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=11657"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}