Cursor selectivity cube -Part II

In the same vein as the preceding blog post, in this second and last post pf the series I will provide three differents scripts to a bind aware cursor that owes its bind sensitiveness property from a bind variable value having a Hybrid histogram. The first one gives the selectivity cube of a popular Hybrid histogram value. The second script do the same thing for a non-popular Hybrid histogram having an endpoint number. The third and last script gives the selectivity cube of a non captured Hybrid histogram value.

1. Cursor Selectivity cube for a popular Hybrid histogram

In order to put all this in action I am going to use the model I have found in this article:

SQL> desc acs_test_tab
 Name                Null?    Type
 ------------------- -------- -------------
 ID                  NOT NULL NUMBER
 RECORD_TYPE                  NUMBER
 DESCRIPTION                  VARCHAR2(50)

SQL> alter session set cursor_sharing=force;

SQL> select
       column_name
      ,num_distinct
      ,num_buckets
      ,sample_size
      ,histogram
    from
       user_tab_col_statistics
    where table_name = 'ACS_TEST_TAB'
    and column_name  = 'RECORD_TYPE';

COLUMN_NAME  NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE HISTOGRAM
------------ ------------ ----------- ----------- ------------
RECORD_TYPE         50080         254        5407 HYBRID

As you can see the RECORD_TYPE column has a HYBRID histogram with the following popular and non-popular values distribution:

select
         endpoint_number
        ,endpoint_actual_value
        ,endpoint_repeat_count
        --,bucket_size
        ,case when Popularity > 0 then 'Pop'
                   else 'Non-Pop'
          end Popularity
    from
   (
     select
         uth.endpoint_number
        ,uth.endpoint_actual_value
        ,uth.endpoint_repeat_count
        ,ucs.sample_size/ucs.num_buckets bucket_size      
        ,(uth.endpoint_repeat_count - ucs.sample_size/ucs.num_buckets) Popularity
    from
        user_tab_histograms uth
       ,user_tab_col_statistics ucs
   where
        uth.table_name   = ucs.table_name
    and uth.column_name   = ucs.column_name
    and uth.table_name    = 'ACS_TEST_TAB'
    and uth.column_name   = 'RECORD_TYPE'
    )
   order by endpoint_number;

ENDPOINT_NUMBER ENDPOINT_A ENDPOINT_REPEAT_COUNT POPULAR
--------------- ---------- --------------------- -------
              1 1                              1 Non-Pop
           2684 2                           2683 Pop     -- we will use this
           2695 569                            1 Non-Pop -- we wiil use this
           2706 1061                           1 Non-Pop
           2717 1681                           1 Non-Pop
           2727 1927                           1 Non-Pop
../..
           5364 98501                          1 Non-Pop
           5375 98859                          1 Non-Pop
           5386 99187                          1 Non-Pop
           5396 99641                          1 Non-Pop
           5407 99999                          1 Non-Pop

254 rows selected.

There are 254 endpoint actual values of which only one value is popular(2). The following query will be used all over this article to show how we can compute the cursor selectivity cube for the three types of Hybrid histogram mentioned in the introduction:

-- as the ambassador of popular values
SELECT MAX(id) FROM acs_test_tab WHERE record_type = 2;

We will start by getting the selectivity cube of the popular value 2 as shown below (script can be found at the end of this blog post):

SQL> @CurSelCubeHybridPop

PL/SQL procedure successfully completed.

Enter value for bind: 2

BIND              LOW       HIGH
---------- ---------- ----------
2             ,446588    ,545829

Now that we have figured out what will be the value of the cursor selectivity cube of a bind aware cursor using the popular value 2, let’s see whether we have been right or wrong:

SELECT MAX(id) FROM acs_test_tab WHERE record_type = 1;-- only for ACS warmup

SELECT MAX(id) FROM acs_test_tab WHERE record_type = 2;
SELECT MAX(id) FROM acs_test_tab WHERE record_type = 2;                            

SQL_ID  9vu42gjuudpvj, child number 1
-------------------------------------
---------------------------------------------------
| Id  | Operation          | Name         | Rows  |
---------------------------------------------------
|   0 | SELECT STATEMENT   |              |       |
|   1 |  SORT AGGREGATE    |              |     1 |
|*  2 |   TABLE ACCESS FULL| ACS_TEST_TAB | 49621 |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 – filter("RECORD_TYPE"=:SYS_B_0)

