关于标量子查询的问题

标量子查询

 

引用: 标量子查询

一个标量子查询是一个放在圆括弧里的普通 SELECT查询， 它只返回只有一个字段的一行．（参阅 Chapter 4 获取有关写查询的信息。） 该 SELECT 将被执行， 而其单个返回值将在周围的值表达式中使用． 把一个返回超过一行或者超过一列的查询用做标量查询是错误的． (不过，在特定的执行中，子查询不返回行则不算错误；标量结果认为是 NULL．)该子查询可以引用周围查询的变量， 那些变量也是在计算任意子查询的时候当做常量使用的． 又见 Section 6.15．

比如，下面的查询找出每个州中的最大人口数量的城市∶

SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name)
FROM states;

子查询作为标量操作数--MySql数据库
ps: 这种方法的只能查询单一值

《Oracle 高效设计》 读书思考--标量子查询查询性能讨论
能提供不少思路

select      n.ex_name,     c.customer_name,     n.remark,          e.employee_name  as  create_name,     n.create_user,         n.project_id,   n.id  as  node_id,         n.model_id,     n. file_id ,             g.grade_name  as  level_name,     t.type  as  type_name,     
         exe.exe_ids,  exe.exe_names,    dept.dept_ids,         dept.dept_names,      
     Convert (DATE_FORMAT(pn.node_end_time,  ' %Y年%c月%e日 ' ) USING utf8)  as  end_time     
from      hrp_project_execution n 
left   join  hrp_cycle_grade g  on  g.id  =  n.worklevel     
left   join  hrp_work_type t  on  t.id  =  n.worktype     
left   join  hrp_customer c  on  c.id  =  n.customer_id     
left   join  hrp_company_employee_info e  on  e.id  =  n.create_user     
left   join  hrp_project_node pn  on  pn.id  =  n.node_id
left   join  ( select  ne.execution_id,  Convert (GROUP_CONCAT(ne.executor_id) USING utf8)  as  exe_ids,      GROUP_CONCAT(e.employee_name)  as  exe_names  
        from   hrp_project_execution_executor ne     
        inner   join  hrp_company_employee_info e  on  e.id  =  ne.executor_id     
        where  ne.delete_flag  =   0         and     ne.execution_id  =   46    group   by  ne.execution_id 
       ) exe  on  exe.execution_id  =  n.id
left   join  ( select  de.exectuion_id,  Convert (GROUP_CONCAT(de.deptor_id) USING utf8)  as  dept_ids,          GROUP_CONCAT(e.employee_name)  as  dept_names  
        from   hrp_project_execution_deptor de     
        inner   join  hrp_company_employee_info e  on  e.id  =  de.deptor_id     
        where  de.delete_flag  =   0         and     de.exectuion_id  =   46    group   by  de.exectuion_id
       ) dept  on  dept.exectuion_id  =  n.id
where     n.id  =   46

 

 

 

呵呵，发现TOM的高效设计都出错了，贴出来发给大家看看，怕我自己搞错了，所以事先请教NEWKID兄，
经过大侠NEWKID兄的确认和指点后，我才坚信TOM大师也犯错了

 

转载：http://www.itpub.net/thread-1338364-1-1.html
背景案例：

开发人员完成一SQL如下：
SELECT TF.FLOW_ID 流程ID,
       TF.FLOW_NAME 流程名称,
       TFM.FLOW_NAME 流程模板名称,
       STAFF1.STAFF_NAME  流程创建人名称,
       STAFF2.STAFF_NAME   流程状态员工,
       TF.CREATE_DATE 创建日期,
       TF.STATE 流程状态名称,
       TF.STATE_DATE 流程状态日期,
       TT.TCH_ID 环节ID,
       TT.TCH_NAME 环节名称,
       TT.TCH_MOD 环节类型名称,
       TT.PERSON 接收任务员工,
       TT.TCH_CONT_DESC 环节内容,
       TT.RESULT_ID 环节执行结果,
       TT.LATE_REASON 影响本环节原因,
       STAFF3.STAFF_NAME   环节执行人,
       TT.SLA_DATE 考核时间点,
       TT.SLA_TIME 考核时间,
       TT.CREATED_DATE 环节创建日期,
       TT.BEGIN_DATE 环节启动日期,
       TT.FINISH_DATE 环节结束日期,
       TT.STATE 环节状态名称,
       NVL2(TT.SUB_FLOW_ID,
            (SELECT TF.FLOW_NAME
               FROM FLOW TF
              WHERE TF.FLOW_ID = TT.SUB_FLOW_ID
                AND TF.FLOW_ID = TT.FLOW_ID),
            0) 子流程名称
  FROM FLOW TF,TACHE TT,FLOW_MODEL TFM,STAFF STAFF1 ,STAFF STAFF2,STAFF STAFF3
