Oracle SQL

Although we know that in the case when we do select from a table and one of the predicates is “field=:bind_variable” and :bind_variable is null, we should not get the rows, oracle not always “think” the same and don’t add upper filter predicate “:bind_variable is not null”, so it can do unnecessary work. It is completely depends from a plan, which will be chosen. Of course this applies to other predicates like >, < or != too.

For examples, will not be any reads only if it is an access predicate on index range/unique scan(it is quite obvious because of ) or full table scan on 11.2.0.3 and with gathered stats. If it is FTS on previous versions will be read only segment header. In others cases oracle will do useless scans.

So if bind variable can be null and you want to be sure that oracle will not do futile work in such cases, just add predicate ":bind_variable is not null".

This note is just aggregated info from recent question from our forum where i participated

Update:The explanation about segment header reads you can listen from Enkitec.tv by Tanel Poder

Full test code you can download as file – test.sql.

Spoiler:: Little example #1	Select Show
set timing off feed off create table XT_NULLS_TEST(a not null, b not null,c) as select level a , mod(level,100) b , lpad(1,100,1) c from dual connect by level<=1e5; create index IX_NULLS_TEST on XT_NULLS_TEST(a); exec dbms_stats.gather_table_stats('','XT_NULLS_TEST'); ------------------- Main test with statistics: ---------------------------------------------- -- Variable with null: var v_null number; exec xt_runstats.init(p_latches => false); -- INDEX RANGE SCAN: select/+ INDEX(XT_NULLS_TEST IX_NULLS_TEST) / count() cnt from XT_NULLS_TEST where a > :v_null; exec xt_runstats.snap('IRS'); -- FULL TABLE SCAN: select/+ FULL(XT_NULLS_TEST) / count() cnt from XT_NULLS_TEST where a > :v_null; exec xt_runstats.snap('FTS'); -- INDEX FAST FULL SCAN: select/+ INDEX_FFS(XT_NULLS_TEST IX_NULLS_TEST) / count() cnt from XT_NULLS_TEST where a > :v_null; exec xt_runstats.snap('IFFS'); --Results set serveroutput on exec xt_runstats.print(p_stats_mask => 'reads\|buff.gets\|consistent gets',p_sta_diff_pct => 1); drop table xt_nulls_test purge; Results: SQL> @test.sql.txt CNT ---------- 0 CNT ---------- 0 CNT ---------- 0 ################ Results: ################## Run # 01 ran in 0 hsecs Run # 02 ran in 0 hsecs Run # 03 ran in 1 hsecs ############################################################################################ Statistics \| IRS \| FTS \| IFFS ############################################################################################ session logical reads................... \| 0 \| 1 \| 230 consistent gets......................... \| 0 \| 1 \| 230 consistent gets from cache.............. \| 0 \| 1 \| 230 consistent gets from cache (fastpath)... \| 0 \| 1 \| 230 ############################################################################################

Spoiler:: Little example #1

Select Show

set timing off feed off

create table XT_NULLS_TEST(a not null, b not null,c)
   as 
      select
            level a
         ,  mod(level,100) b
         ,  lpad(1,100,1)  c 
      from dual 
      connect by level<=1e5;

create index IX_NULLS_TEST on XT_NULLS_TEST(a);

exec dbms_stats.gather_table_stats('','XT_NULLS_TEST');


------------------- Main test with statistics: ----------------------------------------------
-- Variable with null:
var v_null number;

exec xt_runstats.init(p_latches => false);

-- INDEX RANGE SCAN:
select/*+ INDEX(XT_NULLS_TEST IX_NULLS_TEST)       */ count(*) cnt from XT_NULLS_TEST where a > :v_null;
exec xt_runstats.snap('IRS');

-- FULL TABLE SCAN:
select/*+ FULL(XT_NULLS_TEST)                      */ count(*) cnt from XT_NULLS_TEST where a > :v_null;
exec xt_runstats.snap('FTS');

-- INDEX FAST FULL SCAN:
select/*+ INDEX_FFS(XT_NULLS_TEST IX_NULLS_TEST)   */ count(*) cnt from XT_NULLS_TEST where a > :v_null;
exec xt_runstats.snap('IFFS');

--Results
set serveroutput on
exec xt_runstats.print(p_stats_mask => 'reads|buff.*gets|consistent gets',p_sta_diff_pct => 1);

drop table xt_nulls_test purge;

Results:

SQL> @test.sql.txt

       CNT
----------
         0

       CNT
----------
         0

       CNT
----------
         0
################     Results:      ##################
Run #  01 ran in 0 hsecs
Run #  02 ran in 0 hsecs
Run #  03 ran in 1 hsecs
############################################################################################
Statistics                               | IRS            | FTS            | IFFS
############################################################################################
session logical reads................... |              0 |              1 |            230
consistent gets......................... |              0 |              1 |            230
consistent gets from cache.............. |              0 |              1 |            230
consistent gets from cache (fastpath)... |              0 |              1 |            230
############################################################################################

