It is an interesting example and shows how much smarter the CBO is than the RBO. Reason 2134 to use the CBO.

consider this example:

create table t ( acc_nbr number, data char(255), constraint t_pk primary key(acc_nbr) );

insert into t
select rownum, 'x' from all_objects;

analyze table t compute statistics
for table
for all indexes
for all indexed columns;



alter session set sql_trace=true;
exec :n := null
select count(*)
from t n_is_null_cbo
where acc_nbr = nvl(:n,acc_nbr)
/
select /*+ RULE */ count(*)
from t n_is_null_rbo
where acc_nbr = nvl(:n,acc_nbr)
/
exec :n := 55
select count(*)
from t n_is_55_cbo
where acc_nbr = nvl(:n,acc_nbr)
/
select /*+ RULE */ count(*)
from t n_is_55_rbo
where acc_nbr = nvl(:n,acc_nbr)
/

Now, tkprof shows us:

select count(*) from t n_is_null_cbo where acc_nbr = nvl(:n,acc_nbr)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.05 0.04 0 45 4 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.05 0.04 0 45 4 1

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 SORT (AGGREGATE)
24098 CONCATENATION
24098 FILTER
24098 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF 'T_PK' (UNIQUE)
0 FILTER
0 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'T_PK' (UNIQUE)
********************************************************************************
select /*+ RULE */ count(*) from t n_is_null_rbo where acc_nbr = nvl(:n,acc_nbr)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.06 0.06 0 895 5 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.06 0.06 0 895 5 1

Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: 605 (OPS$TKYTE)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: HINT: RULE
0 SORT (AGGREGATE)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'T'
********************************************************************************
select count(*) from t n_is_55_cbo where acc_nbr = nvl(:n,acc_nbr)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.00 0 2 0 1

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 SORT (AGGREGATE)
1 CONCATENATION
0 FILTER
0 INDEX GOAL: ANALYZED (FAST FULL SCAN) OF 'T_PK' (UNIQUE)
1 FILTER
1 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'T_PK' (UNIQUE)
********************************************************************************
select /*+ RULE */ count(*) from t n_is_55_rbo where acc_nbr = nvl(:n,acc_nbr)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.05 0.05 0 895 5 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.05 0.05 0 895 5 1

Misses in library cache during parse: 0
Optimizer goal: RULE
Parsing user id: 605 (OPS$TKYTE)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: HINT: RULE
0 SORT (AGGREGATE)
0 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'T'

see -- the CBO did our work for us -- it rewrote the query effectively as:


select sum(cnt)
from ( select count(*) cnt from t where acc_nbr = :n
union all
select count(*) from t where :n is null )


and the query execution engine -- upon detecting the nullness (or not) of :n would execute only one or the other query in the union all. It realized that if :n is NULL -- acc_nbr = :n is never true -- don't even bother with that -- just index fast full scan and count. If :n is not null -- then the first query would run and the second would never go.....

The RBO on the other hand, it doesn't even realize that it can use the index at all for this query (or count(*)'s in general).



So, as long as you see a plan like that -- a single query is all you need. the optimizer has already in effect rewritten it as two queries for you.