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

 

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

我想通过一些例子来说明情况：
@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")

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

时间： 2024-07-29 05:46:23

[20140125]关于标量子查询.txt的相关文章

[20150727]使用标量子查询小问题.txt

[20150727]使用标量子查询小问题.txt --最近一段时间一直在做优化,仔细看我前面的blog,不主张使用标量子查询,实际上还是有一些小细节要注意. 1.测试环境: SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- ----------------------------------------------

SQL为王：oracle标量子查询和表连接改写

小鱼(邓秋爽) 云和恩墨专家,有超过5年超大型数据库专业服务经验,擅长oracle 数据库优化.SQL优化和troubleshooting 编辑手记:如何提高数据的查询效率是每个人都关注的问题,今天让我们来学习如何合理使用标量子查询和表连接方式来提高查询速度吧~ 之前小鱼就听过了标量子查询,不过对于其中的细节理解还是远远不够,借助一部分资料和自己测试对标量子查询做一点简单的分析和介绍. Oracle允许在select子句中包含单行子查询,这个也就是oracle的标量子查询,标量子查询有点类似于外

性能为王：SQL标量子查询的优化案例分析

黄廷忠(网名:认真就输) 云和恩墨技术专家 个人博客:http://www.htz.pw/ 本篇整理内容是黄廷忠在"云和恩墨大讲堂"微信分享中的讲解案例,SQL优化及SQL审核,是从源头解决性能问题的根本手段,无论是开发人员还是DBA,都应当持续深入的学习SQL开发技能,从而为解决性能问题打下根基. 本篇为系列案例之一:标量子查询优化   以下案例来自于某省电信系统EDW性能优化实践,数据库版本为11.2.0.3,运行在ORACLE Exadata一体机上,是个典型的OLAP环境,表上

在MySQL中使用子查询和标量子查询的基本操作教程_Mysql

MySQL 子查询子查询是将一个 SELECT 语句的查询结果作为中间结果,供另一个 SQL 语句调用.MySQL 支持 SQL 标准要求的所有子查询格式和操作,也扩展了特有的几种特性. 子查询没有固定的语法,一个子查询的例子如下: SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=1) 对应的两个数据表如下: article 文章表: user 用户表: 查询返回结果如下所示: 在该例子中,首先通过子查询语

都是标量子查询惹的祸

都是标量子查询惹的祸 系统又报了一个跑的慢的sql语句,看图就知道这个很恐怖的,已经跑了1天了,还需要跑6个月的时间,   把sql语句拿出来瞅瞅: --201406 XX select SUM(UNPOSTING_AMT_CYC) 分期未结清余额, SUM(greatest(nvl(BAL_TOTAL,0),0)) 余额,COUNT(1),DELQ_LEVEL_CI,city,FQ_TOTAL, CASE WHEN greatest(nvl(BAL_TOTAL,0),0) 500 THEN '

SQL Server高级内容之子查询和表链接概述及使用_MsSql

1．子查询概念 (1)就是在查询的where子句中的判断依据是另一个查询的结果,如此就构成了一个外部的查询和一个内部的查询,这个内部的查询就是自查询. (2)自查询的分类 1)独立子查询 ->独立单值(标量)子查询(=) 复制代码 代码如下: Select testID,stuID,testBase,testBeyond,testPro from Score where stuID=( select stuID from Student where stuName='Kencery' ) ->

详细讲述MySQL中的子查询操作_Mysql

继续做以下的前期准备工作:     新建一个测试数据库TestDB:     create database TestDB;     创建测试表table1和table2: CREATE TABLE table1 ( customer_id VARCHAR(10) NOT NULL, city VARCHAR(10) NOT NULL, PRIMARY KEY(customer_id) )ENGINE=INNODB DEFAULT CHARSET=UTF8; CREATE TABLE table

【MySQL】子查询之一

MySQL 从版本4 开始支持 SQL 标准要求的所有子查询格式和操作,同时扩展了特有的几种特性.本文会介绍子查询的类型以及相关的注意点. 一 什么是子查询     子查询是将一个 SELECT 语句的查询结果作为中间结果,供另一个 SQL 语句调用.如: SELECT * FROM t1  WHERE vid in  (SELECT  vid FROM t2); 二 MySQL 子查询分类       根据子查询的返回数据形式,mysql 子查询可以分为以下几类: a  标量子查询      

SQL Server高级内容之子查询和表链接概述及使用

1．子查询概念 (1)就是在查询的where子句中的判断依据是另一个查询的结果,如此就构成了一个外部的查询和一个内部的查询,这个内部的查询就是自查询. (2)自查询的分类 1)独立子查询 ->独立单值(标量)子查询(=) 复制代码 代码如下: Select testID,stuID,testBase,testBeyond,testPro from Score where stuID=( select stuID from Student where stuName='Kencery' ) ->