[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 |
----------------------------------------------------------------------------------------------------------------------------------
--//說明提示可以這樣寫。