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 point that’s probably fairly well-known, but worth repeating – nvl() and coalesce() aren’t identical in behaviour but you may have some cases where you’re using nvl() when coalesce() would be a more efficient option.
The reason for this is “short-circuiting”. The expression nvl(expr1, expr2) will return expr2 if expr1 is null, otherwise it will return expr1; the expression coalesce(expr1, expr2, …, exprN) will return the first non-null expression in the list so, in particular, coalesce(expr1, expr2) will give the same result as nvl(expr1, expr2) ; the big difference is that nvl() will evaluate both expressions, while coalesce will evaluate expr2 only if it needs to (i.e. only if expr1 evaluates to null). In many cases the difference in performance will be insignificant – but consider the following type of construct (t1 is a table with a single, numeric, column n1 and a single row):
create table t1 as select 1 n1 from dual; execute dbms_stats.gather_table_stats(user,'t1') select nvl(n1, (select max(object_id) from all_objects)) nn from t1 ; select coalesce(n1, (select max(object_id) from all_objects)) nn from t1 ;
In the first query Oracle will execute the inline scalar subquery whether n1 is null or not.
In the second query Oracle will execute the inline scalar subquery only if n1 is null.
I know which option I would prefer to use if I knew that n1 could be null.
There is a trap that you have to watch out for – try recreating t1 with n1 defined as a varchar2() column and the query with coalesce() will fail with Oracle error ORA-00932: inconsistent datatypes: expected CHAR got NUMBER the expressions that appear in the coalesce() must all be explicitly of the same type while nvl() will do implicit conversions when necessary, so be a little careful with the code when you’re looking for opportunities to make the change.
Note: the same difference (strictness of typing) appears when you compare decode() – which does implicit conversion when necessary – with case end – which does not. (Both case and decode() short-circuit, though).
If it’s a long night, and you’ve got nothing better to do waiting for the new year, then here’s some reading to keep you entertained. A collation of Tanel Poder’s trouble-shooting blog postings, dating from June 2007 to September 2013:
- When the wait interface is not enough
- No magic is needed, systematic approach will do
- More adventures in the process stack
- Diagnosing a long parsing issue
- Sampling v$ stuff with waitprof really fast using SQL
- Understanding execution plans with OS explain
- Sampling latch holder statistics using latchprof
- Even more details latch troubleshooting using latchprofx
- Process stack profiling from SQL Plus using ostackprof
- Index unique scan doing multiblock reads
- Complex wait chain signature analysis with ash_wait_chains sql
Here’s a question that I’ve had on my todo (and draft posts) list for a few years – so I’m presenting it as a task for anyone who can demonstrate the answer.
If you’ve got a pl/sql cursor open and you’re using the “update current of” syntax, what happens if you update the same row twice but the row comes from a partitioned table and moves to a new partition on the first update ?
If you have a demo that you want to include in the comments then start with “sourcecode” and end with “/sourcecode” – in square brackets, without the quotation marks – to get a fixed font format and space preservation. If you have a good demonstration or reference article that you can link to, a simple URL will do nicely.#