Oracle Scratchpad

October 31, 2010

Conditional SQL – 3

Filed under: CBO,Conditional SQL,Execution plans,Tuning — Jonathan Lewis @ 5:54 pm BST Oct 31,2010

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.

[Further reading on Conditional SQL]

3 Comments »

  1. Hello Jonathan ,

    It was mentioned in Oracle 8i manual ( the concept of predicate collapsing) .
    I was of the same thinking as yours about the above statement .

    And I asked the follow up question to the similar posting in ask tom site
    ( http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2689943800346359019#2691231100346040851).

    Per Tom , this is no longer the case . Please advise , if I misunderstood the concept or nvl expansion .

    Cheers.

    Comment by Zahir Mohideen — November 1, 2010 @ 1:16 am BST Nov 1,2010 | Reply

    • Zahir,

      I am sure Jonathan will explain it much better but let me see what I can do :)
      I think what Tom has mentioned in his reply is that 8i was the last version where oracle ALWAYS generated a plan containing a single access path (full table scan) for these type of queries. Starting from 9i onwards, oracle is capable to generate a plan that can have two access paths and either of those will be chosen at run time. If you read Jonathan’s post (linked above), it explains this in detail (and also tells that the plan swiches back to full table scan when you have more than one sunch predicates in WHERE clause.)

      p.s. Apologies Jonathan for jumping in your question.

      Comment by Narendra — November 1, 2010 @ 4:21 am BST Nov 1,2010 | Reply

    • Zahir,
      I think Narenda has probably answered your question. A minor detail, though: the concatenation path is no available for “:bind is null or col = :bind”, it’s only available for the nvl() and a specific case of decode().

      Technically, then, it’s possible that the code I supplied could have been modified to use the nvl() mechanism rather than being rewritten with the UNION ALL mechanism – but it’s not always appropriate thanks to the issues of NULLs that I have pointed out elsewhere.

      Comment by Jonathan Lewis — November 4, 2010 @ 7:48 am BST Nov 4,2010 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,910 other followers