Conditional SQL (2)

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.

I may come back to joins at some other time (the mechanism does appear, but the ramifications are convoluted). Let’s just stick with the single table query for the moment.

In the following test case, I create a table indexed on a pair of columns. I’ve indexed the columns in both possible orders (n1, n2) and (n2, n1). After gathering statistics I run a query that uses the offending nvl() construct. The execution plan comes from 10gR2 autotrace with CPU costing disabled:

rem
rem     Script:         null_plan.sql
rem     Author:         Jonathan Lewis
rem     Dated:          February 2003
rem

create table t1
as
with generator as (
	select	--+ materialize
		rownum 	id
	from	all_objects
	where	rownum <= 3000
)
select
	mod(rownum,1000)	n1,
	mod(rownum,400)		n2,
	rownum			id,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 20000
;      

create index t1_i1 on t1(n1, n2);
create index t1_i2 on t1(n2, n1);      

-- gather statistics before running the query below      

variable b1 number
variable b2 number      

exec :b1 := 25; :b2 := 25      

select
	small_vc
from
	t1
where
	n1 = nvl(:b1, n1)
and	n2 = nvl(:b2, n2)
;      

/*
Execution Plan
----------------------------------------------------------
Plan hash value: 3469439717     

-----------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |    51 |   969 |    59 |
|   1 |  CONCATENATION                |       |       |       |       |
|*  2 |   FILTER                      |       |       |       |       |
|*  3 |    TABLE ACCESS FULL          | T1    |    50 |   950 |    56 |
|*  4 |   FILTER                      |       |       |       |       |
|   5 |    TABLE ACCESS BY INDEX ROWID| T1    |     1 |    19 |     3 |
|*  6 |     INDEX RANGE SCAN          | T1_I1 |     1 |       |     2 |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:B1 IS NULL)
   3 - filter("N2"=NVL(:B2,"N2") AND "N1" IS NOT NULL)
   4 - filter(:B1 IS NOT NULL)
   6 - access("N1"=:B1)
       filter("N2"=NVL(:B2,"N2"))
*/

As you can see, the split and concatenation occurs just once. If the optimizer tried to cover more options, it would double the number of sub-plans that had to be examined every time it introduced one more split – the potential optimisation cost could become very large if it tried to get really clever with these nvl() predicates.

In this example column n1 was used to dictate the split and, as far as I can tell, the rationale for choosing the n1 column was that it had a better selectivity than n2 (there are 1,000 distinct values for n1 but only 400 distinct values for n2). Given this choice of split, Oracle was able to use the index on (n1, n2) on one of the branches of the plan.

So what happens if you drop the index (n1, n2) ? The optimizer still splits the query on the n1 predicate, and does a full tablescan for both parts of the query. It doesn’t seem to consider the cost of operating the two halves of the query and use the n2 predicate as the basis for the split instead – despite the “obvious” benefit of doing so.

One of the often-quoted fairy-tales about indexes was the directive to “put the most selective column first”. It was never a sensible rule of thumb (except, possibly, prior to version 6.0). But if you have a lot of code that uses this nvl() construct then there may actually be a reasonable argument for adopting this approach.  (There’s a better argument for fixing the code, of course).

[Further reading on Conditional SQL]