Аналог CROSS APPLY для Oracle 11g и его "стоимость"

Question

Задача: Найти для каждого сотрудника номер телефона из таблицы номеров. Но вывести только, если на каждого сотрудника там только один номер, иначе 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 APPLY для Oracle 11g?

Answer 1

Можно так попробовать

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

Answer 2

Попробуйте сравнить с таким запросом:

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.

Answer 3

Корреляционный подзапрос в SELECT клаузе (второй в вопросе), часто будет эфективней по сравнению с эквивалентным ему OUTER JOIN (но не всегда, зависит от данных).

Высказанное в вопросе предположение, что для 1000 одинаковых значений из внешней таблицы, подзапрос будет выполнен 1000 раз и вернёт одинаковый ответ - неверно. В данном случае, как раз будет преимуществом тот факт, что БД будет кешировать результат подзапроса (Scalar Subquery Caching). То есть, для каждого сета значений из внешней таблицы, подзапрос выполнится только один раз, а его реультат запишется во временную хэш таблицу в памяти. Для всех последующих идентичных значений, результат будет браться из этой хэш таблицы.

Небольшой пример в подтверждение вышесказанному. Подготовка схемы:

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

БЛОГ НА HUSL

Аналог CROSS APPLY для Oracle 11g и его "стоимость"

Ответы (3 шт):