主页 / 问题 / 1215300
Accepted
Виталий Яндулов
Asked:2021-12-07 19:21:32 +0000 UTC2021-12-07 19:21:32 +0000 UTC

Oracle 11g 的 CROSS APPLY 类似物及其“成本”

  • 772

任务：从号码表中找到每个员工的电话号码。但仅当每个员工只有一个数字时才输出，否则null而不是数字。

解决方案CROSS APPLY：

SELECT n.name, t.phone_num
  FROM emp_names n
  CROSS APPLY (SELECT phone_num 
                 FROM phones p
                WHERE p.name = n.name
                GROUP BY phone_num
                HAVING count(*)=1) t

但是这个方法在 11g 中不适用，因为。还没有CROSS APPLY。我正在尝试使用子查询的解决方案：

SELECT n.name,
       (SELECT phone_num
          FROM phones p
         WHERE p.name = n.name
         GROUP BY phone_num
        HAVING count(*)=1) phone_num
  FROM emp_names n

是否相当于请求的成本？毕竟，如果在表中emp_names我们有一个名称 1000 次，那么将有 1000 个相同的子查询具有相同的答案。同时，phones从表中拉出所有相同号码的人并加入也不是一种选择，因为 这张桌子很大。

有没有类似CROSS APPLYOracle 11g 更经济的方式？

sql

3 个回答

  1. 尝试与此查询进行比较：

    select n.name, t.num
from names n
left join (
    select name, num 
    from phones p
    where exists (select 1
        from names n where n.name = p.name) 
    group BY name, num
    having count(*) = 1) t on t.name = n.name
/

    您可以在真实数据或尽可能接近真实的数据示例上比较执行计划。因此，我将避免将上述要求评估为最“经济”的要求。

    对比一下CROSS APPLY（问题最有可能是关于OUTER APPLY），你可以用11g中的等价物LATERAL（因为没有官方支持，只针对测试环境）：

    alter session set events '22829 trace name context forever';

select n.name, t.num
from names n, lateral (
    select num 
    from phones p
    where p.name = n.name
    group BY num
    having count(*) = 1)(+) t
/

    对db<>fiddle的请求。

    • 4
  2. Best Answer

    子句中的相关子查询SELECT（问题中的第二个）通常比其等效子查询更有效OUTER JOIN（但并非总是如此，具体取决于数据）。

    问题中假设对于外部表中的 1000 个相同的值，子查询将执行 1000 次并返回相同的答案是false。在这种情况下，数据库将缓存子查询的结果（标量子查询缓存）将是一个优势。即对于来自外部表的每一组值，子查询只会执行一次，其结果会被写入内存中的临时哈希表。对于所有后续相同的值，将从该哈希表中获取结果。

    一个支持上述内容的小例子。架构准备：

    create table names (name) as
    select 'name1' from dual connect by level<=3
/
create table phones (name, num) as
    select 'name1','007-777/1234567' from dual  
/
create or replace function subq (s varchar2) return varchar2 as
begin
    dbms_application_info.set_client_info (userenv ('client_info')+1);
    return s;
end subq;
/

    以下查询将返回三条记录，但子查询只会执行一次：

    exec dbms_application_info.set_client_info (0)
/
select n.name, (
    select subq (p.num) 
    from phones p
    where p.name = n.name 
    group BY  num having count(*) = 1) num
from names n
/
NAME  NUM             
----- ----------------
name1 007-777/1234567 
name1 007-777/1234567 
name1 007-777/1234567 

select userenv ('client_info') subqcalls from dual
/
SUBQCALLS
----------
1
    • 4

  3. 你可以这样尝试

    SELECT n.name, MAX(p.phone_num) -- идея тут взять тот единственный номер
FROM emp_names n
  LEFT JOIN phones p
    ON p.name = n.name
GROUP BY 
   n.name
HAVING count(p.phone_num)=1
    • 2

相关问题