Условный предикат в левом внешнем соединении оценивается поздно, что вызывает проблемы с производительностью Oracle 8i

Question

Это на Oracle 8i (извините, нет контроля над этим) и PHP 7.

Я строю инструмент поиска. Это простая форма с 3 полями, использующая метод HTTP Post. Затем PHP выполняет некоторые проверки значений 3 полей, определяет, действительны ли они, а затем отправляет значения в запрос SQL. Запрос выглядит примерно так; помните его 8i, так что здесь нет ANSI:

SELECT
reports_table.*, documents_table.*, cases_table.*

FROM
reports_table, documents_table, cases_table

WHERE
reports_table.report_id = documents_table.report_id
AND reports_table.report_id = cases_table.report_id(+)

-- Report Number filtering

    AND reports_table.report_no = 
        CASE
            WHEN $report_no_isvalid = 1
            THEN '$report_no'       -- Oracle expects datatype varchar2
            ELSE reports_table.report_no
        END

-- Document Number filtering

    AND documents_table.document_no = 
        CASE
            WHEN $doc_no_isvalid = 1
            THEN $doc_no        -- Oracle expects datatype number
            ELSE documents_table.document_no
        END

-- Case Number filtering

    AND cases_table.case_no = 
        CASE
            WHEN $case_no_isvalid = 1
            THEN '$case_no'     -- Oracle expects datatype varchar2
            ELSE cases_table.case_no
        END

Пользователь должен ввести хотя бы номер отчета или номер дела. Полные числа обязательны, т.е. поиск по шаблону не разрешен. reports_table очень большой. При поиске по номеру отчета база данных занимает очень много времени, как будто оценка CASE, которая влияет на достоверность номера отчета, то есть этот раздел кода здесь

AND reports_table.report_no = 
    CASE
        WHEN $report_no_isvalid = 1
        THEN '$report_no'       -- Oracle expects datatype varchar2
        ELSE reports_table.report_no
    END

оценивается после операции соединения. Это, кажется, действительно оценивается, потому что, если я добавлю еще один простой предикат в предложении WHERE, чтобы ограничить область действия для номера отчета, база данных ответит очень быстро, с ожидаемым результатом. например, допустим, что номер отчета, который я ищу, это "R123456", если я добавлю AND reports_table.report_no LIKE 'R1234%'как предикат вне оператора CASE, производительность хорошая. В противном случае это очень медленно, как будто Oracle сканирует весь reports_table в попытке сделать соединение.

Я хотел бы найти способ сообщить Oracle, чтобы он смотрел на условный фильтр CASE по номеру отчета при выполнении объединения, но я не знаю, как это сделать. Или, может быть, мне следует вообще избегать такого рода условных ограничений на объединение, и если да, то какую технику я могу использовать для достижения того, что я пытаюсь сделать?

Answer 1

Прошло некоторое время с тех пор, как мне пришлось поработать с этим: и полный выстрел в темноте... Перераспределяет ли ограничения так, чтобы они налагались до внешнего соединения?

WHERE
(reports_table.report_id = documents_table.report_id
-- Report Number filtering

    AND reports_table.report_no = 
        CASE
            WHEN $report_no_isvalid = 1
            THEN '$report_no'       -- Oracle expects datatype varchar2
            ELSE reports_table.report_no
        END
-- Document Number filtering
    AND documents_table.document_no = 
        CASE
            WHEN $doc_no_isvalid = 1
            THEN $doc_no        -- Oracle expects datatype number
            ELSE documents_table.document_no
        END)
AND 
(reports_table.report_id = cases_table.report_id(+)
-- Case Number filtering
    AND cases_table.case_no = 
        CASE
            WHEN $case_no_isvalid = 1
            THEN '$case_no'     -- Oracle expects datatype varchar2
            ELSE cases_table.case_no
        END)

В ANSI я знаю, что если я использую AND в таблице where с помощью внешнего соединения, то мое внешнее соединение ведет себя как внутреннее. Я задаюсь вопросом, если перестроить, если двигатель будет оптимизировать лучше; или если понадобится реальная подсказка SQL.