select
    child_number
    ,range_id
   ,low
   ,high
 from
   gv$sql_cs_selectivity
 where
    sql_id ='9vu42gjuudpvj';

CHILD_NUMBER   RANGE_ID LOW        HIGH
------------ ---------- ---------- ----------
           1          0 0.446588   0.545829

Spot how the low-high value of child cursor n°1 matches perfectly the low and high value given by the CurSelCubeHybridPop.sql script.

2. Cursor Selectivity cube for a non-popular Hybrid histogram value having an endpoint number

Let’s now consider a non-popular value having an endpoint number in the histogram table and let’s figure out what would be the selectivity cube of its underlying bind aware cursor:

SQL> @CurSelCubeHybridNonPop

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Enter value for bind: 569

BIND              LOW       HIGH
---------- ---------- ----------
569           ,000166    ,000203

And now we are ready to execute the corresponding query, get its execution plan and present the low and high value of the bind aware cursor when ran against this Hybrid non-popular value:

SELECT MAX(id) FROM acs_test_tab WHERE record_type = 569;

SQL_ID  9vu42gjuudpvj, child number 2 –- new execution plan
-------------------------------------
-----------------------------------------------------------------------------------
| Id  | Operation                            | Name                       | Rows  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                            |       |
|   1 |  SORT AGGREGATE                      |                            |     1 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ACS_TEST_TAB               |    18 |
|*  3 |    INDEX RANGE SCAN                  | ACS_TEST_TAB_RECORD_TYPE_I |    18 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 – access("RECORD_TYPE"=:SYS_B_0)

select
    child_number
    ,range_id
   ,low
   ,high
 from
   gv$sql_cs_selectivity
 where
    sql_id ='9vu42gjuudpvj';

CHILD_NUMBER   RANGE_ID LOW        HIGH
------------ ---------- ---------- ----------
           2          0 0.000166   0.000203  –- new execution plan
           1          0 0.446588   0.545829

Spot again the precision of the forecast. The low and high value of child cursor n°2 correspond exactly to the selectivity cube of the Hybrid non-popular value anticipated by the CurSelCubeHybridNonPop.sql script.

3. Cursor Selectivity cube for a non-popular Hybrid histogram value without an endpoint number

A Hybrid histogram value without an endpoint number is a value that exists for the column but which has not been captured by the Histogram gathering program for reasons I am not going to expose here. We can get all those values via an appropriate query. 41 is one value among the not captured ones. Let’s use it in the following demonstration:

Firt we will get its expected selectivity cube:

SQL> @CurSelCubeHybridNonPopWithoutEndPoint

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Enter value for bind: 41

        41        LOW       HIGH
---------- ---------- ----------
        41    ,000009    ,000011

This selectivity range is not included in the selectivity range of child cursor n°1 nor in that of child cursor n°2. This is why if we use it in the following query it will certainly force ECS to hard parse a new execution plan as the following proves:

SQL> SELECT MAX(id) FROM acs_test_tab WHERE record_type = 41;

SQL_ID  9vu42gjuudpvj, child number 3 -- new execution plan
-------------------------------------
-----------------------------------------------------------------------------------
| Id  | Operation                            | Name                       | Rows  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                            |       |
|   1 |  SORT AGGREGATE                      |                            |     1 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ACS_TEST_TAB               |     1 |
|*  3 |    INDEX RANGE SCAN                  | ACS_TEST_TAB_RECORD_TYPE_I |     1 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("RECORD_TYPE"=:SYS_B_0)

As it has been again correctly expected a new execution plan (child cursor n°3) has been compiled. But since the new execution plan is identical to an existing one (child cursor n°2) Oracle has merged the selectivities of these two cursors, kept shareable the last compiled one and deleted the old plan as the following proves:

select
    child_number
    ,range_id
   ,low
   ,high
 from
   gv$sql_cs_selectivity
 where
    sql_id ='9vu42gjuudpvj';

CHILD_NUMBER   RANGE_ID LOW        HIGH
------------ ---------- ---------- ----------
           3          0 0.000009   0.000203   -- new plan with merge selectivity
           2          0 0.000166   0.000203
           1          0 0.446588   0.545829

select
   child_number
  ,is_shareable
from
   gv$sql
where
   sql_id = '9vu42gjuudpvj';

