Conditional SQL – 3

I’ve spent the last week in Spain – sightseeing, rather than working – with a minimum amount of access to the Internet.

Inevitably I now have to work hard to catch up with my email. As a moment of light relief in an otherwise irritating chore I thought I’d respond to an emailed request for help. (Regular readers of the blog will know that I don’t usually respond to private email requests for solutions, but sometimes someone gets lucky.)

The question was basically this:  why do I get different execution plans for the following two statements:

select
        DISTINCT CLIENT_DISTRIBUTION_ID, BANK_SOURCE_SYSTEM_ID, CAS_CLIENT_ID,
        CAS_DEBT_ACCOUNT_ID, DESTINATION_ACCOUNT_NUMBER, DESTINATION_SORT_CODE, AMOUNT
FROM
        TRANSFERS_TO_BE_ISSUED
WHERE   CHARGING_SYSTEM_ID = 1
and     ( 1306290 IS NULL OR CLIENT_DISTRIBUTION_BATCH_ID = 1306290)

select
        DISTINCT CLIENT_DISTRIBUTION_ID, BANK_SOURCE_SYSTEM_ID, CAS_CLIENT_ID,
        CAS_DEBT_ACCOUNT_ID, DESTINATION_ACCOUNT_NUMBER, DESTINATION_SORT_CODE,
        AMOUNT
FROM
        TRANSFERS_TO_BE_ISSUED
WHERE   CHARGING_SYSTEM_ID =:Z2
and     ( :Z1 IS NULL OR CLIENT_DISTRIBUTION_BATCH_ID = :Z1 )

You’ll notice that the only difference between the two queries is the change from literals to bind variables. In fact the question goes on to point out that if the values used for the bind variables match the literal version, or even if the literal version is executed with parameter cursor_sharing set to force, the plan you get doesn’t match the plan from the literal code.

The explanation for this behaviour is related to an old posting about conditional SQL. I haven’t given you an explicit statement of the intent of this piece of SQL – but it’s basically an example of the form: “if I don’t supply a value I want everything, otherwise I want the rows associated with the value”.

In the example using literals the optimizer can see that the condition “1306290 IS NULL” is always false and (because of the or) reduce the where clause to: “CHARGING_SYSTEM_ID = 1 and CLIENT_DISTRIBUTION_BATCH_ID = 1306290″.

If you rewrite the query (whether explicitly or by fiddling with cursor_sharing) to use bind variables, it doesn’t matter to the optimizer that on its first call it can see an incoming value that is not null it’s possible that the next time you call the query you will supply a null for the bind variable Z1; so the optimizer has to create a plan that can get the right answer whether or not the bind variable is null – which can easily result in the need for a different execution path.

If you want to write this code to be as efficient as it can be in all cases, you probably need to write something like:

select
        DISTINCT CLIENT_DISTRIBUTION_ID, BANK_SOURCE_SYSTEM_ID, CAS_CLIENT_ID,
        CAS_DEBT_ACCOUNT_ID, DESTINATION_ACCOUNT_NUMBER, DESTINATION_SORT_CODE, AMOUNT
FROM
        TRANSFERS_TO_BE_ISSUED
WHERE   CHARGING_SYSTEM_ID = :Z2
and     :Z1 IS NULL
UNION ALL
select
        DISTINCT CLIENT_DISTRIBUTION_ID, BANK_SOURCE_SYSTEM_ID, CAS_CLIENT_ID,
        CAS_DEBT_ACCOUNT_ID, DESTINATION_ACCOUNT_NUMBER, DESTINATION_SORT_CODE, AMOUNT
FROM
        TRANSFERS_TO_BE_ISSUED
WHERE   CHARGING_SYSTEM_ID = :Z2
and     :Z1 IS NOT NULL
and     CLIENT_DISTRIBUTION_BATCH_ID = :Z1

Basically you’ve got to give the optimizer two queries – of which only one will execute at run time. (And if you want your code to play towards the strengths of the database you’ll put the choice into the front-end code as an “if .. then .. else” test, rather than hiding the choice in a UNION ALL.

Now back to the email.