Oracle: оператор WITH выполняется медленно

Недавно я пытался включить больше операторов WITH в мой Oracle SQL, чтобы создать более чистый и эффективный код. Тем не менее, я продолжаю чувствовать, что это на самом деле менее эффективно, но только при определенных условиях, что разочаровывает.

Одним из примеров является оператор WITH, созданный для ранжирования телефонных номеров. Я хочу получить лучшие из всех действующих / активных телефонных номеров.

Вот пример оператора WITH, который я использую для ранжирования телефонных номеров:

Select * From (
WITH
PHONE_RANK as        
-- Description: Phone numbers with a RANK_NO generated based on importance.
(
    Select 
        ID as ID,
        PHONE_AREA || PHONE_NUMBER || PHONE_EXT as PHONE_NUMBER,
        TELE_CODE as TELE_CODE,
        PRIMARY_IND as PRIMARY_IND,
    --Generate a RANK_NO
        row_Number() over
        (
            Partition By ID
            Order By 
                ID,
                Decode(PRIMARY_IND,  'Y',1,  2),
                Decode(TELE_CODE,  'MA',1,  'PR',2,  'CA',3,  'CELL',4,  99)
        ) as RANK_NO
    From 
        SPRTELE
    Where 
        STATUS_IND is null
        and TELE_CODE in ('MA', 'PR')
        and Length(PHONE_AREA || PHONE_NUMBER || PHONE_EXT) >= 7
)

Select SPRIDEN.ID, PHONE_NUMBER
From SPRIDEN, PHONE_RANK  --SPRIDEN contains basic info (name, id, etc)
Where 
    SPRIDEN.CHANGE_IND is NULL
    SPRIDEN.ID = PHONE_RANK.ID
    and RANK_NO = 1
)

PHONE_RANK работает довольно быстро, если присоединиться к таблице SPRIDEN. Тем не менее, когда я пытаюсь ограничить его с помощью RANK_NO, это занимает гораздо больше времени для запуска.

  • Без каких-либо критериев для PHONE_RANK он запускается менее чем за 0,2 секунды.
  • С and PHONE_RANK.RANK_NO = 1 это займет около 3,5 секунд.
  • Когда используешь and PHONE_RANK.RANK_NO = 1 это часть более сложного кода, он имеет тенденцию к масштабированию.

(Начать редактировать)

Я использовал подзапросы для этого процесса. Скорость отличная, и они неплохие для телефонных номеров. Однако, поскольку наиболее распространенными числами являются PR и MA, а один или другой могут не существовать, я использую два подзапроса для каждого из них.

Мой "план" прост: выведите лучшую запись для каждого учащегося / человека на основе основного показателя и телефонного кода. Таким образом, если есть только запись PR или MA, мы все равно получим результат, так как будут выбраны лучшие. Тем не менее, иногда Phone MA или PR # не существует, но мы по-прежнему хотим, чтобы информация об ученике, таким образом, ограничение запроса внутри основного запроса не является идеальным (но второе предложение WITH должно быть в состоянии позаботиться об этом).

В конечном итоге я хотел бы применить результат к таблице адресов, которая похожа на SPRTELE. Подзапрос для получения номера телефона - это хорошо, но использование отдельных подзапросов для получения улицы, города, штата и почтового индекса не является идеальным. И, опять же, мы должны вытащить как PR, так и MA адрес, если того или другого не существует.

По моему мнению, таблицы адресов и телефонов настроены плохо, так как можно иметь кратные значения для любого типа телефона / адреса (TELE_CODE, ATYP_CODE), и все / ни одна из записей для каждого типа могут быть активными. Кроме того, данные не поддерживаются должным образом (и было бы чудовищно поддерживать их).

Для поля таблицы SPRTELE (индексы помечены звездочкой):

*ID (004000, 123456, etc)
*SEQNO (1, 2... n)
*TELE_CODE (MA, PR, etc)
ACTIVITY_DATE
COMMENT
CTRY_CODE_PHONE
DATA_ORIGIN
INTL_ACCESS
PHONE_AREA
PHONE_EXT
PHONE_NUMBER
PRIMARY_IND
ADDR_SEQNO
ATYP_CODE
STATUS_IND
UNLIST_IND
USER_ID

Если это полезно, вот подзапрос, который я использую для телефонных номеров. Я также включил один для постоянных телефонных номеров (TELE_CODE = 'PR').

(
Select PHONE_AREA || PHONE_NUMBER || PHONE_EXT
From SPRTELE
Where 
    ID = S1.ID
    and STATUS_IND is Null
    and TELE_CODE = 'MA'
    and SEQNO =
    (
        Select Max(SEQNO)
        From SPRTELE
        Where 
            ID = S1.ID
            and STATUS_IND is Null
            and TELE_CODE = 'MA'
    )
) as MA_Phone

Кроме того, это подзапросы, используемые для получения ОДНОГО адреса. Опять же, я должен использовать два, чтобы получить оба типа адресов MA и PR, в случае, если один или другой отсутствует.

--MAILING STREET ADDRESS
(
    Select STREET_LINE1 || ' ' || STREET_LINE2 || ' '|| STREET_LINE3
    From SPRADDR S2
    Where 
        S2.ID = S1.ID
        and ATYP_CODE = 'MA'
        and STATUS_IND is NULL
        and SEQNO =
        (
            Select MAX(SEQNO)
            From SPRADDR S2
            Where 
                S2.ID = S1.ID
                and ATYP_CODE = 'MA'
                and STATUS_IND is NULL
        )
) as MA_Street,

--MAILING CITY STATE ZIP
(
    Select CITY || ', ' || STAT_CODE || ' ' || ZIP
    From SPRADDR S2
    Where 
        S2.ID = S1.ID
        and ATYP_CODE = 'MA'
        and STATUS_IND is NULL
        and SEQNO =
        (
            Select MAX(SEQNO)
            From SPRADDR S2
            Where 
                S2.ID = S1.ID
                and ATYP_CODE = 'MA'
                and STATUS_IND is NULL
        )
) as MA_Address,

(Конец редактирования)

Любая помощь в следующем:

  1. Почему выбор 'RANK_NO = 1' вызвал бы такое снижение производительности? Это оператор WITH, Partition By или что-то еще?
  2. Есть ли лучший способ, чем использовать "Partition By" и затем выбрать RANK_NO = 1, чтобы получить номер телефона верхнего уровня?
  3. Предложения по улучшению вышеуказанного кода.
  4. Любые другие предложения.
Источник

1 ответ

В этом запросе трудной (дорогой) операцией является получение функции rank - row_number - для этого требуются (иногда не явные в запросе) сортировки.

  1. Вероятно, это потому, что когда вы не пишете RANK_NO = 1, вы не используете столбец RANK_NO, иначе Oracle не нужно его вычислять.

  2. вы можете заказать внутренний запрос и во внешних запросах использовать rownum, но у вас не может быть разделов. У вас есть один раздел, сигл 1, один 2 и так далее.

  3. Если вы сможете отфильтровать строки, которые вы получаете из таблицы SPRTELE, вы получите лучшую скорость. Несколько рядов -> лучшая скорость.
  4. Вы должны увидеть весь план объяснения, чтобы что-то улучшить. Разместите план объяснения запроса.
Источник
Другие вопросы по тегам