{"id":1301,"date":"2016-01-07T11:34:48","date_gmt":"2016-01-07T03:34:48","guid":{"rendered":"http:\/\/www.821121.com\/?p=1301"},"modified":"2021-01-07T11:42:33","modified_gmt":"2021-01-07T03:42:33","slug":"%e5%ad%a6%e4%b9%a0%e7%ac%94%e8%ae%b0%e4%b9%8bsql-merging","status":"publish","type":"post","link":"http:\/\/www.821121.com\/?p=1301","title":{"rendered":"\u5b66\u4e60\u7b14\u8bb0\u4e4bSQL-Merging"},"content":{"rendered":"

View Merging<\/h1>\n

In order to generate the best execution plan for the view (inline view), the optimizer may need to transform the SQL query. There are two ways for the transformation:<\/p>\n

    \n
  1. View Merging: merge the view query and the query condition (predicates)<\/li>\n
  2. Predicate Pushing: If the view merge cannot be performed, push the predicates into the view query<\/li>\n<\/ol>\n

    Please note the \u201cdirection\u201d of the two methods above is different. The former is to \u201crewrite\u201d the outer query using the view query (inner query), while the latter on is to push the query condition of the outer query into the view query.
    \nIf the outer query includes the following operations, then the \u201cView Merging\u201d will not be applicable\u2026<\/p>\n

      \n
    1. SET operations, like UNION, UNION ALL, INTERSECT, MINUS, etc<\/li>\n
    2. CONNECT BY<\/li>\n
    3. ROWNUM<\/li>\n
    4. Aggregation function in SELECT-list, like SUM, AVG, MAX, MIN, etc<\/li>\n
    5. GROUP BY ( can use hint MERGE to instruct the optimizer to choose view merging)<\/li>\n
    6. DISTINCT in SELECT-list ( can use hint MERGE to instruct the optimizer to choose view merging)<\/li>\n<\/ol>\n

      If the outer query has many query conditions that can reduce the query range and merge the query condition into the view can reduce the data volume that need to be processed, the view merge is preferable, otherwise the view merging is not necessary.
      \nFor example,<\/p>\n

      CREATE VIEW emp_10(e_no, e_name, job, manager, hire_date, salary) AS SELECT empno, ename, job, mgr, hiredate, sal, comm FROM emp WHERE deptno = 10;\nSELECT e_no, e_name, salary, hire_date FROM emp_10 WHERE salary > 10000000;<\/code><\/pre>\n
      Can be transformed using view merging as follows, <\/p>\n
      SELECT empno, ename, sal, hiredate FROM emp WHERE deptno=10 AND sal > 10000000;<\/code><\/pre>\n
      Another example, <\/p>\n
      CREATE VIEW emp_group_by_deptno AS SELECT deptno, AVG(sal) avg_sal, min(sal) min_sal, max(sal) max_sal FROM emp GROUP BY deptno; SELECT * FROM emp_group_by_deptno WHERE deptno=10;\nCan be transformed as follows\u2026 SELECT deptno, AVG(sal) avg_sal, min(sal) min_sal, max(sal) max_sal FROM emp WHERE deptno=10 GROUP BY deptno;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
      View Merging In order to <\/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-1301","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\/1301","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=1301"}],"version-history":[{"count":2,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1301\/revisions"}],"predecessor-version":[{"id":1303,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1301\/revisions\/1303"}],"wp:attachment":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1301"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1301"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1301"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}