ITPub博客

首页 > 数据库 > Oracle > [20140125]关于标量子查询.txt

[20140125]关于标量子查询.txt

原创 Oracle 作者:lfree 时间:2014-01-26 09:27:26 0 删除 编辑

 

最近一致在优化一个垃圾项目,我发现一个奇怪的想象,就是开发很喜欢使用标量子查询,我发现这个东西像传染病一样,一个人使用其
他人也跟着仿效,而不考虑具体的使用场合。还有一些出现在视图里面。

我想通过一些例子来说明情况:
@ver
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

--标量子查询例子:

SCOTT@test01p> select emp.*,(select dname from dept where dept.deptno=emp.deptno) ename from emp;
     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO ENAME
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- --------------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20 RESEARCH
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30 SALES
...

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  du01z0rvgas7m, child number 0
-------------------------------------
select emp.*,(select dname from dept where dept.deptno=emp.deptno)
ename from emp

Plan hash value: 2981343222

---------------------------------------------------------------------
| Id  | Operation                   | Name    | E-Rows | Cost (%CPU)|
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |        |     6 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |     1   (0)|
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |      1 |     0   (0)|
|   3 |  TABLE ACCESS FULL          | EMP     |     14 |     3   (0)|
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPT"."DEPTNO"=:B1)

--可以发现要存取dept表通过PK_DEPT来取,如果emp很大,效率不高,逻辑读会很大。
--这样写最大的好处是如果连接的表很多的情况下,看上去更加简单一些。
--而且可以发现一些缺点,就是子查询仅仅查询一个字段,不能包括多个字段。

SCOTT@test01p> select emp.*,(select dname,loc from dept where dept.deptno=emp.deptno) from emp;
select emp.*,(select dname,loc from dept where dept.deptno=emp.no) from emp
              *
ERROR at line 1:
ORA-00913: too many values

--如果写成这样效率更低。
SCOTT@test01p> set autot traceonly ;

select emp.*,(select dname from dept where dept.deptno=emp.deptno) dname,
(select loc from dept where dept.deptno=emp.deptno)  loc
from emp;

Execution Plan
---------------------------
Plan hash value: 3707356765
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |    14 |   546 |     9   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   3 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00:00:01 |
|*  4 |   INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
|   5 |  TABLE ACCESS FULL          | EMP     |    14 |   546 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPT"."DEPTNO"=:B1)
   4 - access("DEPT"."DEPTNO"=:B1)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
       1933  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

--逻辑读达到了18个。

--而如果写成如下:
SCOTT@test01p> select emp.*,dept.dname,dept.loc from emp,dept where emp.deptno=dept.deptno;
14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   826 |     5   (0)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   826 |     5   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   546 |     3   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   546 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       1809  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed

--仅仅11个逻辑读。

--补充一些信息在11g下的测试
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production


SCOTT@test> alter session set statistics_level=all;
Session altered.

select emp.*,(select dname from dept where dept.deptno=emp.deptno) dname,
(select loc from dept where dept.deptno=emp.deptno)  loc
from emp;

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  dmgh9v95g9010, child number 0
-------------------------------------
select emp.*,(select dname from dept where dept.deptno=emp.deptno)
dname, (select loc from dept where dept.deptno=emp.deptno)  loc from emp
Plan hash value: 3707356765
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |     3 (100)|     14 |00:00:00.01 |       7 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      1 |     2   (0)|      3 |00:00:00.01 |       5 |
|*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |      3 |      1 |     1   (0)|      3 |00:00:00.01 |       2 |
|   3 |  TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      1 |     2   (0)|      3 |00:00:00.01 |       5 |
|*  4 |   INDEX UNIQUE SCAN         | PK_DEPT |      3 |      1 |     1   (0)|      3 |00:00:00.01 |       2 |
|   5 |  TABLE ACCESS FULL          | EMP     |      1 |     14 |     3   (0)|     14 |00:00:00.01 |       7 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPT"."DEPTNO"=:B1)
   4 - access("DEPT"."DEPTNO"=:B1)

--可以发现1个小小的问题,11G下,标量子查询的cost不计算.导致最后的costs很低.而且最后逻辑度的数量也计算错误,应该是7+5+5=17.

select emp.*,dept.dname,dept.loc from emp,dept where emp.deptno=dept.deptno;

SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  51ag9ppruqa9u, child number 0
-------------------------------------
select emp.*,dept.dname,dept.loc from emp,dept where
emp.deptno=dept.deptno
Plan hash value: 615168685
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     8 (100)|     14 |00:00:00.01 |      15 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |     14 |     8  (13)|     14 |00:00:00.01 |      15 |  1023K|  1023K|  746K (0)|
|   2 |   TABLE ACCESS FULL| DEPT |      1 |      6 |     4   (0)|      5 |00:00:00.01 |       8 |       |       |          |
|   3 |   TABLE ACCESS FULL| EMP  |      1 |     14 |     3   (0)|     14 |00:00:00.01 |       7 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

总结:
使用标量子查询一定要注意场合,仅仅在返回行数很少的时候才有效.而不是到处乱用.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1075477/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6290253