CHILD_NUMBER I
------------ -
           0 N
           1 Y
           2 N –- deleted 
           3 Y – selectivity cube merged

4. Conclusion

The first blog post of this series provided a script with which we can anticipate the cursor selectivity cube range of a bind aware cursor when its bind sensitivenes is due to a predicate having a Frequency histogram. In this article we presented three new scripts giving the same anticipation for a bind aware cursor that owes its bind sensitivenes respectively to a popular Hybrid histogram, a non-popular Hybrid histogram having and endpoint number and a non captured Hybrid histogram. This concludes the series
CurSelCubeHybridNonPop

CurSelCubeHybridNonPopWithoutEndPoint

CurSelCubeHybridPop

Cursor selectivity cube -Part I

Bind variable selectivity is the building block on which the Extended Cursor Sharing Layer code reasons to compile a new good enough execution plan or share an existing one. It kicks in only for a bind aware cursor. The underlying child cursor is given a selectivity interval comprised between a low and a high value derived from the bind variable selectivity that initiates it. This is what Oracle refers to as a cursor selectivity cube shown in the following picture:

The ECS layer code launches the bind-aware cursor matching algorithm at each soft parse of a bind aware cursor. If the new bind variable value selectivity is outside an existing selectivity cube (low-high exposed in gv$sql_cs_selectivity) then a new hard parse is done and a new child cursor with a new selectivity cube is created. If, however, the peeked bind variable selectivity falls into a range of an existing child cursor selectivity cube, ECS will then share the corresponding child cursor’s execution plan. Finally if a new hard parsed execution plan is equivalent to an existing one then both child cursors will be merged. The selectivity cube of the last created child cursor will be adjusted while the previous cursor which served the merge process will be marked un-shareable in order to save space in the memory and reduce the time spent during cursor pruning activity.

The rest of this article shows, first, how the selectivity cube (low-high value) is computed for a bind variable value with a Frequency histogram. It then explains how two cursors with the same execution plan but different selectivity cubes are merged to form a single child cursor with an updated low-high range interval.

1. Cursor Selectivity cube

For simplicity’s sake I am going to use my old and helpful model:

create table t_acs(n1 number, n2 number);

begin
for j in 1..1200150 loop
   if j=1 then
      insert into t_acs values (j,1);
   elsif j> 1 and j <= 101 then          
    insert into t_acs values (j,100);     
    elsif j> 101 and j <= 1101 then          
    insert into t_acs values (j,1000);     
    elsif j> 10001 and j <= 110001 then          
    insert into t_acs values (j,10000);     
    else insert into t_acs values (j,1000000);     
   end if;  end loop;  commit;  
end  
/  
create index t_acs_idx1 on t_acs(n2); 
begin 
dbms_stats.gather_table_stats
        (user, 't_acs' 
        ,method_opt => 'for all columns size skewonly'
        ,cascade => true
	    ,estimate_percent => dbms_stats.auto_sample_size);
end;
/

The above data set contains 1,200,150 rows of which the n2 column has 5 distinct highly skewed values as shown below:

SQL> select n2, count(1) from t_acs group by n2 order by 2 ;

        N2   COUNT(1)
---------- ----------
         1          1
       100        100
      1000        910
     10000     100000
   1000000    1099139

The n2 column has a FREQUENCY histogram.

SQL> select column_name, histogram
     from user_tab_col_statistics
     where table_name ='T_ACS'
     and column_name  ='N2';

 COLUMN_NAM HISTOGRAM
---------- -----------
N2         FREQUENCY

The selectivity of the numeric n2 column is then computed via the following formula:

SQL> select
        endpoint_actual_value bind
       ,round( endpoint_actual_value/1200150,6) bind_sel
       ,endpoint_number
       ,endpoint_number –(lag(endpoint_number,1,0)
                         over(order by endpoint_number)) value_count
     from user_tab_histograms
     where table_name ='T_ACS'
     and column_name  ='N2'
     order by endpoint_number;

BIND         BIND_SEL ENDPOINT_NUMBER VALUE_COUNT
---------- ---------- --------------- -----------
1             ,000001               1           1
100           ,000083             101         100
1000          ,000833            1011         910
10000         ,008332          101011      100000
1000000       ,833229         1200150     1099139

The cursor selectivity cube is computed using the selectivity of the n2 bind variable value and an offset of +- 10% far from that selectivity forming  the x and y abscises of the cursor selectivity cube(see the above figure):

