Will a SELECT . . . INTO use short-circuit evaluation for a COALESCE?

Question

I’m building a stored proc that can take in two separate values that each can be used to identify a single record. One of these values may be null.

My logic is as follows: if IN_ORDER_ID is not NULL, save it to V_ORDER_ID. If it is NULL, then retrieve ORDER_ID with a query and save that to V_ORDER_ID. V_ORDER_ID is then used in the rest of the procedure.

Here is the code:

SELECT COALESCE(IN_ORDER_ID, order_id) INTO V_ORDER_ID FROM SCHEMA.ORDER WHERE SXC_ORDER_NUMBER = IN_SXC_ORDER_NUMBER;

My Question: Will the coalesce in this query still take advantage of short-circuit evaluation?

asked 2016-08-02 by bj13

---

Answer

The question you ask is:

“Will the coalesce in this query still take advantage of short-circuit
evaluation?”

Within the context of the COALESCE expression, the answer to this is yes. Oracle will evaluate IN_ORDER_ID, and if it is not null, does not need to evaluate the column value of order_id.

However, I believe the question you intend to ask is:

“Will short-circuit evaluation mean the statement does not have to access the
SCHEMA.ORDER table?”

Then the answer is ABSOLUTELY NOT.

Table access must occur to evaluate the WHERE clause to identify how many rows to return. For example, if IN_ORDER_ID is not null, but no rows match on SXC_ORDER_NUMBER = IN_SXC_ORDER_NUMBER, then no rows will be returned by the query, and V_ORDER_ID will be NULL.

Consider the behavior from this version of your query:

SELECT COALESCE(IN_ORDER_ID, order_id) INTO V_ORDER_ID 
FROM SCHEMA.ORDER 
WHERE 1=2; --Where no rows match
--
SELECT V_ORDER_ID FROM dual;

Is this the intended behavior when no rows match the WHERE clause?

A different approach, with different behavior would be to use an approach like @SlimsGhost used in his answer, or to use IFcontrol flow to do variable assignment:

IF IN_ORDER_ID IS NOT NULL
    THEN V_ORDER_IF := IN_ORDER_ID;
ELSE
    SELECT order_id INTO V_ORDER_ID 
    FROM SCHEMA.ORDER 
    WHERE SXC_ORDER_NUMBER = IN_SXC_ORDER_NUMBER;
ENDIF;
--
SELECT V_ORDER_ID FROM dual;

These alternate approaches avoid accessing SCHEMA.ORDER if IN_ORDER_ID is not null. As a result, the V_ORDER_ID assignment happens regardless of whether the WHERE clause of the query is satisfied.

answered 2016-08-03 by Andy Mallon