This is one of those posts where the investigation is left as an exercise – it’s not difficult, just something that will take a little time that I don’t have, and just might end up with me chasing half a dozen variations (so I’d rather not get sucked into looking too closely). It comes from an OTN question which ends up reporting this predicate:
WHERE ( LENGTH ( :b7) IS NULL OR
UPPER (TRIM (CODFSC)) = UPPER (TRIM ( :b8)) or
UPPER (TRIM (CODUIC)) = UPPER (TRIM ( :b9)))
AND STATE = 0;
The three bind variables all hold the same value; there is a function-based index on upper(trim(codfsc)), and another on upper(trim(coduic)). The execution plan for this query is a full tablescan, but if you eliminate the first predicate Oracle can do a concatenation of two index range scans. This variation doesn’t surprise me, the optimizer’s ability to introduce concatenation is limited; however, I did wonder whether some small variation in the SQL would allow the optimizer to get just a little more clever.
Would you get concatenation if you changed the first predicate to (:b7 is null); if not, would a similar query that didn’t depend on function-based indexes do concatenation; if not is there any rewrite of this query that could do a tablescan ONLY for the case where :b7 was null ?
Demonstrations of any levels of success can be left in the comments if anyone’s interested. To get a fixed font that preserves space start the code with “sourcecode” and end with “/sourcecode” (removing the quotation marks and replacing them with square brackets).
Here’s a little detail about how the optimizer can handle the nvl() function that I hadn’t noticed before (and it goes back to at least 8i). This is running on 22.214.171.124, and table t1 is just all_objects where rownum <= 20000:
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:
Some time ago I wrote a note on conditional SQL, commenting on code with predicates like:
where t1.n1 = nvl(:n1,t1.n1)
In that article, I showed how Oracle can transform a query of this type using concatenation so that at run time one of two possible execution paths can be taken. But, as often happens, a simple example leads to more complex questions.
What happens if you have several similar predicates in your query – does Oracle keep doubling the number of possible execution paths for the query: if so, how far does it go, if not are there any side effects to consider? What happens with more complex queries, including joins.
Here’s a simplified example of the type of SQL that I often used to see coming from screen generators:
where t1.n1 = nvl(:n1,t1.n1)
This code is basically trying to say: “if the user supplies an input value for some on-screen field then return data for that value, otherwise return all the data unconditionally.”