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

[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 | ---------------------------------------------------------------------------------------------------------------------------------- --//说明提示可以这样写。