{"id":1298,"date":"2016-01-07T11:21:34","date_gmt":"2016-01-07T03:21:34","guid":{"rendered":"http:\/\/www.821121.com\/?p=1298"},"modified":"2021-01-07T11:42:40","modified_gmt":"2021-01-07T03:42:40","slug":"%e5%ad%a6%e4%b9%a0%e7%ac%94%e8%ae%b0%e4%b9%8bsql1","status":"publish","type":"post","link":"http:\/\/www.821121.com\/?p=1298","title":{"rendered":"\u5b66\u4e60\u7b14\u8bb0\u4e4bSQL-Statement Transformation"},"content":{"rendered":"<h1>1 SQL Statement Transformation<\/h1>\n<p>The SQL Optimizer consists of \u201cQuery Transformer\u201d, \u201cCost Estimator\u201d and \u201cExplain Plan Generator\u201d. Most of the SQL statements will be transformed more or less by the optimizer before generating the execution plan for the purpose of getting the best performance.<br \/>\nHere are some examples of query transformation\u2026 <\/p>\n<pre><code class=\"language-sql\">(1) sales_qty &gt; 1200 \/ 12 \n(2) sales_qty &gt; 100 \n(3) sales_qty * 12 &gt; 1200<\/code><\/pre>\n<p>The predicate (1) will be transformed into (2), but the (3) will not. This is because SQL optimizer will not \u201cmove\u201d the condition across the \u201coperator\u201d (&gt;).<\/p>\n<p>Another example, the IN list will be transformed using \u201cOR\u201d operator. <\/p>\n<pre><code class=\"language-sql\">(1) job IN (&#039;MANAGER&#039;, &#039;CLERK&#039;) \n(2) job = &#039;CLERK&#039; OR job = &#039;MANAGER&#039;<\/code><\/pre>\n<p>\u201cBETWEEN\u201d will be transformed using \u201c&gt;=\u201d and \u201c&lt;=\u201d operators. <\/p>\n<pre><code class=\"language-sql\">(1) sales_qty BETWEEN 100 AND 200 \n(2) sales_qty &gt;= 100 AND sales_qty &lt;=200<\/code><\/pre>\n<p>\u201cANY\u201d operator will be transformed using \u201cOR\u201d operators in some cases\u2026 <\/p>\n<pre><code class=\"language-sql\">(1) sales_qty &gt; ANY ( 100, 200) \n(2) sales_qty &gt; 100 OR sales_qty &gt; 200<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>1 SQL Statement Transform<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18,48],"tags":[],"class_list":["post-1298","post","type-post","status-publish","format-standard","hentry","category-oracle-11g","category-sql"],"_links":{"self":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1298","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1298"}],"version-history":[{"count":3,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1298\/revisions"}],"predecessor-version":[{"id":1304,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1298\/revisions\/1304"}],"wp:attachment":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1298"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1298"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1298"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}