值得反复研读的表连接之SCALAR SUBQUERY 方式

最新推荐文章于 2024-04-14 23:19:14 发布

DB架构

最新推荐文章于 2024-04-14 23:19:14 发布

阅读量555

点赞数

分类专栏： SQL优化文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/u011868279/article/details/127472789

版权

SQL优化专栏收录该内容

17 篇文章 2 订阅

订阅专栏

本文探讨了标量子查询的概念及其在SQL查询中的应用。解释了为何在特定情况下应避免使用标量子查询，尤其是当主表数据量大且连接列基数高时，这可能严重影响查询性能。同时，建议将标量子查询转换为外连接以提高效率，并通过实例展示了如何进行转换。最后，强调了考虑索引优化和查询改写对于提升SQL性能的重要性。

摘要由CSDN通过智能技术生成

当一个子查询介于select 与from之间，这种子查询就叫标量子查询。

select e.ename,
       e.sal,
       (select d.dname from dept d where d.deptno = e.deptno) dname
  from emp e;

scott@orclpdb1:orclcdb> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5123bdvqcrffd, child number 0
-------------------------------------
select /*+gather_plan_statistics*/ e.ename,        e.sal,
(select d.dname from dept d where d.deptno = e.deptno) dname   from emp
e

Plan hash value: 2981343222

-------------------------------------------------------------------------------------------------
| Id | Operation                   | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |     14 |00:00:00.01 |       8 |
|   1 | TABLE ACCESS BY INDEX ROWID| DEPT    |      3 |      1 |      3 |00:00:00.01 |       5 |
|* 2 |   INDEX UNIQUE SCAN         | PK_DEPT |      3 |      1 |      3 |00:00:00.01 |       2 |
|   3 | TABLE ACCESS FULL          | EMP     |      1 |     14 |     14 |00:00:00.01 |       8 |
-------------------------------------------------------------------------------------------------

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

   2 - access("D"."DEPTNO"=:B1)

22 rows selected.

scott@orclpdb1:orclcdb>

尽量避免使用标量子查询，假如主表示返回大量数据，主表的连接列基数很高，那么子查询中的表会被多次扫描，从而严重影响SQL性能。如果主表数据量小，或者主表的连接列基数很低，那么这个时候我们也可以使用标量子查询，但要记得要给子查询中表的连接列建立索引。

Q: 如何优化SQL中标量子查询

可以将标量子查询等价改写为外连接，从而使它们可以进行HASH连接。

Q:为什么要将标量子查询改写为外连接而不是内连接呢？

因为标量子查询是一个传值的过程，如果主表传值给子查询，子查询没有查询到数据，这个时候会显示NULL。如果将标量子查询改写为内连接，会丢失没有关联上的数据。

scott@orclpdb1:orclcdb> select d.dname,
  2         d.loc,
       (select max(e.sal) from emp e where e.deptno = d.deptno) max_sal
  4    from dept d;

DNAME          LOC              MAX_SAL
-------------- ------------- ----------
ACCOUNTING     NEW YORK            5000
RESEARCH       DALLAS              3000
SALES          CHICAGO             2850
OPERATIONS     BOSTON

4 rows selected.

scott@orclpdb1:orclcdb> select d.dname, d.loc, e.max_sal
  2    from dept d
  left join (select max(sal) max_sal, deptno from emp group by deptno) e
  4      on d.deptno = e.deptno;

DNAME          LOC              MAX_SAL
-------------- ------------- ----------
ACCOUNTING     NEW YORK            5000
RESEARCH       DALLAS              3000
SALES          CHICAGO             2850
OPERATIONS     BOSTON

4 rows selected.

scott@orclpdb1:orclcdb>