Spoiler:: Little example #2	Select Show
SQL> alter session set optimizer_dynamic_sampling=0; SQL> alter session set statistics_level=all; SQL> create table xt_test as select 1 i from dual; Table created. SQL> var a number; SQL> -- NULL 1: SQL> select * from xt_test where i=:a; no rows selected SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------- SQL_ID 4rjbsjvwbq5m0, child number 0 ------------------------------------- select * from xt_test where i=:a Plan hash value: 3713359643 --------------------------------------------------------------------------------------- \| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \| --------------------------------------------------------------------------------------- \| 0 \| SELECT STATEMENT \| \| 1 \| \| 0 \|00:00:00.01 \| 1 \| \|* 1 \| TABLE ACCESS FULL\| XT_TEST \| 1 \| 3 \| 0 \|00:00:00.01 \| 1 \| --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("I"=:A) 18 rows selected. SQL> -- NOT NULL 1: SQL> exec :a := 0; PL/SQL procedure successfully completed. SQL> select * from xt_test where i=:a; no rows selected SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------- SQL_ID 4rjbsjvwbq5m0, child number 0 ------------------------------------- select * from xt_test where i=:a Plan hash value: 3713359643 ------------------------------------------------------------------------------------------------ \| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \| Reads \| ------------------------------------------------------------------------------------------------ \| 0 \| SELECT STATEMENT \| \| 1 \| \| 0 \|00:00:00.01 \| 3 \| 1 \| \|* 1 \| TABLE ACCESS FULL\| XT_TEST \| 1 \| 3 \| 0 \|00:00:00.01 \| 3 \| 1 \| ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("I"=:A) 18 rows selected. SQL> alter table xt_test add primary key(i); Table altered. SQL> exec :a := null; PL/SQL procedure successfully completed. SQL> -- NULL 2: SQL> select * from xt_test where i=:a; no rows selected SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------- SQL_ID 4rjbsjvwbq5m0, child number 0 ------------------------------------- select * from xt_test where i=:a Plan hash value: 136758570 ----------------------------------------------------------------------------------- \| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| ----------------------------------------------------------------------------------- \| 0 \| SELECT STATEMENT \| \| 1 \| \| 0 \|00:00:00.01 \| \|* 1 \| INDEX UNIQUE SCAN\| SYS_C00161305 \| 1 \| 1 \| 0 \|00:00:00.01 \| ----------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("I"=:A) 18 rows selected. SQL> exec :a := 0; PL/SQL procedure successfully completed. SQL> -- NOT NULL 2: SQL> select * from xt_test where i=:a; no rows selected SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------- SQL_ID 4rjbsjvwbq5m0, child number 0 ------------------------------------- select * from xt_test where i=:a Plan hash value: 136758570 --------------------------------------------------------------------------------------------- \| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \| --------------------------------------------------------------------------------------------- \| 0 \| SELECT STATEMENT \| \| 1 \| \| 0 \|00:00:00.01 \| 1 \| \|* 1 \| INDEX UNIQUE SCAN\| SYS_C00161305 \| 1 \| 1 \| 0 \|00:00:00.01 \| 1 \| --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("I"=:A) 18 rows selected.

Spoiler:: Little example #2

Select Show

SQL> alter session set optimizer_dynamic_sampling=0;
SQL> alter session set statistics_level=all;
SQL> create table xt_test as select 1 i from dual;

Table created.

SQL> var a number;
SQL> -- NULL 1:
SQL> select * from xt_test where i=:a;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  4rjbsjvwbq5m0, child number 0
-------------------------------------
select * from xt_test where i=:a

Plan hash value: 3713359643

---------------------------------------------------------------------------------------
| Id  | Operation         | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |      1 |        |      0 |00:00:00.01 |       1 |
|*  1 |  TABLE ACCESS FULL| XT_TEST |      1 |      3 |      0 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------

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

   1 - filter("I"=:A)


18 rows selected.

SQL> -- NOT NULL 1:
SQL> exec :a := 0;

PL/SQL procedure successfully completed.

SQL> select * from xt_test where i=:a;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  4rjbsjvwbq5m0, child number 0
-------------------------------------
select * from xt_test where i=:a

Plan hash value: 3713359643

------------------------------------------------------------------------------------------------
| Id  | Operation         | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |      1 |        |      0 |00:00:00.01 |       3 |      1 |
|*  1 |  TABLE ACCESS FULL| XT_TEST |      1 |      3 |      0 |00:00:00.01 |       3 |      1 |
------------------------------------------------------------------------------------------------

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

   1 - filter("I"=:A)


18 rows selected.

SQL> alter table xt_test add primary key(i);

Table altered.

SQL> exec :a := null;

PL/SQL procedure successfully completed.

SQL> -- NULL 2:
SQL> select * from xt_test where i=:a;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  4rjbsjvwbq5m0, child number 0
-------------------------------------
select * from xt_test where i=:a

Plan hash value: 136758570

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Starts | E-Rows | A-Rows |   A-Time   |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |      1 |        |      0 |00:00:00.01 |
|*  1 |  INDEX UNIQUE SCAN| SYS_C00161305 |      1 |      1 |      0 |00:00:00.01 |
-----------------------------------------------------------------------------------

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

   1 - access("I"=:A)


18 rows selected.

SQL> exec :a := 0;

PL/SQL procedure successfully completed.

SQL> -- NOT NULL 2:
SQL> select * from xt_test where i=:a;

no rows selected

SQL> select * from table(dbms_xplan.display_cursor('','','ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  4rjbsjvwbq5m0, child number 0
-------------------------------------
select * from xt_test where i=:a

Plan hash value: 136758570

---------------------------------------------------------------------------------------------
| Id  | Operation         | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |      1 |        |      0 |00:00:00.01 |       1 |
|*  1 |  INDEX UNIQUE SCAN| SYS_C00161305 |      1 |      1 |      0 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------

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

   1 - access("I"=:A)


18 rows selected.