SQL> select
    bind
   ,round((sel_of_bind - offset),6) low
   ,round((sel_of_bind + offset),6) high
from
   (select
      bind
     ,value_count/1200150 sel_of_bind
	 ,0.1*(value_count/1200150) offset
     from
     (
      select
        endpoint_actual_value bind
       ,round( endpoint_actual_value/1200150,6) bind_sel
       ,endpoint_number
       ,endpoint_number –(lag(endpoint_number,1,0)
                         over(order by endpoint_number)) value_count
     from user_tab_histograms
     where table_name ='T_ACS'
     and column_name  ='N2'
     order by endpoint_number
     )
    )
    ;

BIND              LOW       HIGH
---------- ---------- ----------
1             ,000001    ,000001
100           ,000075    ,000092
1000          ,000682    ,000834
10000         ,074991    ,091655
1000000       ,824251   1,007418

Let’s put this select into a sql script and name it CurSelCubeFreq.sql.

We will be back to this script later in this article. For this moment, we will put it on hold and we will embark on the cursor merge section.

2. Cursor merge

The cursor of the query I am going to execute is not yet bind aware as the following proves:

select
    child_number
	,range_id
   ,low
   ,high
 from
   gv$sql_cs_selectivity
 where
    sql_id ='42wmc4buuh6wb';

no rows selected

But the next execution will mark it bind aware (cursor sharing parameter is set to FORCE) and will generate a new (full table scan) execution plan:

SQL> select count(1) from t_acs where N2 = 1e6;

  COUNT(1)
----------
   1099139

SQL> start xpsimp

SQL_ID  42wmc4buuh6wb, child number 1
-------------------------------------
--------------------------------------------
| Id  | Operation          | Name  | Rows  |
--------------------------------------------
|   0 | SELECT STATEMENT   |       |       |
|   1 |  SORT AGGREGATE    |       |     1 |
|*  2 |   TABLE ACCESS FULL| T_ACS |  1099K|
--------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("N2"=:SYS_B_1)

select
    child_number
	,range_id
   ,low
   ,high
 from
   gv$sql_cs_selectivity
 where
    sql_id ='42wmc4buuh6wb';

CHILD_NUMBER   RANGE_ID LOW        HIGH
------------ ---------- ---------- ----------
           1          0 0.824251   1.007418

And, for the sake of clarity, let’s print again the content of the CurSelCubeFre.sql (slightly updated):

SQL> @CurSelCubeFreq
Enter value for bind: 1e6

BIND              LOW       HIGH
---------- ---------- ----------
1000000       ,824251   1,007418

Spot the coincidence 🙂

Suppose now that I want to know whether a less selective bind variable value (1) will force a new hard parse or share an existing execution plan. For that, I will first get the selectivity cube of this bind variable as shown below:

-- The selectivity cube of bind variable 1
SQL> @CurSelCubeFreq
Enter value for bind: 1

BIND              LOW       HIGH
---------- ---------- ----------
1             ,000001    ,000001

As you can see this bind variable value has a selectivity outside outside of that of the existing child cursor n°1. This is why ECS will fairly likely trigger a new hard parse as the following proves:

SQL> select count(1) from t_acs where N2 = 1;

  COUNT(1)
----------
         1

SQL> start xpsimp

SQL_ID  42wmc4buuh6wb, child number 2 -- new execution plan
-------------------------------------
------------------------------------------------
| Id  | Operation         | Name       | Rows  |
------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |
|   1 |  SORT AGGREGATE   |            |     1 |
|*  2 |   INDEX RANGE SCAN| T_ACS_IDX1 |     1 |
------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N2"=:SYS_B_1)

select
    child_number
	,range_id
   ,low
   ,high
 from
   gv$sql_cs_selectivity
 where
    sql_id ='42wmc4buuh6wb';

CHILD_NUMBER   RANGE_ID LOW        HIGH
------------ ---------- ---------- ----------
           2          0 0.000001   0.000001  --> new execution plan
           1          0 0.824251   1.007418

Notice that we have got, as expected, a new child cursor n°2 with a new range of selectivity that has produced an index range scan execution plan.

Finally, what if, in the next run, I will use a bind variable value(10000) having a different selectivity but producing the same index range scan execution plan?

SQL> @CurSelCubeFreq
Enter value for bind: 10000

