[20200325]慎用标量子查询.txt

--//我在以前许多链接提到应该慎用标量子查询，不知道开发看别人的代码会传染，几乎到了乱用的地步，应该不要乱用。
--//比如一个表存在多个字段关联科室代码，许多开发喜欢这样标量子查询，这样sql语句看上去简洁许多，不然在from处
--//要写多次科室表。但是如果不分情况滥用，就不是很好，通过例子说明：

1.环境：
SCOTT@book> @ ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create view v_emp as select emp.*,(select dname from dept where dept.deptno=emp.deptno) dname from emp;
View created.

2.测试：
SCOTT@book> alter session set statistics_level = all;
Session altered.

SCOTT@book> select * from v_emp where dname='SALES';
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO DNAME
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- --------------
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30 SALES
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30 SALES
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30 SALES
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30 SALES
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30 SALES
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30 SALES
6 rows selected.

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  b6hyzqt25ra9c, child number 0
-------------------------------------
select * from v_emp where dname='SALES'
Plan hash value: 3142684405
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |       |     3 (100)|          |      6 |00:00:00.01 |      13 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      1 |    13 |     1   (0)| 00:00:01 |      3 |00:00:00.01 |       6 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |      3 |      1 |       |     0   (0)|          |      3 |00:00:00.01 |       3 |
|*  3 |  VIEW                       | V_EMP   |      1 |     14 |  1344 |     3   (0)| 00:00:01 |      6 |00:00:00.01 |      13 |
|   4 |   TABLE ACCESS FULL         | EMP     |      1 |     14 |   532 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       7 |
---------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$3 / DEPT@SEL$3
   2 - SEL$3 / DEPT@SEL$3
   3 - SEL$2 / V_EMP@SEL$1
   4 - SEL$2 / EMP@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPT"."DEPTNO"=:B1)
   3 - filter("DNAME"='SALES')

--//即使你在dept.dname上建立索引。
SCOTT@book> create unique index i_dept_dname on dept(dname);
Index created.

--//执行计划也不会改名。
Plan hash value: 3142684405
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |       |     3 (100)|          |      6 |00:00:00.01 |      13 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      1 |    13 |     1   (0)| 00:00:01 |      3 |00:00:00.01 |       6 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |      3 |      1 |       |     0   (0)|          |      3 |00:00:00.01 |       3 |
|*  3 |  VIEW                       | V_EMP   |      1 |     14 |  1344 |     3   (0)| 00:00:01 |      6 |00:00:00.01 |      13 |
|   4 |   TABLE ACCESS FULL         | EMP     |      1 |     14 |   532 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       7 |
---------------------------------------------------------------------------------------------------------------------------------

3.而如果写成这样
SCOTT@book> create view v_emp1 as select emp.*,dept.dname from emp ,dept where emp.deptno=dept.deptno;
View created.

SCOTT@book> select * from v_emp1 where dname='SALES';
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO DNAME
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- --------------
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30 SALES
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30 SALES
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30 SALES
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30 SALES
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30 SALES
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30 SALES

6 rows selected.

SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  8fz7svvgz65pu, child number 0
-------------------------------------
select * from v_emp1 where dname='SALES'
Plan hash value: 1614995081
---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |      1 |        |       |     4 (100)|          |      6 |00:00:00.01 |       9 |
|   1 |  NESTED LOOPS                |              |      1 |      5 |   255 |     4   (0)| 00:00:01 |      6 |00:00:00.01 |       9 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT         |      1 |      1 |    13 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|*  3 |    INDEX UNIQUE SCAN         | I_DEPT_DNAME |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|*  4 |   TABLE ACCESS FULL          | EMP          |      1 |      5 |   190 |     3   (0)| 00:00:01 |      6 |00:00:00.01 |       7 |
---------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$F5BB74E1
   2 - SEL$F5BB74E1 / DEPT@SEL$2
   3 - SEL$F5BB74E1 / DEPT@SEL$2
   4 - SEL$F5BB74E1 / EMP@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("DEPT"."DNAME"='SALES')
   4 - filter("EMP"."DEPTNO"="DEPT"."DEPTNO")
--//就能很好的使用索引，生产系统语句比上面还要复杂，我仅仅友情提醒开发不要乱用标量子查询。
--//我现在非常害怕开发写的复杂视图，开发应该好好的规划组织这些视图，写这些sql语句不要随心所遇，用点心，对自己对别人对自
--//己的事业都是一种交代，不然老了再看自己写的代码就是就是垃圾。

--//尤其注意where查询条件要利用这个变量子查询结果的情况。

4.顺便复习视图在优化时如何使用索引：
select /*+ index(v_emp.dept i_dept_dname) */ * from v_emp where dname='SALES';

Plan hash value: 3142684405
---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |       |     3 (100)|          |      6 |00:00:00.01 |      13 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      1 |    13 |     1   (0)| 00:00:01 |      3 |00:00:00.01 |       6 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |      3 |      1 |       |     0   (0)|          |      3 |00:00:00.01 |       3 |
|*  3 |  VIEW                       | V_EMP   |      1 |     14 |  1344 |     3   (0)| 00:00:01 |      6 |00:00:00.01 |      13 |
|   4 |   TABLE ACCESS FULL         | EMP     |      1 |     14 |   532 |     3   (0)| 00:00:01 |     14 |00:00:00.01 |       7 |
---------------------------------------------------------------------------------------------------------------------------------
--//提示无效。

select /*+ index(v_emp1.dept pk_dept) */ * from v_emp1 where dname='SALES';

Plan hash value: 4130191885
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |       |     5 (100)|          |      6 |00:00:00.01 |      11 |
|   1 |  NESTED LOOPS                |         |      1 |      5 |   255 |     5   (0)| 00:00:01 |      6 |00:00:00.01 |      11 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |      1 |    13 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       4 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |      1 |      4 |       |     1   (0)| 00:00:01 |      4 |00:00:00.01 |       2 |
|*  4 |   TABLE ACCESS FULL          | EMP     |      1 |      5 |   190 |     3   (0)| 00:00:01 |      6 |00:00:00.01 |       7 |
----------------------------------------------------------------------------------------------------------------------------------
--//说明提示可以这样写。