oracle标量子查询

SQL> select * from table( dbms_xplan.display_cursor( format=> 'allstats last' ));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------

SQL_ID  583tdq3s2x51d, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ department_id,department_name
,(select count(*) from employees emp where
emp.department_id=departments.department_id) as count from departments


Plan hash value: 385474422


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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                   |      1 |        |     27 |00:00:00.01 |       4 |
|   1 |  SORT AGGREGATE   |                   |     27 |      1 |     27 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN| EMP_DEPARTMENT_IX |     27 |     10 |    106 |00:00:00.01 |       4 |
|   3 |  TABLE ACCESS FULL| DEPARTMENTS       |      1 |     27 |     27 |00:00:00.01 |       4 |
-------------------------------------------------------------------------------------------------


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

   2 - access("EMP"."DEPARTMENT_ID"=:B1)

看这个执行计划是先读取departments表，把每行的department_id传递给emp表，emp表在聚合计算count(*)这个执行计划的顺序是与常规的顺序不一样的。

标量子查询会缓存，就是对于dept的每行deptno不是每次都会去执行count(*),对于相同的id，oralce会在内存中建立一个hash表，id作为键值，第二次或许就缓存了，直接读取count(*)的值，这样就提高了效率。如果deptno的唯一值超过了hash表的大小，也就是255，就是下面的处理了：

The answer is the same for all these questions and is rather simple: Oracle Database will not be able to cache the second or nth value to that slot in the hash table. For example, what if the third row returned by the query contains the DEPTNO = 30 value? Further, suppose that DEPTNO = 30 is to be assigned to exactly the same hash table slot as DEPTNO = 10. The database won’t be able to effectively cache DEPTNO = 30 in this case—the value will never make it into the hash table. It will, however, be “partially cached.” Oracle Database still has the hash table with all the previous executions, but it also keeps the last scalar subquery result it had “next to” the hash table. That is, if the fourth row also includes a DEPTNO = 30 value, Oracle Database will discover that the result is not in the hash table but is “next to” the hash table, because the last time it ran the scalar subquery, it was run with an input of 30. On the other hand, if the fourth row includes a DEPTNO = 40 value, Oracle Database will run the scalar subquery with the DEPTNO = 40 value (because it hasn’t seen that value yet during this query execution) and overwrite the DEPTNO = 30 result. The next time Oracle Database sees DEPTNO = 30 in the result set, it’ll have to run that scalar subquery again.

就是说对于传入标量子查询中的值，如果有很多重复的，就可以利用缓存的特性来大大减少子查询的执行次数，来提高效率