[20150709]慎用标量子查询.txt
--花了几天的时间调试sql语句，最终发现是标量子查询在作怪，原始的语句太复杂，我拿scott做一个例子来说明问题。
1.建立测试环境：
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create view v_deptemp1 as select emp.*,dname,loc from emp,dept where dept.deptno=emp.deptno ;
create view v_deptemp2 as select emp.*,(select dname from dept where dept.deptno=emp.deptno) dname ,(select loc from dept where dept.deptno=emp.deptno )loc from emp ;
--第2种写法就是标量子查询。
2.测试1：
create index i_dept_dname on scott.dept (dname);
create index i_emp_deptno on scott.emp (deptno)
SCOTT@test> select * from v_deptemp1 where dname='ACCOUNTING' ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME LOC
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- -------------- -------------
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 ACCOUNTING NEW YORK
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 09tbznuqjnxa4, child number 0
-------------------------------------
select * from v_deptemp1 where dname='ACCOUNTING'
Plan hash value: 2863776355
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 5 | 295 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I_DEPT_DNAME | 1 | | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | I_EMP_DEPTNO | 5 | | 0 (0)| |
| 6 | TABLE ACCESS BY INDEX ROWID | EMP | 5 | 195 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
3 - SEL$F5BB74E1 / DEPT@SEL$2
4 - SEL$F5BB74E1 / DEPT@SEL$2
5 - SEL$F5BB74E1 / EMP@SEL$2
6 - SEL$F5BB74E1 / EMP@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DNAME"='ACCOUNTING')
5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
--oracle 很好的分析确定索引，并且对emp表也可以使用i_emp_deptno。
--看看第2种情况：
SCOTT@test> select * from v_deptemp2 where dname='ACCOUNTING' ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME LOC
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- -------------- -------------
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 ACCOUNTING NEW YORK
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 45acjyf0f8x54, child number 0
-------------------------------------
select * from v_deptemp2 where dname='ACCOUNTING'
Plan hash value: 2698003519
-------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
|* 5 | VIEW | V_DEPTEMP2 | 14 | 1456 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 14 | 546 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$3 / DEPT@SEL$3
2 - SEL$3 / DEPT@SEL$3
3 - SEL$4 / DEPT@SEL$4
4 - SEL$4 / DEPT@SEL$4
5 - SEL$2 / V_DEPTEMP2@SEL$1
6 - SEL$2 / EMP@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPT"."DEPTNO"=:B1)
4 - access("DEPT"."DEPTNO"=:B1)
5 - filter("DNAME"='ACCOUNTING')
--这样无论如何都不会使用emp的i_emp_deptno。当然我实际上的情况实际上开发想避免dname 为null的情况。
--因为标量子查询如果查询dname为null不是他所需要的。
3.测试2：
--再建立一个表tx
create table tx ( id number,name varchar2(20));
insert into tx values (7654,'MARTIN');
commit;
SCOTT@test> select * from v_deptemp1 v ,tx where tx.id=v.empno and dname is not null;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME LOC ID NAME
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- -------------- ------------- ---------- ------------
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 SALES CHICAGO 7654 MARTIN
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID gskw0uw49rw0k, child number 0
-------------------------------------
select * from v_deptemp1 v ,tx where tx.id=v.empno and dname is not null
Plan hash value: 2230440165
------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 70 | 5 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 50 | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TX | 1 | 11 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 39 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_EMP_EMPNO | 1 | | 0 (0)| |
|* 7 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
|* 8 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
4 - SEL$F5BB74E1 / TX@SEL$1
5 - SEL$F5BB74E1 / EMP@SEL$2
6 - SEL$F5BB74E1 / EMP@SEL$2
7 - SEL$F5BB74E1 / DEPT@SEL$2
8 - SEL$F5BB74E1 / DEPT@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("TX"."ID"="EMP"."EMPNO")
7 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
8 - filter("DNAME" IS NOT NULL)
--而如果换成v_deptemp2
SCOTT@test> select * from v_deptemp2 v ,tx where tx.id=v.empno and dname is not null;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME LOC ID NAME
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- -------------- ------------- ---------- --------
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 SALES CHICAGO 7654 MARTIN
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7b8950at9688x, child number 0
-------------------------------------
select * from v_deptemp2 v ,tx where tx.id=v.empno and dname is not null
Plan hash value: 2887512270
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| | | | |
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 13 | 1 (0)| 00:00:01 | | | |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 1 (0)| 00:00:01 | | | |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| | | | |
|* 5 | HASH JOIN | | 1 | 106 | 7 (15)| 00:00:01 | 1156K| 1156K| 383K (0)|
| 6 | TABLE ACCESS FULL | TX | 1 | 11 | 3 (0)| 00:00:01 | | | |
|* 7 | VIEW | V_DEPTEMP2 | 14 | 1330 | 3 (0)| 00:00:01 | | | |
| 8 | TABLE ACCESS FULL | EMP | 14 | 546 | 3 (0)| 00:00:01 | | | |
----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$3 / DEPT@SEL$3
2 - SEL$3 / DEPT@SEL$3
3 - SEL$4 / DEPT@SEL$4
4 - SEL$4 / DEPT@SEL$4
5 - SEL$1
6 - SEL$1 / TX@SEL$1
7 - SEL$2 / V@SEL$1
8 - SEL$2 / EMP@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPT"."DEPTNO"=:B1)
4 - access("DEPT"."DEPTNO"=:B1)
5 - access("TX"."ID"="V"."EMPNO")
7 - filter("DNAME" IS NOT NULL)
--emp始终选择的是全表扫描。
4.继续测试：
--当把dname is not null删除后，结果呢？
SCOTT@test> select * from v_deptemp2 v ,tx where tx.id=v.empno ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME LOC ID NAME
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- -------------- ------------- ---------- ---------
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 SALES CHICAGO 7654 MARTIN
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 1sd5umnwjt823, child number 0
-------------------------------------
select * from v_deptemp2 v ,tx where tx.id=v.empno
Plan hash value: 3698375752
----------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
| 3 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 11 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
| 5 | NESTED LOOPS | | | | | |
| 6 | NESTED LOOPS | | 1 | 50 | 4 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | TX | 1 | 11 | 3 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | PK_EMP_EMPNO | 1 | | 0 (0)| |
| 9 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 39 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$3 / DEPT@SEL$3
2 - SEL$3 / DEPT@SEL$3
3 - SEL$4 / DEPT@SEL$4
4 - SEL$4 / DEPT@SEL$4
5 - SEL$F5BB74E1
7 - SEL$F5BB74E1 / TX@SEL$1
8 - SEL$F5BB74E1 / EMP@SEL$2
9 - SEL$F5BB74E1 / EMP@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPT"."DEPTNO"=:B1)
4 - access("DEPT"."DEPTNO"=:B1)
8 - access("TX"."ID"="EMP"."EMPNO")
--可以发现没有这个条件(dname is not null),emp可以很好的选择索引。
--实际我们的视图定义有多处使用标量子查询，当然我只要把在where出现的改写成不使用标量子查询。
--如果不愿意修改还有1个简单的改写方法，就是：
SCOTT@test> select * from (select * from v_deptemp2 v ,tx where tx.id=v.empno ) where dname is not null ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DNAME LOC ID NAME
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- -------------- ------------- ---------- --------
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 SALES CHICAGO 7654 MARTIN
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8yy4n9t1dqhmm, child number 0
-------------------------------------
select * from (select * from v_deptemp2 v ,tx where tx.id=v.empno )
where dname is not null
Plan hash value: 3563298867
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 13 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
| 3 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 11 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
|* 5 | VIEW | | 1 | 129 | 4 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | | | | |
| 7 | NESTED LOOPS | | 1 | 50 | 4 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | TX | 1 | 11 | 3 (0)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | PK_EMP_EMPNO | 1 | | 0 (0)| |
| 10 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 39 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$4 / DEPT@SEL$4
2 - SEL$4 / DEPT@SEL$4
3 - SEL$5 / DEPT@SEL$5
4 - SEL$5 / DEPT@SEL$5
5 - SEL$335DD26A / from$_subquery$_001@SEL$1
6 - SEL$335DD26A
8 - SEL$335DD26A / TX@SEL$2
9 - SEL$335DD26A / EMP@SEL$3
10 - SEL$335DD26A / EMP@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPT"."DEPTNO"=:B1)
4 - access("DEPT"."DEPTNO"=:B1)
5 - filter("DNAME" IS NOT NULL)
9 - access("TX"."ID"="EMP"."EMPNO")
--这样改写后emp可以使用索引!看来oracle cbo 还是不够成熟，无法识别这种情况。
5.也许有人问v_deptemp1 与 v_deptemp2是不等价的。继续测试：
SCOTT@test> create or replace view v_deptemp3 as select emp.*,dname,loc from emp,dept where dept.deptno(+)=emp.deptno ;
View created.
alter table emp modify constraint fk_deptno disable novalidate;
--取消主外键约束。
SCOTT@test> update emp set deptno=90 where empno=7654;
1 row updated.
SCOTT@test> commit ;
Commit complete.
SCOTT@test> select * from v_deptemp3 v ,tx where tx.id=v.empno and dname is not null;
no rows selected
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID bwytb3gt4vtx1, child number 0
-------------------------------------
select * from v_deptemp3 v ,tx where tx.id=v.empno and dname is not null
Plan hash value: 2230440165
------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 70 | 5 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 50 | 4 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TX | 1 | 11 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 39 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_EMP_EMPNO | 1 | | 0 (0)| |
|* 7 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
|* 8 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 20 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$EB0D93E2
4 - SEL$EB0D93E2 / TX@SEL$1
5 - SEL$EB0D93E2 / EMP@SEL$2
6 - SEL$EB0D93E2 / EMP@SEL$2
7 - SEL$EB0D93E2 / DEPT@SEL$2
8 - SEL$EB0D93E2 / DEPT@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("TX"."ID"="EMP"."EMPNO")
7 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
8 - filter("DNAME" IS NOT NULL)
--就是这样定义，oracle 一样选择合理的执行方式。
总结：
1.不要大量不顾各种情况的使用标量子查询，实际上标量子查询存在许多缺陷，仅仅能查询1个字段，仅仅返回1行。
2.并且导致内部嵌套连接，而不能采用hash join等连接方式。假如emp表1万行，这样查询dept表要有1万次(我上面执行2次标量子查询，也就是2万次)。
即使dept有索引，逻辑读的数量不可小看.
3.另外一个原因我觉得可能是数据结构问题，这种情况如果太遵守范式，数据结构设计耦合太紧，开发很容易选择标量子查询的写法。
例如：1个表有6个字段有科室代码字段，这样要显示科室名称，这样from中写6次科室表，做连接6次查询，这样许多开发更愿意选择标量子查询写sql语句。
4.总之要开发合理选择这种写法,不能到处滥用。
