Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Emilio.

Asked: April 07, 2008 - 8:52 am UTC

Last updated: November 09, 2012 - 6:39 am UTC

Version: 9.2.0

Viewed 10K+ times! This question is

You Asked

I Have this query:

SQL> create table a (col1 varchar2(10));

Table created.

SQL> insert into a values('xxx');

1 row created.

SQL> create index ax on a(col1);

Index created.

SQL> select * from a where col1='xxx';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (RANGE SCAN) OF 'AX' (NON-UNIQUE)

and this query:

SQL> var b1 varchar2;

SQL> exec :b1:= null ;

PL/SQL procedure successfully completed.

SQL> select * from a where col1=nvl(:b1,col1);

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'A'

The second one does not use the index.

But if I change the value of :b1:

SQL> exec :b1:= 'xxx';

PL/SQL procedure successfully completed.

SQL> select * from a where col1=nvl(:b1,col1);


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'A'


It still not use the index. There is a way to rewrite the query in order to use the index? Also, it is possible rewrite the query using CASE, WHEN THEN? It is useful for index usage?

Thanks.

and Tom said...

use the CBO


ops$tkyte%ORA9IR2> create table t ( x varchar2(10), y varchar2(10));

Table created.

ops$tkyte%ORA9IR2> create index t_idx on t(x);

Index created.

ops$tkyte%ORA9IR2> insert into t values ( 'x','y' );

1 row created.

ops$tkyte%ORA9IR2> exec dbms_stats.set_table_stats( user, 'T', numrows => 100000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> variable x varchar2(10)
ops$tkyte%ORA9IR2> @at
ops$tkyte%ORA9IR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA9IR2> set autotrace traceonly explain
ops$tkyte%ORA9IR2> select * from t where x = nvl(:x,x);

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=13 Card=6000 Bytes=84000)
   1    0   CONCATENATION
   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1000 Bytes=14000)
   4    1     FILTER
   5    4       TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1000 Bytes=14000)
   6    5         INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=400)



ops$tkyte%ORA9IR2> set autotrace off
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> @trace
ops$tkyte%ORA9IR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte%ORA9IR2> exec :x := null

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> select * from t x_is_null where x = nvl(:x,x);

X          Y
---------- ----------
x          y

ops$tkyte%ORA9IR2> exec :x := 'y'

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> select * from t x_is_NOT_null where x = nvl(:x,x);

no rows selected


it does this neat thing calling "NVL OR EXPANSION". In this case, it will either full scan (when :x is null) or index range scan (when :x is not null)


select * from t x_is_null where x = nvl(:x,x)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  CONCATENATION  (cr=7 r=0 w=0 time=81 us)
      1   FILTER  (cr=7 r=0 w=0 time=72 us)
      1    TABLE ACCESS FULL T (cr=7 r=0 w=0 time=57 us)
      0   FILTER  (cr=0 r=0 w=0 time=1 us)
      0    TABLE ACCESS BY INDEX ROWID T
      0     INDEX RANGE SCAN T_IDX (object id 34021)
********************************************************************************
select * from t x_is_NOT_null where x = nvl(:x,x)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  CONCATENATION  (cr=1 r=0 w=0 time=44 us)
      0   FILTER  (cr=0 r=0 w=0 time=1 us)
      0    TABLE ACCESS FULL T
      0   FILTER  (cr=1 r=0 w=0 time=31 us)
      0    TABLE ACCESS BY INDEX ROWID T (cr=1 r=0 w=0 time=30 us)
      0     INDEX RANGE SCAN T_IDX (cr=1 r=0 w=0 time=27 us)(object id 34021)


see the CR= values in the above, when X is null - the full scan ran, but the index was *not touched*.

when x is not null, the index range scan happened - and the table *was not full scanned*

Rating

  (3 ratings)

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

Comments

Good one

Rupesh, November 09, 2012 - 3:50 am UTC

Please correct my understanding after reading this article..

Using NVL with column clause in queries "where the column in the context acts as Index but is valid to have NULL'"s will give better results.
Tom Kyte
November 09, 2012 - 6:39 am UTC

I could not parse your sentence, sorry I don't know what you were trying to say.



This article was about NVL-or expansion. If you have a predicate:

where database_column = nvl(:bind_variable, database_column)


we will rewrite it as


where (database_column is not null and :bind_variable is null)
   OR (:bind_variable is not null and database_column = :bind_variable)


you can see that in dbms_xplan output:

ops$tkyte%ORA11GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA11GR2> alter table t add constraint t_pk primary key(object_id);

Table altered.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable x number
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from t where object_id = nvl(:x,object_id);

Execution Plan
----------------------------------------------------------
Plan hash value: 2163721932

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      | 78688 |    11M|   295   (1)| 00:00:04 |
|   1 |  CONCATENATION                |      |       |       |            |          |
|*  2 |   FILTER                      |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL          | T    | 78687 |    11M|   293   (1)| 00:00:04 |
|*  4 |   FILTER                      |      |       |       |            |          |
|   5 |    TABLE ACCESS BY INDEX ROWID| T    |     1 |   158 |     2   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN         | T_PK |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - filter(:X IS NULL)
   3 - filter("OBJECT_ID" IS NOT NULL)
   4 - filter(:X IS NOT NULL)
   6 - access("OBJECT_ID"=:X)

Note
-----
   - dynamic sampling used for this statement (level=2)

ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2>



this allows us to have two plans in one - a full scan plan for when you don't supply a value for the bind variable and index access when you do.

Funny !!!

Ved, November 12, 2012 - 7:32 am UTC

To an response to Rupesh's follow up earlier..Tom comments

"I could not parse your sentence, sorry I don't know what you were trying to say..."

Tom thinks and breathe SQL. Hats of to you sir

This is a very humble tip

Daniel Quiroz, July 02, 2014 - 9:08 pm UTC

if you have COALESCE function instead of NVL, the index doesn't work, I found it meanwhile I was reviewing a DB and I had to replace by NVL function and now the query uses de index

Regards
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library