filter(NULL IS NOT NULL)

“filter(NULL IS NOT NULL)” in explain plan usually means what CBO found a conflict between your WHERE-clause predicates and declarative constraints on the tables involved.

example –


SQL> alter session set statistics_level=all; 

drop table t1a;
drop table t1b; 

create table t1a as select a.*, 'A' ind from dba_tables a where rownum <= 10;
create table t1b as select a.*, 'B' ind from dba_tables a where rownum <= 10; 

alter table t1a add constraint t1a_ck check (ind='A') validate;
alter table t1b add constraint t1b_ck check (ind='B') validate; 

create or replace view v1 as
select * from T1A
union all
select * from T1B
;

select * from v1 where ind='A';

...

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last cost note')); 

PLAN_TABLE_OUTPUT
------------------------------------------------
SQL_ID	2vxaadnbduq8m, child number 0
-------------------------------------
select * from v1 where ind='A'

Plan hash value: 2156607360

----------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |  A-Time    | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |	    |	   1 |	      |     4 (100)|	 10 |00:00:00.01 |       4 |
|   1 |  VIEW                | V1   |	   1 |	    1 |     4   (0)|	 10 |00:00:00.01 |       4 |
|   2 |   UNION-ALL          |	    |	   1 |	      |            |	 10 |00:00:00.01 |       4 |
|*  3 |    TABLE ACCESS FULL | T1A  |	   1 |	   10 |     3   (0)|	 10 |00:00:00.01 |       4 |
|*  4 |    FILTER            |	    |	   1 |	      |            |	  0 |00:00:00.01 |       0 |
|*  5 |     TABLE ACCESS FULL| T1B  |	   0 |	    1 |     3   (0)|	  0 |00:00:00.01 |       0 |
----------------------------------------------------------------------------------------------------

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

   3 - filter("IND"='A')
   4 - filter(NULL IS NOT NULL)
   5 - filter("IND"='A')

And if constraints are validated, Oracle does not physically touch the Object at all (Starts=0 above).
Its a good example where constraints actually help performance, not hurt it.

In contrast, this is the plan if constraints are not validated:

SQL> alter table t1b modify constraint t1b_ck novalidate; 

SQL> select * from v1 where ind='A';
...

SQL> select * from table(dbms_xplan.display_cursor(null, null, 'allstats last cost note')); 

PLAN_TABLE_OUTPUT
------------------------------------------------
SQL_ID	2vxaadnbduq8m, child number 0
-------------------------------------
select * from v1 where ind='A'

Plan hash value: 1278722009

---------------------------------------------------------------------------------------------------
| Id  | Operation	    | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time	| Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |	   |	  1 |	     |	   4 (100)|	10 |00:00:00.01 |       7 |
|   1 |  VIEW               | V1   |	  1 |	   1 |	   4   (0)|	10 |00:00:00.01 |       7 |
|   2 |   UNION-AL          |	   |	  1 |	     |	          |	10 |00:00:00.01 |       7 |
|*  3 |    TABLE ACCESS FULL| T1A  |	  1 |	  10 |	   3   (0)|	10 |00:00:00.01 |       4 |
|*  4 |    TABLE ACCESS FULL| T1B  |	  1 |	   1 |	   3   (0)|	 0 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------

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

   3 - filter("IND"='A')
   4 - filter("IND"='A')

Starts=1 – Oracle accesses both tables, doing unnecessary IO.

Check us out on http://avmconsulting.net

Enter your email address to follow this blog and receive notifications of new posts by email.

Advertisements
This entry was posted in Oracle, SQL and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s