with as優化sql

myownstars發表於2011-11-29
優化SQL
with as是9.2推出的，在解析的時候被當成內聯檢視或者臨時表(臨時表可以被多次讀取)，使用/*+ materialize */可以讓其轉換成臨時表
國外有一篇文章介紹的挺細，轉載一下以備後用，而且有牛人通過with as優化sql http://blog.csdn.net/robinson1988/article/details/6953019
 

WITH Clause

The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference. You should assess the performance implications of the WITH clause on a case-by-case basis.

This article shows how the WITH clause can be used to reduce repetition and simplify complex SQL statements. I'm not suggesting the following queries are the best way to retrieve the required information. They merely demonstrate the use of the WITH clause.

Using the SCOTT schema, for each employee we want to know how many other people are in their department. Using an inline view we might do the following.

SELECT e.ename AS employee_name,

       dc.dept_count AS emp_dept_count

FROM   emp e,

       (SELECT deptno, COUNT(*) AS dept_count

        FROM   emp

        GROUP BY deptno) dc

WHERE  e.deptno = dc.deptno;

Using a WITH clause this would look like the following.

WITH dept_count AS (

  SELECT deptno, COUNT(*) AS dept_count

  FROM   emp

  GROUP BY deptno)

SELECT e.ename AS employee_name,

       dc.dept_count AS emp_dept_count

FROM   emp e,

       dept_count dc

WHERE  e.deptno = dc.deptno;

The difference seems rather insignificant here.

What if we also want to pull back each employees manager name and the number of people in the managers department? Using the inline view it now looks like this.

SELECT e.ename AS employee_name,

       dc1.dept_count AS emp_dept_count,

       m.ename AS manager_name,

       dc2.dept_count AS mgr_dept_count

FROM   emp e,

       (SELECT deptno, COUNT(*) AS dept_count

        FROM   emp

        GROUP BY deptno) dc1,

       emp m,

       (SELECT deptno, COUNT(*) AS dept_count

        FROM   emp

        GROUP BY deptno) dc2

WHERE  e.deptno = dc1.deptno

AND    e.mgr = m.empno

AND    m.deptno = dc2.deptno;

Using the WITH clause this would look like the following.

WITH dept_count AS (

  SELECT deptno, COUNT(*) AS dept_count

  FROM   emp

  GROUP BY deptno)

SELECT e.ename AS employee_name,

       dc1.dept_count AS emp_dept_count,

       m.ename AS manager_name,

       dc2.dept_count AS mgr_dept_count

FROM   emp e,

       dept_count dc1,

       emp m,

       dept_count dc2

WHERE  e.deptno = dc1.deptno

AND    e.mgr = m.empno

AND    m.deptno = dc2.deptno;

So we don't need to redefine the same subquery multiple times. Instead we just use the query name defined in the WITH clause, making the query much easier to read.

If the contents of the WITH clause is sufficiently complex, Oracle may decide to resolve the result of the subquery into a global temporary table. This can make multiple references to the subquery more efficient. The MATERIALIZE and INLINE optimizer hints can be used to influence the decision. The undocumented MATERIALIZE hint tells the optimizer to resolve the subquery as a global temporary table, while the INLINE hint tells it to process the query inline.

WITH dept_count AS (

  SELECT /*+ MATERIALIZE */ deptno, COUNT(*) AS dept_count

  FROM   emp

  GROUP BY deptno)

SELECT ...

 

WITH dept_count AS (

  SELECT /*+ INLINE */ deptno, COUNT(*) AS dept_count

  FROM   emp

  GROUP BY deptno)

SELECT ...

Even when there is no repetition of SQL, the WITH clause can simplify complex queries, like the following example that lists those departments with above average wages.

WITH

  dept_costs AS (

    SELECT dname, SUM(sal) dept_total

    FROM   emp e, dept d

    WHERE  e.deptno = d.deptno

    GROUP BY dname),

  avg_cost AS (

    SELECT SUM(dept_total)/COUNT(*) avg

    FROM   dept_costs)

SELECT *

FROM   dept_costs

WHERE  dept_total > (SELECT avg FROM avg_cost)

ORDER BY dname;

In the previous example, the main body of the query is very simple, with the complexity hidden in the WITH clause.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15480802/viewspace-712365/，如需轉載，請註明出處，否則將追究法律責任。

相關文章