什么是快乐星球？什么又是“标量子查询”呢？

- 日理万妓 2022-10-14 05:40 307阅读 0赞

现在有个很火的歌词，“什么是快乐星球？”，那个劲儿，其实还很难拿捏，

![76be0629b93538756048367e2604f67a.png][]

同理地，以前总是在资料或者培训中听到“标量子查询”，好像很神秘的一个概念，什么是“标量子查询”？

其实从形式上讲，标量子查询就是在查询语句select和from之间的子查询，这就决定了他的返回值肯定只能是1条，如果是多条，就可能提示“ORA-01427：单行子查询返回多个行的错误”。

这是一个标量子查询的例子，

SQL> select /*+ gather_plan_statistics */ e.first_name, e.salary,
               (select d.department_name from departments d
          where d.department_id = e.department_id) d_name
           from employees e;

他的执行计划，

![beb82f05d26a0bae723286f64cbb5551.png][]

从执行计划，可以看到，主表employees通过连接列(department\_id)传值给子查询中的表(departments)，上述执行计划中第二步的\* - :B1就是传值，而且传值12次。

之所以是12次，因为主表的连接列基数是12，

SQL> select count(distinct nvl(department_id,0)) from employees;
    COUNT(DISTINCTNVL(DEPARTMENT_ID,0))
    -----------------------------------
             12

从这个过程，我们能知道，如果必须使用标量子查询，要为子查询的连接列创建索引，毕竟主表连接列基数是多少，子查询就会被执行多少次，因此能推导出，如果主表的数据量很少，或者主表的连接列基数很低，子查询的连接列创建了索引，是可以使用标量子查询的。反之，主表返回的数据量很多，主表的连接列基数很高，甚至子查询的连接列没索引，应该避免使用标量子查询。

为了避免使用标量子查询，一般可以改写为外连接，

SQL> select /*+ gather_plan_statistics */ e.first_name, e.salary, d.department_name
           from employees e 
           left join departments d
             on e.department_id = d.department_id;

他的执行计划，

![a25b0cd22da2111b9bcca5e0c0381c93.png][]

可以看到，原来的子查询改为了全表扫描，但是仅需要扫描一次，和主表做哈希外连接。

之所以是外连接，因为每次主表传值给子查询，但未必在子查询中就存在对应的记录，但是如果子查询的连接列就是主表的外键，或者业务上就是1:1的关系(这里用到了落落老师在培训和书中提到的数量关系)，外连接可以改为内连接，他俩是等价的，如本例中，employees共107条，

SQL> select count(*) from employees;
      COUNT(*)
    ----------
           107

如果采用外连接，返回107条，

SQL> select count(*) from employees e left join departments d on e.department_id = d.department_id;
      COUNT(*)
    ----------
           107

如果采用内连接，只会返回106条，说明在employees中有一条记录在departments是找不到的，

SQL> select count(*) from employees e join departments d on e.department_id = d.department_id;
      COUNT(*)
    ----------
           106

这个例子中，标量子查询departments和主表employees是1:N的关系，因此改为外连接的时候，不需要去重。

如果是这种，标量子查询和主表是N:1的关系，

SQL> select d.department_name,
                (select max(e.salary) from employees e where e.department_id = d.department_id) max_salary
           from departments d;
    DEPARTMENT_NAME       MAX_SALARY
    --------------------- ----------
    Shipping              8200
    Human Resources       6500
    Accounting            12008
    Executive             24000
    Purchasing            11000
    Public Relations      10000
    Administration        4400
    Marketing             13000
    IT                    9000
    Finance               12008
    Sales                 14000
    IT Support
    Operations
    Payroll
    Construction
    Government Sales
    Retail Sales
    Contracting
    Recruiting
    Control And Credit
    NOC
    Treasury
    Manufacturing
    Corporate Tax
    IT Helpdesk
    Shareholder Services
    Benefits
    27 rows selected.

改成外连接，需要对标量子查询做去重(group by)，可以先去重，再做关联，如下所示，

