标量子查询真不如表联结吗？

在数据库日新月异的时代，不实际跑跑，谁快谁慢还真不好说。

有这么一个员工表：

create table tb_emp03(
     id number(8,0) primary key,
     name nvarchar2(60),
     salary number(5,0),
     deptno number(2,0)
)

可以这么填充数据：

Insert into tb_emp03
select rownum,dbms_random.string('*',dbms_random.value(6,20)),dbms_random.value(1000,50000),dbms_random.value(0,10) from dual
connect by level<=2000000
order by dbms_random.random

还有这么一个部门表：

create table tb_dept03(
    id number(5,0) primary key,
    name nvarchar2(20)
)

它的数据是定死的：

insert into tb_dept03(id,name) values('1','sales');
insert into tb_dept03(id,name) values('2','manage');
insert into tb_dept03(id,name) values('3','postsale');
insert into tb_dept03(id,name) values('4','dev');
insert into tb_dept03(id,name) values('5','manu');
insert into tb_dept03(id,name) values('6','maintenance');
insert into tb_dept03(id,name) values('7','fix');
insert into tb_dept03(id,name) values('8','counter');
insert into tb_dept03(id,name) values('9','sweeper');
insert into tb_dept03(id,name) values('10','cleaner');

然后，做个带标量子查询的sql，目的是把员工的部门名称秀出来：

select emp.id,emp.name,emp.salary,emp.deptno,(select dept.name from tb_dept03 dept where dept.id=emp.deptno) as deptname from tb_emp03 emp where emp.salary>49990
已选择381行。

已用时间:  00: 00: 00.40

用时0.4秒。

而把上面的SQL改成左联结再跑：

select emp.id,emp.name,emp.salary,emp.deptno,dept.name from tb_emp03 emp left join tb_dept03 dept on emp.deptno=dept.id where emp.salary>49990


已选择381行。

已用时间:  00: 00: 00.40

发现运行时间两者几乎一样啊。

再跑跑两者的解释计划：

SQL> select emp.id,emp.name,emp.salary,emp.deptno,(select dept.name from tb_dept03 dept where dept.id=emp.deptno) as deptname from tb_emp03 emp where emp.salary>49990;
已用时间:  00: 00: 00.00

执行计划
----------------------------------------------------------
Plan hash value: 84985261

--------------------------------------------------------------------------------
------------

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)
| Time     |

--------------------------------------------------------------------------------
------------

|   0 | SELECT STATEMENT            |              |   418 | 42218 |  3609   (2)
| 00:00:44 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TB_DEPT03    |     1 |    35 |     1   (0)
| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | SYS_C0011427 |     1 |       |     1   (0)
| 00:00:01 |

|*  3 |  TABLE ACCESS FULL          | TB_EMP03     |   418 | 42218 |  3609   (2)
| 00:00:44 |

--------------------------------------------------------------------------------
------------


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

   2 - access("DEPT"."ID"=:B1)
   3 - filter("EMP"."SALARY">49990)

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL>

Cost是3609

 

再看看左联结的：

SQL> select emp.id,emp.name,emp.salary,emp.deptno,dept.name from tb_emp03 emp left join tb_dept03 dept on emp.deptno=dept.id where emp.salary>49990;
已用时间:  00: 00: 00.00

执行计划
----------------------------------------------------------
Plan hash value: 3854866026

--------------------------------------------------------------------------------
---

| Id  | Operation             | Name      | Rows  | Bytes | Cost (%CPU)| Time
  |

--------------------------------------------------------------------------------
---

|   0 | SELECT STATEMENT      |           |   418 | 56848 |  3612   (2)| 00:00:4
4 |

|*  1 |  HASH JOIN RIGHT OUTER|           |   418 | 56848 |  3612   (2)| 00:00:4
4 |

|   2 |   TABLE ACCESS FULL   | TB_DEPT03 |    10 |   350 |     3   (0)| 00:00:0
1 |

|*  3 |   TABLE ACCESS FULL   | TB_EMP03  |   418 | 42218 |  3609   (2)| 00:00:4
4 |

--------------------------------------------------------------------------------
---


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

   1 - access("EMP"."DEPTNO"="DEPT"."ID"(+))
   3 - filter("EMP"."SALARY">49990)

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL>

Cost是3612，和标量子查询也差不多。

到这里，可以得出结论了，标量子查询不是肯定慢的，左联结也未必快了。

 在DB优化器越做越好的今天，很多以前的理念都不能墨守成规了。

--2020-01-27--