conditional join (Oracle)-CodePudding

We have front-end app where user enters customer and/or PO to retrieve data. For instance, if user want to retrieve all POs for a customer, he will enter '%' in PO field. If user wants to retrieve all data, he will enter '%' in every field. I am trying this, but it does not work

SELECT *
FROM PO_INFO
WHERE customer_id = case when '%' then customer_id else 'Macys' end
  AND purchase_order = case when '%' then purchase_order else '79124' end

What am I missing?

CodePudding user response：

You should not (some would say must not) just plug the user-entered search values into your SQL query as literals. That is,

AND purchase_order = case when '%' then purchase_order else '79124' end

... is not going to perform or scale well because every single search looks to Oracle like a brand new SQL query that has to get parsed and optimized (i.e., "hard parsed). This is an expensive process that also requires a lot of latches, meaning multiple users trying to run searches at the same time will have to wait for each other.

Instead, you should construct your SQL using bind variables. So,

AND purchase_order = :1 -- or something.  We'll discuss the exact logic later...

That :1 is a bind variable, a placeholder for a value your code will supply when it submits the query. Now, everyone doing a search is using the same query (just each supplying different values for the bind variables). No more excessive hard parsing and performance disaster averted.

But, here is the next problem. One query for all means it only gets hard parse one time (good) but it also means everyone runs using the same execution plan. But in a search like this, one size does not fit all. Suppose the execution plan Oracle comes up with uses an index on column 'ABC'. If a user does not supply a bind variable value for column 'ABC', that execution plan will still be followed, but with terrible results.

So, what we want really is one SQL for each set of bind variables that have values or don't, but not one SQL for each distinct set of literal search values.

Build your SQL in code by starting with this string:

SELECT * FROM PO_INFO WHERE 1=1

Then, for each search condition add this (if the value is %)

AND (:1 IS NULL)    -- and pass `NULL`, not "%" as the value for :1

(Aside: the reason for this condition, which is essentially NULL IS NULL is to make to so the number and order of the bind variables that have to be passed in is always the same, regardless of what the end user does or does not give you a value for. This makes it much easier to submit the SQL in some languages, like PL/SQL).

If the search condition is not %, add this:

AND (customer_id /* or whatever column */ = :1)  -- and pass the user-entered value

So, for example, if the user specified values for customer_id and po_date but not purchase_order, your final SQL might look like:

SELECT *
FROM PO_INFO
WHERE 1=1
  AND  customer_id = :1
  AND  po_date := 2
  AND :3 IS NULL  -- this is purchase order and :3 you will pass as null

If you do all this, you'll get the least amount of hard-parsing and the best execution plan for each search.