SQL несколько общих критериев WHERE

#sql #database #oracle #hierarchical-data

#sql #База данных #Oracle #иерархический-данные

Вопрос:

Я пытаюсь найти хороший и эффективный способ выполнения такого запроса:

 SELECT *
  FROM tableA a
 WHERE    a.manager IN (    SELECT id
                                 FROM tableB b
                           CONNECT BY PRIOR b.id = b.manager_id
                           START WITH b.id = 'managerBob')
       OR a.teamLead IN (    SELECT ID
                               FROM tableB b
                         CONNECT BY PRIOR b.ID = b.manager_id
                         START WITH b.ID = 'managerBob')
       OR a.creator IN (    SELECT id
                              FROM tableB b
                        CONNECT BY PRIOR b.id = b.manager_id
                        START WITH b.id = 'managerBob')

Как вы можете видеть, я пытаюсь использовать несколько предложений WHERE, но каждое предложение использует один и тот же набор данных в правой части уравнения. Кажется, что он работает очень медленно, если я использую более одного предложения, и я почти уверен, что это потому, что Oracle выполняет каждый подзапрос. Есть ли способ заставить что-то подобное работать?

 SELECT *
  FROM tableA a
 WHERE    a.manager, 
          a.teamLead, 
          a.creator in (    SELECT id
                                 FROM tableB b
                           CONNECT BY PRIOR b.id = b.manager_id
                           START WITH b.id = 'managerBob')

Кстати, извините, если это то, что я мог бы использовать в Google, я не уверен, как это назвать.

Ответ №1:

Факторинг подзапросов может помочь:

 WITH people AS
(    SELECT id
       FROM tableB b
    CONNECT BY PRIOR b.id = b.manager_id
      START WITH b.id = 'managerBob'
)
SELECT *
  FROM tableA a
 WHERE    a.manager IN (SELECT id FROM people)
       OR a.teamLead IN (SELECT id FROM people)
       OR a.creator IN (SELECT id FROM people)

1. Этот синтаксис меня беспокоит, но он самый быстрый с точки зрения выполнения. Спасибо, что рассказали мне, как это называется, хотя это была самая неприятная часть попытки разобраться в этом 🙂

Ответ №2:

Вы можете сделать:

 WITH bob_subordinates AS (
(    SELECT id
       FROM tableB b
 CONNECT BY PRIOR b.id = b.manager_id
 START WITH b.id = 'managerBob')
SELECT * FROM tableA a
 WHERE a.manager in  (select id from bob_subordinates)
    OR a.teamlead in (select id from bob_subordinates)
    or a.creator  in (select id from bob_subordinates)

Альтернатива (проверьте использование DISTINCT: если идентификаторы не уникальны в таблице B, то это не эквивалентно):

 WITH bob_subordinates AS (
(    SELECT DISTINCT id
       FROM tableB b
 CONNECT BY PRIOR b.id = b.manager_id
 START WITH b.id = 'managerBob')
SELECT DISTINCT a.*
  FROM tableA a JOIN bob_subordinates b ON b.id IN (a.manager, a.teamlead, a.creator);

Ответ №3:

ОБНОВЛЕНИЕ в соответствии с комментариями — попробуйте

 SELECT A.* FROM 
(SELECT bb.id FROM tableB bb CONNECT BY PRIOR bb.id = bb.manager_id START WITH bb.id = 'managerBob') B INNER JOIN TABLEA A ON B.ID IN (A.MANAGER, A.TEAMLEAD, A.CREATOR)

1. Вы можете немного упростить предложение where: WHERE b.id in (a.manager, a.teamlead, a.creator)

2. Я бы хотел, чтобы люди перестали пытаться научить людей использовать , вместо JOIN 🙁

3. Это работает, и я предпочитаю этот синтаксис, но Oracle обрабатывает его не так быстро, как решение WITH() . Я наблюдаю увеличение времени выполнения примерно на 50-70% с использованием этого синтаксиса. Я не уверен, почему, возможно, Oracle каким-то образом оптимизирует оператор WITH.