SQL> select /*+ gather_plan_statistics */ d.department_name, max_salary
           from departments d
           left join (select max(salary) max_salary, department_id from employees group by department_id) e
             on e.department_id = d.department_id;
    DEPARTMENT_NAME        MAX_SALARY
    ---------------------- ----------
    Shipping                8200
    Human Resources         6500
    Accounting              12008
    Executive               24000
    Purchasing              11000
    Public Relations        10000
    Administration          4400
    Marketing               13000
    IT                      9000
    Finance                 12008
    Sales                   14000
    IT Support
    Operations
    Payroll
    Construction
    Government Sales
    Retail Sales
    Contracting
    Recruiting
    Control And Credit
    NOC
    Treasury
    Manufacturing
    Corporate Tax
    IT Helpdesk
    Shareholder Services
    Benefits
    27 rows selected.

他的执行计划，

![2920c64cbf4846f8d850e8f320f68d32.png][]

或者先关联，再做去重，如下所示，

SQL>  select /*+ gather_plan_statistics */ d.department_name, max(e.salary)
            from departments d
            left join employees e
              on e.department_id = d.department_id
           group by d.department_name;
    DEPARTMENT_NAME       MAX(E.SALARY)
    --------------------- -------------
    Sales                  14000
    Recruiting
    Control And Credit
    IT Support
    Government Sales
    Retail Sales
    Corporate Tax
    Marketing              13000
    IT Helpdesk
    Purchasing             11000
    Administration         4400
    Contracting
    NOC
    Executive              24000
    IT                     9000
    Finance                12008
    Shipping               8200
    Public Relations       10000
    Payroll
    Shareholder Services
    Benefits
    Human Resources        6500
    Accounting             12008
    Operations
    Construction
    Treasury
    Manufacturing
    27 rows selected.

他的执行计划，

![3bd8f328936de11efe688ff61facb148.png][]

从执行计划看，这两种写法，成本几乎相同。

理解标量子查询的适用场景，以及改成外连接是否需要去重的意义，是对待这种标量子查询的关键。

**近期更新的文章：**

《[VMWare增加磁盘空间的操作][VMWare]》

《[曾经运维生涯中的几个“最”][Link 1]》

《[一条半连接SQL的优化过程][SQL]》

《[如何找到隐式转换的SQL？][SQL 1]》

《[为什么日期不建议使用VARCHAR2或者NUMBER？][VARCHAR2_NUMBER]》

**文章分类和索引：**

《[公众号700篇文章分类和索引][700]》

[76be0629b93538756048367e2604f67a.png]: /images/20221014/c34a929f4c364c5fbc43c7a2db16e874.png
[beb82f05d26a0bae723286f64cbb5551.png]: /images/20221014/702f78643e9c4f5288f827d3ccbb249c.png
[a25b0cd22da2111b9bcca5e0c0381c93.png]: /images/20221014/1bc7d9d35616413ebab3f52b1be999fc.png
[2920c64cbf4846f8d850e8f320f68d32.png]: /images/20221014/e9d284258427435bb45be05061d8f207.png
[3bd8f328936de11efe688ff61facb148.png]: /images/20221014/691b473af7ba49478726c6e295787f52.png
[VMWare]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a7e8ddf52efe6fd6ca95fc4405d1bdeff10c528b571824fed58a57b11063c0960066d675&idx=1&mid=2247492123&scene=21&sn=518d5e367e0dbd5486f2d29745b41012#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a7f2ddf52ee49775b374898978d2f13624f1e51dcbfecdfdd32925332132e4eaa88cc7bd&idx=1&mid=2247492097&scene=21&sn=f2c338461919594062136eb10a70e9c4#wechat_redirect
[SQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a404ddf52d127b63d6a15e3d204db28426064a005e58ea83a6b89f30256eab76d464aad2&idx=1&mid=2247492087&scene=21&sn=743c7ae9ad0d77073f291ae5abc2ef29#wechat_redirect
[SQL 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a414ddf52d02b74d3e476d6c4daf1aa9ba6a5be958a4f19d9b880a0029b43895eebceb2f&idx=1&mid=2247492071&scene=21&sn=05655f3f6ec05fd54b94097552708259#wechat_redirect
[VARCHAR2_NUMBER]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a411ddf52d07968c523b8869089f79e531474503306a0561ad1be8ed36625ef8ad8d3b4a&idx=1&mid=2247492066&scene=21&sn=06e18747abd40914a505c0c62da889c0#wechat_redirect
[700]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5dfddf52cc949a5b8b316ee905927ff399713b34aec7d806c656e8ada09bfb562c039ca&idx=1&mid=2247491628&scene=21&sn=83bd320bd28da84501fada2e8d515e77#wechat_redirect