BIND              LOW       HIGH
---------- ---------- ----------
10000         ,074991    ,091655

SQL> select count(1) from t_acs where N2 = 10000;

  COUNT(1)
----------
    100000

SQL> start xpsimp

SQL_ID  42wmc4buuh6wb, child number 3
-------------------------------------
------------------------------------------------
| Id  | Operation         | Name       | Rows  |
------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |
|   1 |  SORT AGGREGATE   |            |     1 |
|*  2 |   INDEX RANGE SCAN| T_ACS_IDX1 |   100K|
------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("N2"=:SYS_B_1)

select
    child_number
	,range_id
   ,low
   ,high
 from
   gv$sql_cs_selectivity
 where
    sql_id ='42wmc4buuh6wb';

CHILD_NUMBER   RANGE_ID LOW        HIGH
------------ ---------- ---------- ----------
           3          0 0.000001   0.091655  --> new child cursor
           2          0 0.000001   0.000001
           1          0 0.824251   1.007418

Notice how the low value of child cursor n°3 (0.000001) corresponds to the low value of child cursor n°2 and not to the low selectivity of the bind variable value for which it has been compiled (.074991). This is because the selectivity of child cursor n°2 has been merged with that of child cursor n°3 since their execution plans are identical. While the selectivity cube of child cursor n°3 has been enlarged child cursor n°2 has been deleted (put in a non-shareable status) as shown below:

select
   child_number
  ,is_bind_aware
  ,is_shareable
 from
   gv$sql
 where
   sql_id ='42wmc4buuh6wb'; 

CHILD_NUMBER I I
------------ - -
           0 N N
           1 Y Y
           2 Y N → is not shareable
           3 Y Y → includes the selectivity of child n°2

If we want to know whether the bind variable value 100 will share an existing execution plan or force a new one we have to check if its selectivity falls into an existing child cursor selectivity cube or not:

SQL> @CurSelCubeFreq
Enter value for bind: 100

BIND              LOW       HIGH
---------- ---------- ----------
100           ,000075    ,000092

This is going to share the child cursor n°3 since its selectivity falls into the low-high range of that cursor:

SQL> select count(1) from t_acs where N2 = 100;

  COUNT(1)
----------
       100

SQL> start xpsimp

SQL_ID  42wmc4buuh6wb, child number 3
-------------------------------------
------------------------------------------------
| Id  | Operation         | Name       | Rows  |
------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |
|   1 |  SORT AGGREGATE   |            |     1 |
|*  2 |   INDEX RANGE SCAN| T_ACS_IDX1 |   100K|
------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 – access("N2"=:SYS_B_1)

3. Conclusion

That is how the Extended Cursor Sharing layer code works. A combination of bind variable selectivities, with a possibly extendable cursor selectivity cube, allows Oracle to decide, at each execution of a bind aware cursor, whether to share an existing execution plan, compile a brand new one, or merge two cursors to form a single one to the detriment of a deleted one. This last action reduces the memory usage and the number of child cursors during the non-innocent child cursor pruning that occurs when a shareable parent cursor with multiple childs is soft parsed.

DDL(constraint), DML(predicate) and SQL CASE

Check and integrity constraints are DDL (Data Definition Language) operations. They consider TRUE and NULL alike. Query predicates are DML (Data Manipulation Language) operations. They consider FALSE and NULL alike.

Did you spot the difference?

In this article Jonathan Lewis turns this to:

• DDL predicate allows a row if it does not evalute to FALSE – which means it is allowed to evaluate to TRUE or NULL.
• DML predicate returns a row if it evaluates to TRUE – which means it will not return a row if it evaluates to FALSE or NULL.

In this article I am going to show that using the SQL CASE statement we can make DDL and DML predicates behave identically.

The standard boolean logic difference between DDL and DML predicates having been already explained (see this and this excellent book) I am not going to repeat that demonstration here. But, for simplicity’s sake, I am going to reuse a model from one of those articles as shown below:

create table t1 (v1 varchar2(1));
alter table t1 add constraint t1_ck_v1 check (v1 in ('a','b','c'));
insert into t1 values ('a');
insert into t1 values ('b');
insert into t1 values ('c');

The t1_ck_v1 DDL check constraint is supposed to prevent insertions into t1 table of any value that doesn’t match ‘a’ or ‘b’ or ‘c’. But since DDL check and integrity constraint considers TRUE and NULL alike, we are allowed to insert a null value in t1 as the following demonstrates:

