Thanks for the question, John.

Asked: May 12, 2016 - 1:46 pm UTC

Last updated: May 14, 2016 - 3:32 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi,
We have a query which may be restricted based on a bind variable. If the bind is null then we want to return all rows. To achieve this we used:

And table.value = Nvl(:Bind,table.value)

However we noticed that the plan generated was poor due to bad cardinality estimates. Changing this to :

And (table.value = :Bind or :Bind Is Null)

resulted in a better plan (in our situation anyway) - the Nvl example seemed to use a 1% cardnality. Now I am aware that bind variable peeking can cause a whole host of issues here, however I am confused as to why the two differ when the bind is null?

create table xxjktest as (select mod(level,10) n from dual connect by level < 100000);

exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'XXJKTEST');

explain plan for 
select * from xxjktest where n = :val or :val is null;
select * from table(dbms_xplan.display());



Plan hash value: 3644865739

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 14500 | 43500 |    49   (7)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| XXJKTEST | 14500 | 43500 |    49   (7)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(:VAL IS NULL OR "N"=TO_NUMBER(:VAL))


explain plan for 
select * from xxjktest where n = nvl(:val,n);
select * from table(dbms_xplan.display());

 Plan hash value: 3644865739
  
 ------------------------------------------------------------------------------
 | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
 ------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT  |          | 10000 | 30000 |    50   (8)| 00:00:01 |
 |*  1 |  TABLE ACCESS FULL| XXJKTEST | 10000 | 30000 |    50   (8)| 00:00:01 |
 ------------------------------------------------------------------------------
  
 Predicate Information (identified by operation id):
 ---------------------------------------------------
  
    1 - filter("N"=NVL(:VAL,"N"))

and Chris said...

With the OR condition and your plan, the optimzier is:

- Estimating 10% for n = :val (1/distinct values for n = 1/10)
- Estimating 5% for :val is null

It then uses standard OR probability maths which state that

  P(A OR B) = P(A) + P(B) - P(A AND B)
            = P(A) + P(B) - (P(A) * P(B))
            = 0.1 + 0.05 - (0.1 * 0.05)
            = 0.145

Multiply this up by the number of rows (99,999) and you get 14,500 (rounding up).

But if you use NVL, Oracle can recognize that n = nvl(:val, n) is true if :val is null. i.e. it returns all rows. So it only needs to use:

  n = :val

For its estimate. As stated above, there's 10 values, so it uses 1/10 = 0.1. Multiplied by 99,999 rows = 10,000 (rounding up).

In general optional bind variables cause the optimizer problems. Instead of using nvl or OR, I recommend using dynamic SQL with the approach Tom outlines at:

http://www.oracle.com/technetwork/issue-archive/2009/09-jul/o49asktom-090487.html

This should give better estimates and ultimately better plans.

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you

John, May 12, 2016 - 7:45 pm UTC

Great explanation, thank you very much. At first I wondered why in Tom's example he didn't just omit the clause altogether when the value wasn't passed in a variable, however I see now that would require either more complexity in the package to bind only the variables we are interested in when opening the cursor, or constructing the actual values in the string, which would then result in far more hard parses as any change in parameter value would be a new statement.
Back to my example, I didn't realise the optimizer would do a bit of "up front" optimization for Nvl but not for an OR. I'll look into using the technique described above.

Many thanks
John

Union all

Rajeshwaran, Jeyabal, May 13, 2016 - 2:50 pm UTC

Other option to be conditionally branch them using IS NULL and IS NOT NULL option, at run time optimizer will pick up the right portion of UNION ALL branch based on presence or absence of bind values.

if :x is null then FTS else Index scan.

rajesh@ORA11G> create table t as select * from all_objects;

Table created.

rajesh@ORA11G> create unique index t_idx on t(object_id);

Index created.

rajesh@ORA11G> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

rajesh@ORA11G> variable x number
rajesh@ORA11G> exec :x := 2;

PL/SQL procedure successfully completed.

rajesh@ORA11G> set autotrace traceonly explain
rajesh@ORA11G> select owner
  2  from t
  3  where :x is not null
  4  and object_id = :x
  5  union all
  6  select owner
  7  from t
  8  where :x is null ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3707600866

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       | 84661 |   496K|   631   (2)| 00:00:01 |
|   1 |  UNION-ALL                    |       |       |       |            |          |
|*  2 |   FILTER                      |       |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T     |     1 |    11 |     2   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | T_IDX |     1 |       |     1   (0)| 00:00:01 |
|*  5 |   FILTER                      |       |       |       |            |          |
|   6 |    TABLE ACCESS FULL          | T     | 84660 |   496K|   629   (2)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(:X IS NOT NULL)
   4 - access("OBJECT_ID"=TO_NUMBER(:X))
   5 - filter(:X IS NULL)

rajesh@ORA11G> set autotrace off
rajesh@ORA11G>

NULLABLE columns.

Rajeshwaran, Jeyabal, May 13, 2016 - 2:59 pm UTC

If the bind is null then we want to return all rows. To achieve this we used:
And table.value = Nvl(:Bind,table.value)

if "table.value" column is a NULLABLE column, then the above condition doesn't return "all" rows.

rajesh@ORA11G> set feedback off
rajesh@ORA11G> drop table t purge;
rajesh@ORA11G> create table t(x int,y int);
rajesh@ORA11G> insert into t values(1,1);
rajesh@ORA11G> insert into t values(2,NULL);
rajesh@ORA11G> commit;
rajesh@ORA11G> exec dbms_stats.gather_table_stats(user,'T');
rajesh@ORA11G> set feedback on
rajesh@ORA11G>
rajesh@ORA11G> select * from t;

         X          Y
---------- ----------
         1          1
         2

2 rows selected.

rajesh@ORA11G> set linesize 71
rajesh@ORA11G> desc t
 Name                                Null?    Type
 ----------------------------------- -------- -------------------------
 X                                            NUMBER(38)
 Y                                            NUMBER(38)

rajesh@ORA11G>
rajesh@ORA11G> set linesize 180
rajesh@ORA11G> set serveroutput off
rajesh@ORA11G> variable b1 number
rajesh@ORA11G> select * from t where y = nvl(:b1,y);

         X          Y
---------- ----------
         1          1

1 row selected.

rajesh@ORA11G> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID  cp2n1vgbyz0m9, child number 0
-------------------------------------
select * from t where y = nvl(:b1,y)

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     4 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |     5 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("Y"=NVL(:B1,"Y"))


18 rows selected.

rajesh@ORA11G>

In this case :b1 is null so the condition becomes Y=Y, Hence supressing the NULL values on column Y from output.

May 14, 2016 - 3:32 am UTC

Just for completeness, you can use DECODE for the other variant, namely, if the bind is null, you want to find the null values in the column as well.

SQL> create table t ( x int );

Table created.

SQL> insert into t values (1);

1 row created.

SQL> insert into t values (2);

1 row created.

SQL> insert into t values (null);

1 row created.

SQL>
SQL> variable n number
SQL> exec :n := 1

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t
  2  where decode(x,:n,1,0) = 1;

         X
----------
         1

SQL>
SQL> exec :n := null

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t
  2  where decode(x,:n,1,0) = 1;

         X
----------

Nullable

A reader, May 15, 2016 - 9:34 pm UTC

Hi,
Yes, in our example the column in not nullable.

Thanks for the feedback
John

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thank you

Union all

NULLABLE columns.

Nullable

More to Explore

Performance

Nvl vs equality or bind is null - different cardinality estimates

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Thank you

Union all

NULLABLE columns.

Nullable

More to Explore

Performance