Oracle SQL | About unnecessary work with predicate “field=:bind_variable” where bind

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 example, there will not be any reads only if it is an access predicate on index range/unique scan or full table scan on 11.2.0.3 and with gathered stats. If it is FTS on previous versions, then only segment header is read. 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.

Little example #1

[sourcecode language=”sql”]
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;
[/sourcecode]
Results:
[sourcecode language=”sql”]
SQL> @test.sql.txt

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
############################################################################################
[/sourcecode]

[collapse]

Example #2

DDL

[sourcecode language=”sql”]
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.
[/sourcecode]

[collapse]

NULL 1

[sourcecode language=”sql”]
SQL> — NULL 1:
SQL> var a number;

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)
[/sourcecode]

[collapse]

Not NULL 1

[sourcecode language=”sql”]
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 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)
[/sourcecode]

[collapse]

Null 2

[sourcecode language=”sql”]
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 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)
[/sourcecode]

[collapse]

Not NULL 2

[sourcecode language=”sql”]
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 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)
[/sourcecode]

[collapse]