SQL> insert into t1 values (null);
1 row created.

It suffices to change the check constraint t1_ck_v1 definition and nulls will definitively be banned from entering into t1 table as shown below:

truncate table t1;
alter table t1 drop constraint t1_ck_v1;

alter table t1 add constraint t1_ck_v1 check
    (case when v1 in ('a','b','c') then 1 else 0 end = 1);

insert into t1 values ('a');
insert into t1 values ('b');
insert into t1 values ('c');

SQL> insert into t1 values (null);
insert into t1 values (null)
*
ERROR at line 1:
ORA-02290: check constraint (C##MHOURI.T1_CK_V1) violated

In contrast to the first created check constraint the new refactored one has successfully pre-empted a null value from being inserted into t1 table (we will see shortly why).

The above demonstrates that using the CASE statement we made a DDL predicates considering NULL and FALSE alike while they are originally implemented to treat TRUE and NULL alike. Let’s now check how the CASE statement works with a DML predicate:

SQL> select count(1) from t1;

  COUNT(1)
----------
         3

SQL> select count(1) from t1
     where
        ( case when v1 in ('a','b','c') then 1 else 0 end = 1);

  COUNT(1)
----------
         3

Indeed the case statement works the same way in both DML and DDL predicates.

And now the legitimate question is why does the CASE statement make the check constraint, which is a DDL predicate, consider FALSE and NULL alike?
The answer can be found in the Oracle official documentation:

“In a searched CASE expression, Oracle searches from left to right until it finds an occurrence of condition that is true, and then returns return_expr. If no condition is found to be true, and an ELSE clause exists, Oracle returns else_expr. Otherwise, Oracle returns null”

Thanks to the ELSE clause of the CASE-WHEN combination we transformed a NULL possibility into a NOT NULL value (0 in the current situation). This is why we should always forsee an ELSE into a CASE statement. Should we have ommited the ELSE clause in the last t1_ck_v1 we would have then allowed null values to be inserted into t1 table as shown below:

truncate table t1;
alter table t1 drop constraint t1_ck_v1;

alter table t1 add constraint t1_ck_v1 check
    (case when v1 in ('a','b','c') then 1 end = 1);

SQL> insert into t1 values(null);
1 row created.

Furthermore ommitting the ELSE in the CASE allows not null values not in the triplet (‘a’,’b’,’c’) to be inserted into t1 table as shown below:

SQL> insert into t1 values ('z');
1 row created.

SUMMARY
Protect yourself against the always threatening NULL by setting your column not null whenever possible. Bear in mind that the CASE statement, when used with its ELSE part, can not only make DDL predicate treats FALSE and NULL alike, but it behaves the same way during DML and DDL predicate.

Execution plans : blocking and non-blocking operations

Below are two 12cR2 row-source execution plans of the same query:

SQL_ID  9yqrqpgux93zk, child number 0
--------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |      1 |        |      5 |
|*  1 |  VIEW                    |      |      1 |   1000 |      5 |
|*  2 |   WINDOW SORT PUSHED RANK|      |      1 |   1000 |      5 |
|   3 |    TABLE ACCESS FULL     | T1   |      1 |   1000 |   1000 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN"<=5)
   2 - filter(RANK() OVER ( ORDER BY "N1")<=5)

SQL_ID  9yqrqpgux93zk, child number 0
--------------------------------------------------------------------------
| Id  | Operation              | Name         | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |      1 |        |      5 |
|*  1 |  VIEW                  |              |      1 |   1000 |      5 |
|*  2 |   WINDOW NOSORT STOPKEY|              |      1 |   1000 |      5 |
|   3 |    INDEX FULL SCAN     | IDX_T1_N1_UK |      1 |   1000 |      6 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN"<=5)
   2 - filter(RANK() OVER ( ORDER BY "N1")<=5)
 

Forget the switch from a FULL TABLE scan to an INDEX FULL scan at line n° 3 and the switch from a SORT to a NOSORT operation at line n°2. What other remarkable difference we can still spot out there? Have you noticed that operation n°3 produced 1000 rows in the first execution plan and only 6 rows in the second one?

Here’s the model with which I obtained the above execution plans respectively:

create table t1 
as select 
    rownum n1
   ,trunc((rownum-1/3)) n2 
from dual 
connect by level <=1e3;

create unique index idx_t1_n1_uk on t1(n1);

exec dbms_stats.gather_table_stats(user, 't1');

select /*+ gather_plan_statistics */ n1
from 
   (select 
       n1
	  ,rank() over (order by n1) rn
	from t1)
where
   rn <=5;

select * from table(dbms_xplan.display_cursor(null,null, 'allstats last'));

alter table t1 modify (n1 not null);

-- execute the same query

select * from table(dbms_xplan.display_cursor(null,null, 'allstats last'));

What happened here so that an operation without a filter predicate produces 6 rows where it is supposed to generate the entire set of rows in the table i.e. 1,000 rows?

The explanation comes from the way Oracle handles blocking and non-blocking operations. A blocking operation requires receiving all rows from its child operation before a single row can be processed. A non-blocking operation consumes and produces rows at the same time.

Applied to the current situation, we see that the two previous execution plans have both a blocking operation at line n°2 which is the VIEW operation.

Right?

In fact, as magnificiently explained by Jonathan Lewis in this post, the blocking nature of the VIEW operation is function of its first child operation. If this one is a blocking operation then the VIEW is a blocking operation as well. If, however, the child operation is not blocking then its VIEW parent operation is a non-blocking operation that can consume and produce rows at the same time.

The first child of the two VIEW operations in the above execution plans is respectively:

|*  2 |   WINDOW SORT PUSHED RANK|

|*  2 |   WINDOW NOSORT STOPKEY| 

The WINDOW SORT PUSHED RANK child operation of the VIEW is a sort operation. Sorts,as you know, are blocking operations. This is why the VIEW in the first execution plan is a blocking operation. Oracle has to retrieve all rows from table t1 (A-Rows = 1000) at operation n°3, sort them totally at operation n°2 before applying the filter predicate n°2 reducing as such the number of retrieved rows to only 5.

In the second execution plan the WINDOW NOSORT STOPKEY child operation of the VIEW is a non-blocking operation thanks to the ordered list of keys it receives from its INDEX FULL SCAN child operation. The VIEW asks for the first row from its first child operation which, at its turn, asks for the same row from its child operation the INDEX FULL SCAN at line 3. The index gives the first ordered key it has found to its parent operation, the WINDOW NOSORT STOPKEY,which feeds back again it parent operation, the VIEW, provided the index key survives the filter predicate n°2. The VIEW asks then for the second row, for the third row, and so on until the WINDOW NOSORT STOPKEYdeclares the end of the query. Indeed, the WINDOW NOSORT STOPKEY knows the upper bound it can’t go beyound. This is why when it receives the 6th row from the index full scan operation (A-Rows= 6) it realises that this row spans its upper bound and that it is now time to signal to the coordinator (select statement) the end of the query.

Here’s another example demonstrating that even with FULL segment scans Oracle is able to stop generating rows at the appropriate moment. All what it requires from the parent operation is to be a non-blocking operation able to consume and produce rows at the same time:

-- n1  is declared nullable

select /*+ gather_plan_statistics */ n1
from
   t1
fetch next 5 rows only;

------------------------------------------------------------------
| Id  | Operation              | Name | Starts | E-Rows | A-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |      1 |        |      5 |
|*  1 |  VIEW                  |      |      1 |      5 |      5 |
|*  2 |   WINDOW NOSORT STOPKEY|      |      1 |   1000 |      5 |
|   3 |    TABLE ACCESS FULL   | T1   |      1 |   1000 |      6 |
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY  NULL )<=5)

-- n1 is declared not null

select /*+ gather_plan_statistics */ n1
from
   t1
fetch next 5 rows only;

--------------------------------------------------------------------------
| Id  | Operation              | Name         | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |      1 |        |      5 |
|*  1 |  VIEW                  |              |      1 |      5 |      5 |
|*  2 |   WINDOW NOSORT STOPKEY|              |      1 |   1000 |      5 |
|   3 |    INDEX FAST FULL SCAN| IDX_T1_N1_UK |      1 |   1000 |      6 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=5)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY  NULL )<=5)

Summary

Sometimes we need to read and interpret execution plans not only in their order of operation using the “first child first, recursive descent” rule, but also in their order of data flow propagation (or row-source generation). In the latter case, knowing how to identify blocking and non-blocking operations is very important.