左外连接的条件谓词评估较晚，导致性能问题。 Oracle 8i

Question

This is on Oracle 8i (sorry, no control over this) and PHP 7.

I am building a search tool. It's a simple form with 3 fields, using HTTP Post method. PHP then does some checks on the 3 fields' values, determines if they are valid and then sends the values off to the SQL query. The query looks something like this; remember its 8i so no ANSI join here :

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

The user is required to enter at least a Report Number, or a Case Number. The full numbers are required, i.e. no wildcard search is allowed. The reports_table is very large. When searching by Report Number, the database takes a very long time as if the CASE evaluation that acts on the validity of the Report Number, i.e. this section of code here

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

is being evaluated after the joining operation. It seems to be indeed evaluated though, because if I add another simple predicate in the WHERE clause to limit the scope on the Report Number, the database answers very fast, with the expected result. e.g. lets say the Report Number I'm searching for is 'R123456', if I add AND reports_table.report_no LIKE 'R1234%', as a predicate outside of the CASE statement, the performance is good. Otherwise it is extremely slow, as if Oracle is scanning the whole reports_table in trying to make the join.

I would like to find a way to tell Oracle to ensure its looking at the conditional CASE filter on the Report Number when performing the join but I have no idea how. Or maybe I should avoid altogether that kind of conditional restriction on the join, and if so, what technique could I use to achieve what I'm trying to do?

Answer 1

It's been awhile since I've had to work with this: and a total shot in the dark... Does re-arranging the limits so they are imposed before the outer join work?

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)

I know in ANSI that if I use an AND in the where on a table using an outer join, My outer join behaves like an inner. I'm wondering if by re-arranging if the engine will optimize better; or if an actual SQL hint will be needed.