oracle+st+astext,总结笔记之sql调优

【sql调优之执行计划】in相关的operation

当where条件中有某字段的in条件，且in后的值为具体值而不是子查询，并且在该字段上有索引可以使用时，oracle优化器可能会使用in-lisy iterators操作，而不是concatrnation或者union all。而in可以用多个or来代替，所使用的执行计划相同，例子：

SQL> select /*+ index(a) */

2  a.deptno

3   from scott.emp a

4  where a.empno in (7339, 9000);

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 2355049923

---------------------------------------------------------------------------------------

| Id | Operation                   | Name  | Rows | Bytes | Cost (%CPU)| Time    |

---------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT            |       |    1 |    7 |    2  (0)| 00:00:01 |

|  1 | INLIST ITERATOR            |       |      |      |           |         |

|  2 |  TABLE ACCESS BY INDEX ROWID| EMP   |    1 |    7 |    2  (0)| 00:00:01 |

|* 3 |   INDEX UNIQUE SCAN        | PK_EMP |    1 |      |    1  (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("A"."EMPNO"=7339 OR "A"."EMPNO"=9000)

或者：

SQL> select /*+ index(a) */

2  a.deptno

3   from scott.emp a

4  where a.empno = 7339

5  or a.empno = 9000;

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 2355049923

---------------------------------------------------------------------------------------

| Id | Operation                   | Name  | Rows | Bytes | Cost (%CPU)| Time    |

---------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT            |       |    1 |    7 |    2  (0)| 00:00:01 |

|  1 | INLIST ITERATOR            |       |      |      |           |         |

|  2 |  TABLE ACCESS BY INDEX ROWID| EMP   |    1 |    7 |    2  (0)| 00:00:01 |

|* 3 |   INDEX UNIQUE SCAN        | PK_EMP |    1 |      |    1  (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("A"."EMPNO"=7339 OR "A"."EMPNO"=9000)

但是如果不是用empno上的索引，则有不同：

SQL> select /*+ full(a)*/

2  a.deptno

3   from scott.emp a

4  where a.empno in (7339, 9000);

no rows selected

Execution Plan

----------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time    |

--------------------------------------------------------------------------

|  0 | SELECT STATEMENT |     |    1 |    7 |    3  (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| EMP |    1 |    7 |    3  (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("A"."EMPNO"=7339 OR "A"."EMPNO"=9000)

在scott.emp.ename上创建索引，修改一下查询，使得where clause中的两个条件字段上都有索引可以使用，看看执行计划的改变：

SQL> create index scott.ind_emp_ename on scott.emp(ename);

Index created.

SQL> select

2  a.deptno

3   from scott.emp a

4  where a.empno = 7369

5  or a.ename = 'asd'

6 ;

DEPTNO

----------

20

Execution Plan

----------------------------------------------------------

Plan hash value: 2971452327

----------------------------------------------------------------------------------------------

| Id | Operation                   | Name         | Rows | Bytes | Cost (%CPU)| Time    |

----------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT            |              |    2 |   26 |    3  (0)| 00:00:01 |

|  1 | CONCATENATION              |              |      |      |           |         |

|  2 |  TABLE ACCESS BY INDEX ROWID| EMP          |    1 |   13 |    2  (0)| 00:00:01 |

|* 3 |   INDEX RANGE SCAN         | IND_EMP_ENAME |    1 |      |    1  (0)| 00:00:01 |

|* 4 |  TABLE ACCESS BY INDEX ROWID| EMP          |    1 |   13 |    1  (0)| 00:00:01 |

|* 5 |   INDEX UNIQUE SCAN        | PK_EMP       |    1 |      |    0  (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("A"."ENAME"='asd')

4 - filter(LNNVL("A"."ENAME"='asd'))

5 - access("A"."EMPNO"=7369)

这里使用的是CONCATENATION操作(串接)。

可以使用/*+NO_EXPAND */的hint来禁用串接操作，例如：

select/*+NO_EXPAND */

a.deptno

from scott.emp a

where a.empno = 7369

or a.ename = 'asd'

再来看看in子查询的执行计划

SQL> select /*+ */* from scott.emp a

2 where a.deptno in

3 (select deptno from scott.dept where rownum < 2 );

…

Execution Plan

----------------------------------------------------------

Plan hash value: 650699563

-------------------------------------------------------------------------------

| Id | Operation         | Name    | Rows | Bytes | Cost (%CPU)| Time    |

-------------------------------------------------------------------------------

|  0 | SELECT STATEMENT  |         |    4 |  204 |    4  (0)| 00:00:01 |

|  1 | NESTED LOOPS     |         |    4 |  204 |    4  (0)| 00:00:01 |

|  2 |  VIEW            | VW_NSO_1 |    1 |   13 |    1  (0)| 00:00:01 |

|* 3 |   COUNT STOPKEY  |         |      |      |           |         |

|  4 |    INDEX FULL SCAN| PK_DEPT |    4 |   12 |    1  (0)| 00:00:01 |

|* 5 |  TABLE ACCESS FULL| EMP     |    4 |  152 |    3  (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - filter(ROWNUM<2)

5 - filter("A"."DEPTNO" IS NOT NULL AND "A"."DEPTNO"="$nso_col_1")

SQL> select /*+ */* from scott.emp a

2 where a.deptno in

3 (select deptno from scott.dept where rownum < 3 );

…

Execution Plan

----------------------------------------------------------

Plan hash value: 1313905718

-------------------------------------------------------------------------------

| Id | Operation         | Name    | Rows | Bytes | Cost (%CPU)| Time    |

-------------------------------------------------------------------------------

|  0 | SELECT STATEMENT  |         |    9 |  459 |    5 (20)| 00:00:01 |

|* 1 | HASH JOIN SEMI   |         |    9 |  459 |    5 (20)| 00:00:01 |

|* 2 |  TABLE ACCESS FULL| EMP     |   13 |  494 |    3  (0)| 00:00:01 |

|  3 |  VIEW            | VW_NSO_1 |    2 |   26 |    1  (0)| 00:00:01 |

|* 4 |   COUNT STOPKEY  |         |      |      |           |         |

|  5 |    INDEX FULL SCAN| PK_DEPT |    4 |   12 |    1  (0)| 00:00:01 |

-------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("A"."DEPTNO"="$nso_col_1")

2 - filter("A"."DEPTNO" IS NOT NULL)

4 - filter(ROWNUM<3)

SQL> select /*+ */* from scott.emp a

2 where a.deptno in

3 (select deptno from scott.dept);

….

Execution Plan

----------------------------------------------------------

Plan hash value: 3074306753

------------------------------------------------------------------------------

| Id | Operation         | Name   | Rows | Bytes | Cost (%CPU)| Time    |

------------------------------------------------------------------------------

|  0 | SELECT STATEMENT  |        |   13 |  533 |    3  (0)| 00:00:01 |

|  1 | NESTED LOOPS     |        |   13 |  533 |    3  (0)| 00:00:01 |

|* 2 |  TABLE ACCESS FULL| EMP    |   13 |  494 |    3  (0)| 00:00:01 |

|* 3 |  INDEX UNIQUE SCAN| PK_DEPT |    1 |    3 |    0  (0)| 00:00:01 |

------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("A"."DEPTNO" IS NOT NULL)

3 - access("A"."DEPTNO"="DEPTNO")

可以看出，cbo下in的执行计划与in内的数据量，字段上的索引，是否空值等方面都有关系，从谓词信息可以看出，不同的操作，访问路径和应用条件的顺序有所不同。

再看一个常见的子查询问题：

SQL> select /*+ */* from scott.emp a

2 wherea.empnoin

3 (selectempnofrom scott.dept where rownum < 2 );

EMPNO ENAME     JOB             MGR HIREDATE        SAL      COMM    DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH     CLERK          7902 17-DEC-80    812.61                   20

7499 ALLEN     SALESMAN       7698 20-FEB-81    1609.6       300        30

7521 WARD      SALESMAN       7698 22-FEB-81    1259.6       500        30

7566 JONES     MANAGER        7839 02-APR-81    2009.6                   20

7654 MARTIN    SALESMAN       7698 28-SEP-81    1259.6      1400        30

7698 BLAKE     MANAGER        7839 01-MAY-81    2859.6                   30

7782 CLARK     MANAGER        7839 09-JUN-81    2459.6                   10

7788 SCOTT     ANALYST        7566 19-APR-87    3009.6                   20

7839 KING      PRESIDENT           17-NOV-81    5009.6                   10

7844 TURNER    SALESMAN       7698 08-SEP-81    1509.6         0        30

7876 ADAMS     CLERK          7788 23-MAY-87    1109.6                   20

EMPNO ENAME     JOB             MGR HIREDATE        SAL      COMM    DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7900 JAMES     CLERK          7698 03-DEC-81     959.6                   30

7902 FORD      ANALYST        7566 03-DEC-81    3009.6                   20

7934 update    CLERK          7782 23-JAN-82    1309.6

14 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 3458227086

------------------------------------------------------------------------------

| Id | Operation         | Name   | Rows | Bytes | Cost (%CPU)| Time    |

------------------------------------------------------------------------------

|  0 | SELECT STATEMENT  |        |    1 |   38 |   10  (0)| 00:00:01 |

|* 1 | FILTER           |        |      |      |           |         |

|  2 |  TABLE ACCESS FULL| EMP    |   14 |  532 |    3  (0)| 00:00:01 |

|* 3 |  FILTER          |        |      |      |           |         |

|* 4 |   COUNT STOPKEY  |        |      |      |           |         |

|  5 |    INDEX FULL SCAN| PK_DEPT |    1 |      |    1  (0)| 00:00:01 |

------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter( EXISTS (???)

3 - filter(:B1=:B2)

4 - filter(ROWNUM<2)

子查询中的表并不存在empno字段，然而这是可以运行的，从谓词信息中可以看到有一个exists的operation，这并不正确，但确实影响到了外层的查询，如果修改成rownum<1，那么这个查询将不返回行，需要注意。