Null is Not Null – Filter

While checking something got through below old OTN question, so wanted to verify on optimizer behaviour

https://community.oracle.com/thread/2406366

TEST@XCONTEST>create table test as select RPAD(TO_CHAR(ROWNUM),3,'X') a,rownum b from dual CONNECT BY LEVEL<=1000; Table created. TEST@XCONTEST>create index test_idx on test (a,b);

Index created.

TEST@XCONTEST>EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>USER,TABNAME=>'TEST',CASCADE=>TRUE,ESTIMATE_PERCENT=>100);

PL/SQL procedure successfully completed.

Check for rows with a=”

TEST@XCONTEST>select * from test where a='';

no rows selected

TEST@XCONTEST>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

PLAN_TABLE_OUTPUT
 ----------------------------------------------------------------------------------------------------------------------------------
 SQL_ID 1f8m76xwz6mpv, child number 0
 -------------------------------------
 select * from test where a=''

Plan hash value: 766971379

---------------------------------------------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 ---------------------------------------------------------------------------
 | 0 | SELECT STATEMENT | | | | 1 (100)| |
 |* 1 | FILTER | | | | | |
 | 2 | TABLE ACCESS FULL| TEST | 1000 | 8000 | 2 (0)| 00:00:01 |
 ---------------------------------------------------------------------------

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

1 - filter(NULL IS NOT NULL)

Optimizer will add filter as null is not null when sub plan tree is false, the sub plan tree will never be executed, that is the reason the cost of parent operation is 0 when the child has 2.

What was the reason for not picking the index and the plan to have the specific filter?

A ZERO length varchar2 is treated as NULL (Thanks to Tom Kyte), so in above query we are searching for null value and nothing equates to null,so the child operation at 2 doesn’t need to be executed at all. Index wasn’t used here as the column a isn’t defined as not null.

TEST@XCONTEST>alter table test modify a not null;

Table altered.

TEST@XCONTEST>select * from test where a=”;

no rows selected

TEST@XCONTEST>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));

PLAN_TABLE_OUTPUT
 ----------------------------------------------------------------------------------------------------
 SQL_ID bzgnr9bg1z2ps, child number 0
 -------------------------------------
 select * from test where a=''

Plan hash value: 1731493281

-----------------------------------------------------------------------------
 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
 -----------------------------------------------------------------------------
 | 0 | SELECT STATEMENT | | | | 1 (100)| |
 |* 1 | FILTER | | | | | |
 | 2 | INDEX FULL SCAN| TEST_IDX | 1000 | 8000 | 1 (0)| 00:00:01 |
 -----------------------------------------------------------------------------

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

1 - filter(NULL IS NOT NULL)
 19 rows selected.

APPEND PARALLEL – Parallel DML

SQL> create table t1 (x number, y number) nologging;

Table created.

SQL> create table t2 (x number, y number) nologging;

Table created.

SQL> alter table t1 parallel (degree 2);
alter table t2 parallel (degree 2);
Table altered.

SQL>

Table altered.

 

SQL> alter session enable parallel dml;

Session altered.

SQL> explain plan for insert /*+ APPEND parallel (t1,4) */ into t1
select rownum, rownum
from dba_source, (select *
from dual
connect by level < 11) 2 3 4 5 ;

Explained.
 PLAN_TABLE_OUTPUT
 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Plan hash value: 1812084382

---------------------------------------------------------------------------------------------------------
 | Id | Operation | Name | Time | TQ |IN-OUT| PQ Distrib |
 ---------------------------------------------------------------------------------------------------------
 | 0 | INSERT STATEMENT | | 00:00:03 | | | |
 | 1 | PX COORDINATOR | | | | | |
 | 2 | PX SEND QC (RANDOM) | :TQ10001 | 00:00:03 | Q1,01 | P->S | QC (RAND) |
 | 3 | LOAD AS SELECT | T1 | | Q1,01 | PCWP | |
 | 4 | PX RECEIVE | | 00:00:03 | Q1,01 | PCWP | |
 | 5 | PX SEND ROUND-ROBIN | :TQ10000 | 00:00:03 | | S->P | RND-ROBIN |
 | 6 | COUNT | | | | | |
 | 7 | MERGE JOIN CARTESIAN | | 00:00:03 | | | |
 | 8 | VIEW | | 00:00:01 | | | |
 |* 9 | CONNECT BY WITHOUT FILTERING| | | | | |
 | 10 | TABLE ACCESS FULL | DUAL | 00:00:01 | | | |
 | 11 | BUFFER SORT | | 00:00:03 | | | |
 | 12 | VIEW | DBA_SOURCE | 00:00:03 | | | |
 | 13 | UNION-ALL | | | | | |
 |* 14 | FILTER | | | | | |
 |* 15 | HASH JOIN | | 00:00:02 | | | |
 |* 16 | HASH JOIN | | 00:00:01 | | | |
 | 17 | INDEX FULL SCAN | I_USER2 | 00:00:01 | | | |
 |* 18 | HASH JOIN | | 00:00:01 | | | |
 | 19 | INDEX FULL SCAN | I_USER2 | 00:00:01 | | | |
 |* 20 | INDEX FAST FULL SCAN | I_OBJ2 | 00:00:01 | | | |
 | 21 | INDEX FAST FULL SCAN | I_SOURCE1 | 00:00:02 | | | |
 | 22 | NESTED LOOPS | | 00:00:01 | | | |
 |* 23 | INDEX SKIP SCAN | I_USER2 | 00:00:01 | | | |
 |* 24 | INDEX RANGE SCAN | I_OBJ4 | 00:00:01 | | | |
 | 25 | NESTED LOOPS | | 00:00:01 | | | |
 | 26 | NESTED LOOPS | | 00:00:01 | | | |
 | 27 | NESTED LOOPS | | 00:00:01 | | | |
 |* 28 | INDEX FAST FULL SCAN | I_OBJ2 | 00:00:01 | | | |
 |* 29 | FIXED TABLE FIXED INDEX| X$JOXSCD (ind:1) | 00:00:01 | | | |
 |* 30 | INDEX RANGE SCAN | I_USER2 | 00:00:01 | | | |
 |* 31 | INDEX RANGE SCAN | I_USER2 | 00:00:01 | | | |
 ---------------------------------------------------------------------------------------------------------

Reference:

Bottom line: if the PX SEND QC is above (lower line number) the LOAD AS SELECT the slaves are doing the insert, if the LOAD AS SELECT is above the PX SEND QC the query coordinator is doing the insert (and you’ll probably see lots of “PX Deq Credit: send blkd” at that point as the PX slaves wait for the QC to load data into blocks).

https://jonathanlewis.wordpress.com/2013/06/06/parallel-dml/