WHERE TF.FLOW_ID = TT.FLOW_ID
   AND TF.BEGIN_TCH_ID = TT.TCH_ID
   AND TF.FLOW_MOD = TFM.FLOW_MOD
   AND TF.STAFF_ID=STAFF1.STAFF_ID
   AND TF.STATE_STAFF=STAFF2.STAFF_ID
   AND TT.STAFF_ID=STAFF3.STAFF_ID

后来，开发人员将上述语句修改为标量子查询，具体如下。

SELECT TF.FLOW_ID 流程ID,
       TF.FLOW_NAME 流程名称,
       TFM.FLOW_NAME 流程模板名称,
       (SELECT STAFF_NAME FROM STAFF TS WHERE TS.STAFF_ID = TF.STAFF_ID) 流程创建人名称,
       (SELECT STAFF_NAME FROM STAFF TS WHERE TS.STAFF_ID = TF.STATE_STAFF) 流程状态员工,
       TF.CREATE_DATE 创建日期,
       TF.STATE 流程状态名称,
       TF.STATE_DATE 流程状态日期,
       TT.TCH_ID 环节ID,
       TT.TCH_NAME 环节名称,
       TT.TCH_MOD 环节类型名称,
       TT.PERSON 接收任务员工,
       TT.TCH_CONT_DESC 环节内容,
       TT.RESULT_ID 环节执行结果,
       TT.LATE_REASON 影响本环节原因,
       (SELECT STAFF_NAME FROM STAFF TS WHERE TS.STAFF_ID = TT.STAFF_ID) 环节执行人,
       TT.SLA_DATE 考核时间点,
       TT.SLA_TIME 考核时间,
       TT.CREATED_DATE 环节创建日期,
       TT.BEGIN_DATE 环节启动日期,
       TT.FINISH_DATE 环节结束日期,
       TT.STATE 环节状态名称,
       NVL2(TT.SUB_FLOW_ID,
            (SELECT TF.FLOW_NAME
               FROM FLOW TF
              WHERE TF.FLOW_ID = TT.SUB_FLOW_ID
                AND TF.FLOW_ID = TT.FLOW_ID),
            0) 子流程名称
  FROM FLOW TF,TACHE TT,FLOW_MODEL TFM
WHERE TF.FLOW_ID = TT.FLOW_ID
   AND TF.BEGIN_TCH_ID = TT.TCH_ID
   AND TF.FLOW_MOD = TFM.FLOW_MOD


问题来了，这两种写法等价吗？
其实是不等价的！
如果TF.STAFF_ID可能为空，必须该用外连接才等价，另一个TT.STAF_ID 也是如此!
------------------------------------------------------------------------------------------------------------------------

我们可以试验如下：

DROP TABLE TEST111;
CREATE TABLE TEST111 (ID INT );
INSERT  INTO TEST111 VALUES (1);
INSERT  INTO TEST111 VALUES (2);
INSERT  INTO TEST111 VALUES (3);
COMMIT;
DROP TABLE TEST222;
CREATE TABLE TEST222 (ID INT );
INSERT  INTO TEST222 VALUES (1);
INSERT  INTO TEST222 VALUES (2);
INSERT  INTO TEST222 VALUES (NULL);
COMMIT;


