Why test cases are good.
I received an email a few days ago that referenced my posting on the order of predicate evaluation. This was something I posted on 6th May 1999, and gave some test scripts for Oracle 7.3.4 and 8.0 to demonstrate that the order of predicate evaluation was bottom-up after all other considerations had been taken into account.
The document introduced the test cases with the comment:
My previous testing on Version 7 had indicated that the answer was NO. For version 7 Oracle works on the predicates from the bottom up. I had assumed from some recent experiences that the answer for Oracle 8 was also 'bottom up', but since the optimiser may have changed I thought I would check it properly.
And ended with the comment:
We conclude that Oracle 8.0, like Oracle 7, tests WHERE clause predicates from the bottom up when all other considerations have been assessed.
The email I received started with the statement:
I worked through the test case for the "Where and When" article ... it seems Oracle goes "top down" in 9i as opposed to "bottom up" in 8.
And followed with the interesting observation:
My question though is, for both versions, how can we prove the mechanism is deterministic? The conclusion that predicates [when all other considerations have been assessed] are processed in a certain order is based on empirical observations rather than an irrefutable proof. I'll agree though that, statistically, the conclusion seems to be true.
The observation in the email is correct – in fact the order changed with the cost based optimizer in 8i although, of course, the order for the rule-based optimizer is still bottom up, even in 9i and 10g.
When I got the email, I was very pleased that someone had actually simply taken the tests and re-run them. That point, by itself, demonstrated to me the importance of the test case. Things change. If you make a claim, without any justification, how do you discover that the world has moved on?
I just happen to have an old machine that was still running 7.3.4, so the first thing I did when I got the email was to re-run the test case – just to see if there was anything wrong with it – on 7.3.4, then 18.104.22.168, then 22.214.171.124. Sure enough, the 7.3.4 really was different from the others. (When something new happens, always go back to check the old – the original test may have been wrong for some reason).
But let’s consider the second point – what does the test prove?
I presented my conclusion as something a lot stronger than it really is – in fact I made the assumptions that the cost based optimizer was deterministic, and that the final evaluation order would either be bottom-up or top-down. Having demonstrated the order in these (and a few other, more complex) tests, I then produced the conclusion that the order was bottom-up (for 7.3 and 8.0).
But what I have isn’t really a proof – it’s only a good test case waiting to be turned over. Maybe the conclusion is true when all the predicates include a call to a user-defined function. Maybe the order is dictated by the alphabetic sorting of function names if I had two predicates that called different user-defined functions. Maybe the order would be dicateted by the relative number of parameters in each function if I had two different user-defined functions.
But until someone needs to understand why a more complex case does something “funny” – my test case, and the refinements I haven’t included in the article – are all I need now, and a good basis for investigating if something goes wrong in the future.
The person who sent me the email about the change in 9i subsequently sent me an email saying:
I have a question regarding a similar test case for 9i and WHERE_TEST as IOT (PK on col1) ... tkprof shows INDEX UNIQUE SCAN on the table, which is OK ...but it shows 4 passes through DUAL ... I was expecting just 3: one for the col1 predicate to get the unique row, on for the predicate on col2 to assert that condition and one more for the function call in the select list.
This is brilliant, and something that happens to me quite frequently. I set up a test case for one reason, and just happen to come across some a completely unexpected side-effect that tells me something else about how Oracle works.
In this case, the ‘spare’ call appears because Oracle generates a start key and stop key for any index scan (including a unique scan), so had to call the function twice. If you switch to 9i and generate a full execution plan, you can see the plan_table column called access_predicates which shows you the predicates that Oracle has used to dictate the boundaries of the index range scan. It is the access_predicates that has to be evaluated twice – once for the start key, once for the stop key.