[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 |
----------------------------------------------------------------------------------------------------------------------------------
--//說明提示可以這樣寫。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2682390/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20150709]慎用標量子查詢.txt
- 標量子查詢
- 標量子查詢（二）
- 標量子查詢(一)
- 用WITH…AS改寫標量子查詢
- [20140125]關於標量子查詢.txt
- [20150727]使用標量子查詢小問題.txt
- 都是標量子查詢惹的禍
- MYSQL學習筆記25: 多表查詢(子查詢)[標量子查詢,列子查詢]MySql筆記
- MYSQL count標量子查詢改left joinMySql
- 用LEFT JOIN優化標量子查詢優化
- [20211220]關於標量子查詢問題.txt
- [20211214]18c標量子查詢unnest.txt
- [20180612]函式與標量子查詢10.txt函式
- [20180626]函式與標量子查詢14.txt函式
- [20180602]函式與標量子查詢3.txt函式
- 20180601]函式與標量子查詢2.txt函式
- [20180611]函式與標量子查詢9.txt函式
- [20180607]函式與標量子查詢8.txt函式
- [20180602]函式與標量子查詢4.txt函式
- Oracle常用抓取SQL-標量子查詢等OracleSQL
- 帶彙總的標量子查詢改寫
- 標量子查詢優化（用group by 代替distinct）優化
- mysql 標量子查詢和現金盤程式製作非法子查詢MySql
- 影響Oracle標量子查詢效能的三個因素Oracle
- [20210202]計算標量子查詢快取數量2.txt快取
- [20210201]19c計算標量子查詢快取數量.txt快取
- 【TUNE_ORACLE】列出有標量子查詢的SQL參考OracleSQL
- 效能為王：SQL標量子查詢的優化案例分析SQL優化
- GreatSQL 最佳化技巧：將 MINUS 改寫為標量子查詢SQL
- [20180625]函式與標量子查詢13(補充)函式
- 遊標查詢
- 如何查詢上標
- Java 中如何使用 SQL 查詢 TXTJavaSQL
- [20160205]大量子游標引起的等待事件.txt事件
- [20220328]查詢游標為什麼不共享指令碼.txt指令碼
- [20220420]完善查詢游標為什麼不共享指令碼.txt指令碼
- [20190524]淺談模糊查詢.txt