如下两种写法等价
SELECT T1.ID ,(SELECT T2.ID FROM TEST222 T2 WHERE ID=T1.ID)
FROM TEST111 T1;


                                     ID (SELECTT2.IDFROMTEST222T2WHERE
--------------------------------------- ------------------------------
                                      1                              1
                                      2                              2
                                      3
                                      
                                      
SELECT T1.ID ,T2.ID FROM TEST111 T1 ,TEST222 T2 WHERE T1.ID=T2.ID(+)

SQL> SELECT T1.ID ,T2.ID FROM TEST111 T1 ,TEST222 T2 WHERE T1.ID=T2.ID(+);

                                     ID                                      ID
--------------------------------------- ---------------------------------------
                                      1                                       1
                                      2                                       2
                                      3

如果不写成外连接，将不等价

SQL> SELECT T1.ID ,T2.ID FROM TEST111 T1 ,TEST222 T2 WHERE T1.ID=T2.ID;

                                     ID                                      ID
--------------------------------------- ---------------------------------------
                                      1                                       1
                                      2                                       2
        
        
        
        
-----------------------------------------------------------------------------------------------------------------------               
转入正题，查阅TOM的高效设计，学习TOM的标量子查询的这一章节。发现世界级大师不小心也犯错了，我发现TOM的高效设计中涉及到标量子查询的地方有两处BUG  

                                    
第1处错误：                                      
第8章，390页处如下脚本，写法有误

如下脚本在执行过程会报ORA-01427: 单行子查询返回多个行的错误

select object_type,
       object_name,
       decode(status, 'INVALID', '*', '') status,
       decode(object_type,
              'TABLE',
              (select tablespace_name
                 from user_tables
                where table_name = object_name),
              'TABLE PARTITION',
              (select tablespace_name
                 from user_tab_partitions
                where partition_name = subobject_name),
              'INDEX',
              (select tablespace_name
                 from user_indexes
                where index_name = object_name),
              'INDEX PARTITION',
              (select tablespace_name
                 from user_ind_partitions
                where partition_name = subobject_name),
              'LOB',
              (select tablespace_name
                 from user_segments
                where segment_name = object_name),
              null) tablespace_name
  from user_objects a
order by object_type, object_name

其中查找脚本出错的方法可将SQL改造为如下后发现原因
SELECT *
  FROM (select object_type,
               object_name,
               subobject_name,
               decode(status, 'INVALID', '*', '') status,
               decode(object_type,
                      'TABLE',
                      (select COUNT(*)
                         from user_tables
                        where table_name = object_name),
                      'TABLE PARTITION',
                      (select COUNT(*)
                         from user_tab_partitions
                        where  partition_name = subobject_name),
                      'INDEX',
                      (select COUNT(*)
                         from user_indexes
                        where index_name = object_name),
                      'INDEX PARTITION',
                      (select COUNT(*)
                         from user_ind_partitions
                        where partition_name = subobject_name),
                      'LOB',
                      (select COUNT(*)
                         from user_segments
                        where segment_name = object_name),
                      null) CNT
          from user_objects a
         order by object_type, object_name)
WHERE CNT > 1

如下查询结果可知user_tab_partitions和user_ind_partitions部分关联user_objects后返回了多条记录！

OBJECT_TYPE         OBJECT_NAME                          SUBOBJECT_NAME     STATUS        CNT
------------------- ------------------------------------ ------------------ ------ ----------
INDEX PARTITION     BIN$CMOKJKViStmNxTg7xMcerw==$0       P3                                 4
INDEX PARTITION     BIN$CMOKJKViStmNxTg7xMcerw==$0       P1                                 4
INDEX PARTITION     BIN$CMOKJKViStmNxTg7xMcerw==$0       P2                                 4
INDEX PARTITION     BIN$VaNYTNQDTwCUGLznTySTTQ==$0       P2                                 4
INDEX PARTITION     BIN$VaNYTNQDTwCUGLznTySTTQ==$0       P1                                 4
INDEX PARTITION     BIN$VaNYTNQDTwCUGLznTySTTQ==$0       P3                                 4
INDEX PARTITION     BIN$omlhPKACRIuWVFt3q9Yl/A==$0       P2                                 4
INDEX PARTITION     BIN$omlhPKACRIuWVFt3q9Yl/A==$0       P3                                 4
INDEX PARTITION     BIN$omlhPKACRIuWVFt3q9Yl/A==$0       P1                                 4
INDEX PARTITION     IDX_TAB1                             P2                                 4
INDEX PARTITION     IDX_TAB1                             P1                                 4
INDEX PARTITION     IDX_TAB1                             P3                                 4
TABLE PARTITION     BIN$12P24K0NSsyE26Qw1UmD0g==$0       P3                                12
TABLE PARTITION     BIN$12P24K0NSsyE26Qw1UmD0g==$0       P1                                12
TABLE PARTITION     BIN$12P24K0NSsyE26Qw1UmD0g==$0       P2                                12
TABLE PARTITION     BIN$6FIF7ZkBRF6GU120ostiWw==$0       P1                                12
TABLE PARTITION     BIN$6FIF7ZkBRF6GU120ostiWw==$0       P3                                12
TABLE PARTITION     BIN$6FIF7ZkBRF6GU120ostiWw==$0       P2                                12
TABLE PARTITION     BIN$DQPZK/0eQbaYEt0BZ1s/Mg==$0       P3                                12
TABLE PARTITION     BIN$DQPZK/0eQbaYEt0BZ1s/Mg==$0       P2                                12
TABLE PARTITION     BIN$DQPZK/0eQbaYEt0BZ1s/Mg==$0       P1                                12
TABLE PARTITION     BIN$O9jKeQ63Qx6GagrHewYrVQ==$0       P2                                12
TABLE PARTITION     BIN$O9jKeQ63Qx6GagrHewYrVQ==$0       P3                                12
TABLE PARTITION     BIN$O9jKeQ63Qx6GagrHewYrVQ==$0       P1                                12
TABLE PARTITION     BIN$QNX8vccsSM+fpTBZJYN42g==$0       P3                                12
TABLE PARTITION     BIN$QNX8vccsSM+fpTBZJYN42g==$0       P2                                12
TABLE PARTITION     BIN$QNX8vccsSM+fpTBZJYN42g==$0       P1                                12
TABLE PARTITION     BIN$lxLXtRKkSgW4kmMo60MyRA==$0       P1                                12
TABLE PARTITION     BIN$lxLXtRKkSgW4kmMo60MyRA==$0       P2                                12
TABLE PARTITION     BIN$lxLXtRKkSgW4kmMo60MyRA==$0       P3                                12
TABLE PARTITION     BIN$o2PkS10LQ5Gl+yYu2lSkGg==$0       P1                                12
TABLE PARTITION     BIN$o2PkS10LQ5Gl+yYu2lSkGg==$0       P3                                12
TABLE PARTITION     BIN$o2PkS10LQ5Gl+yYu2lSkGg==$0       P2                                12
TABLE PARTITION     BIN$vPEKh0mZQ9KenijFQ/9URQ==$0       P2                                12
TABLE PARTITION     BIN$vPEKh0mZQ9KenijFQ/9URQ==$0       P3                                12
TABLE PARTITION     BIN$vPEKh0mZQ9KenijFQ/9URQ==$0       P1                                12
TABLE PARTITION     FANGXC12                             P2                                12
TABLE PARTITION     FANGXC12                             P1                                12
TABLE PARTITION     FANGXC12                             P3                                12
TABLE PARTITION     PART_TAB1                            P3                                12
TABLE PARTITION     PART_TAB1                            P2                                12
TABLE PARTITION     PART_TAB1                            P1                                12
TABLE PARTITION     PART_TAB2                            P1                                12
TABLE PARTITION     PART_TAB2                            P3                                12
TABLE PARTITION     PART_TAB2                            P2                                12
TABLE PARTITION     PART_TAB3                            P3                                12
TABLE PARTITION     PART_TAB3                            P2                                12
TABLE PARTITION     PART_TAB3                            P1                                12

48 rows selected

而修改为加上table_name = a.object_name等后，则无重复记录

SELECT *
  FROM (select object_type,
               object_name,
               subobject_name,
               decode(status, 'INVALID', '*', '') status,
               decode(object_type,
                      'TABLE',
                      (select COUNT(*)
                         from user_tables
                        where table_name = object_name),
                      'TABLE PARTITION',
                      (select COUNT(*)
                         from user_tab_partitions
                        where  table_name = a.object_name
                        AND    partition_name = subobject_name),
                      'INDEX',
                      (select COUNT(*)
                         from user_indexes
                        where index_name = object_name),
                      'INDEX PARTITION',
                      (select COUNT(*)
                         from user_ind_partitions
                        where partition_name = subobject_name
                        and INDEX_NAME = A.OBJECT_NAME),
                      'LOB',
                      (select COUNT(*)
                         from user_segments
                        where segment_name = object_name),
                      null) CNT
          from user_objects a
         order by object_type, object_name)
WHERE CNT > 1

OBJECT_TYPE         OBJECT_NAME                                                                      SUBOBJECT_NAME                 STATUS        CNT
------------------- -------------------------------------------------------------------------------- ------------------------------ ------ ----------


因此最终代码应修正为如下：

select object_type,
       object_name,
       decode(status, 'INVALID', '*', '') status,
       decode(object_type,
              'TABLE',
              (select tablespace_name
                 from user_tables
                where table_name = object_name),
              'TABLE PARTITION',
              (select tablespace_name
                 from user_tab_partitions
                where table_name = a.object_name
                  AND partition_name = subobject_name),
              'INDEX',
              (select tablespace_name
                 from user_indexes
                where index_name = object_name),
              'INDEX PARTITION',
              (select tablespace_name
                 from user_ind_partitions
                where partition_name = subobject_name
                  and INDEX_NAME = A.OBJECT_NAME),
              'LOB',
              (select tablespace_name
                 from user_segments
                where segment_name = object_name),
              null) tablespace_name
  from user_objects a
order by object_type, object_name


注：会产生这个原因主要是因为分区表时候各个分区表都可以用相同的分区名，比如都可以用P1,P2..，所以要加上表名才唯一，TOM不小心忽略了这一点了，这个问题和ORACLE的版本无关。
create table part_tab1 (id int)
    partition by range (id)
    (
    partition p1 values less than (10),
    partition p2 values less than (20),
    partition p3 values less than (maxvalue)
    )
    ;
create table part_tab2 (id int)
    partition by range (id)
    (
    partition p1 values less than (10),
    partition p2 values less than (20),
    partition p3 values less than (maxvalue)
    )
    ;
create index idx_part_tab1 on part_tab1(id) local;
create index idx_part_tab2 on part_tab2(id) local;






第2处错误，第8章的384页
TOM说如下两个语句等价，其实不等价

select a.username, count(*)
  from all_users a, all_objects b
where a.username = b.owner (+)
group by a.username;

select a.username,
      (select count(*) from all_objects b where b.owner = a.username) cnt
from all_users a

简单试验即可得知，一边为1，一边查询结果为0：

SQL> select  username,count(*) from a, b where a.username=b.owner(+) group by a.username order by username;

USERNAME                         COUNT(*)
------------------------------ ----------
AAA                                     1
ANONYMOUS                               1
BOSSWG                                  1
CTXSYS                                339
DBSNMP                                 46
DIP                                     1
DMSYS                                 189
EXFSYS                                281
LJB                                   272
LJB1                                    1
LJB2                                    1
MDDATA                                  1
MDSYS                                 885
MGMT_VIEW                               1
OLAPSYS                               720
ORDPLUGINS                             10
ORDSYS                               1669
OUTLN                                   8
SCOTT                                   6
SI_INFORMTN_SCHEMA                      8
SYS                                 22979
SYSMAN                               1321
SYSTEM                                454
TSMSYS                                  3
WMSYS                                 242
XDB                                   680
YXL                                     2

27 rows selected
SQL> SELECT username,(select count(*) from b where b.owner=a.username) cnt  from a order by username;
USERNAME                              CNT
------------------------------ ----------
AAA                                     0
ANONYMOUS                               0
BOSSWG                                  0
CTXSYS                                339
DBSNMP                                 46
DIP                                     0
DMSYS                                 189
EXFSYS                                281
LJB                                   272
LJB1                                    0
LJB2                                    0
MDDATA                                  0
MDSYS                                 885
MGMT_VIEW                               0
OLAPSYS                               720
ORDPLUGINS                             10
ORDSYS                               1669
OUTLN                                   8
SCOTT                                   6
SI_INFORMTN_SCHEMA                      8
SYS                                 22979
SYSMAN                               1321
SYSTEM                                454
TSMSYS                                  3
WMSYS                                 242
XDB                                   680
YXL                                     2
27 rows selected




前面COUNT(*) 是错的，哪怕b表没数据，至少也会返回一行。COUNT(连接键)的写法才对

--如下才是等价的！


select a.username, count(owner)
  from all_users a, all_objects b
where a.username = b.owner (+)
group by a.username;

select a.username,
      (select count(*) from all_objects b where b.owner = a.username) cnt
from all_users a




要特别注意，只有在左连接的时候，这个写法才和标量子查询等价，如下两种切记是不等价的，
可以回顾一下文章开头开发人员的案例从而进一步加深印象。

select a.username, count(owner)
  from all_users a, all_objects b
where a.username = b.owner
group by a.username;

select a.username,
      (select count(*) from all_objects b where b.owner = a.username) cnt
from all_users a