Mohamed Houri’s Oracle Notes

January 20, 2018

NESTED LOOP and full/right outer join in modern RDBMS

Filed under: CBO,explain plan — hourim @ 11:36 am

The Oracle Cost Based Optimizer, the MS-SQL Server optimizer and the PostgreSQL query planner cannot use a NESTED LOOP physical operation to execute FULL OUTER and RIGHT OUTER joins logical operations. They all address the RIGHT OUTER join limitation by switching the inner and the outer row source so that a LEFT OUTER JOIN can be used. While the first two optimizer turn the FULL OUTER join into a LEFT OUTER join concatenated with an ANTI-join, PostgreSQL query planner will always use a HASH/MERGE JOIN to do a FULL OUTER join.

Let’s make this less confusing by starting with the basics. The algorithm of a NESTED LOOP physical operation is:

for each row ro in the outer row source 
loop
   for each row ri in the inner row source
   loop
     if ro joins ri then return current row
   end loop
end loop

1.Oracle 12cR2

A simple execution of the above algorithm can be schematically represented via the following Oracle execution plan:

select /*+ use_nl(t1 t2) */ 
      t1.*
from t1 inner join t2
on t1.n1 = t2.n1;

----------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      3 |
|   1 |  NESTED LOOPS      |        |      1 |      4 |      3 |
|   2 |   TABLE ACCESS FULL| T1     |      1 |      3 |      3 |
|*  3 |   INDEX RANGE SCAN | T2_IDX |      3 |      1 |      3 |
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 – access("T1"."N1"="T2"."N1")

As you can see, in accordance with the algorithm, for each row in T1 table (A-Rows=3 operation id n°2) we scanned 3 times (Starts = 3 operation id n°3) the T2_IDX index.

Let’s now try a FULL OUTER join but without any hint:

select  
      t1.*
from t1 full outer join t2
on t1.n1 = t2.n1;

---------------------------------------------------
| Id  | Operation             | Name     | Rows  |
---------------------------------------------------
|   0 | SELECT STATEMENT      |          |       |
|   1 |  VIEW                 | VW_FOJ_0 |     4 |
|*  2 |   HASH JOIN FULL OUTER|          |     4 |
|   3 |    TABLE ACCESS FULL  | T1       |     3 |
|   4 |    TABLE ACCESS FULL  | T2       |     4 |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."N1"="T2"."N1")

So far so good. A HASH JOIN FULL OUTER to honor a full outer join between two tables.

But what if I want to use a NESTED LOOP FULL OUTER instead of HASH JOIN FULL OUTER join ?

select  /*+ use_nl(t1 t2) */
      t1.*
from t1 FULL outer join t2
on t1.n1 = t2.n1;

-------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes |
-------------------------------------------------------
|   0 | SELECT STATEMENT     |        |       |       |
|   1 |  VIEW                |        |     6 |   120 |
|   2 |   UNION-ALL          |        |       |       |
|   3 |    NESTED LOOPS OUTER|        |     4 |    40 |
|   4 |     TABLE ACCESS FULL| T1     |     3 |    21 |
|*  5 |     INDEX RANGE SCAN | T2_IDX |     1 |     3 |
|   6 |    NESTED LOOPS ANTI |        |     2 |    12 |
|   7 |     TABLE ACCESS FULL| T2     |     4 |    12 |
|*  8 |     TABLE ACCESS FULL| T1     |     2 |     6 |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("T1"."N1"="T2"."N1")
   8 - filter("T1"."N1"="T2"."N1")

What the heck is this execution plan of 8 operations?

Instead of having a simple NESTED LOOP FULL OUTER I got a concatenation of NESTED LOOPS OUTER and a NESTED LOOPS ANTI join.That’s an interesting transformation operated by the CBO.

Should I have tried to reverse engineer the query that sits behind the above execution plan I would have very probably obtained the following query:

select  
      t1.*
from 
      t1 
     ,t2 
where t1.n1 = t2.n1(+)
union all
select 
     t2.*
from t2
where not exists (select /*+ use_nl(t2 t1) */ 
                      null 
                  from t1 
                  where t1.n1 = t2.n1);

------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes |
------------------------------------------------------
|   0 | SELECT STATEMENT    |        |       |       |
|   1 |  UNION-ALL          |        |       |       |
|   2 |   NESTED LOOPS OUTER|        |     4 |    40 |
|   3 |    TABLE ACCESS FULL| T1     |     3 |    21 |
|*  4 |    INDEX RANGE SCAN | T2_IDX |     1 |     3 |
|   5 |   NESTED LOOPS ANTI |        |     2 |    20 |
|   6 |    TABLE ACCESS FULL| T2     |     4 |    28 |
|*  7 |    TABLE ACCESS FULL| T1     |     2 |     6 |
------------------------------------------------------

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

   4 - access("T1"."N1"="T2"."N1")
   7 - filter("T1"."N1"="T2"."N1")

In fact when I have directed Oracle to use a NESTED LOOP to FULL OUTER JOIN T1 and T2 it has turned out my instruction into:

 T1 LEFT OUTER JOIN T2 UNION ALL T2 ANTI JOIN T1

Which is nothing else than :

  • select all rows from T1 and T2 provided they join
  • add to these rows, rows from T1 that don’t join (LEFT OUTER)
  • add to these rows, all rows from T2 that don’t join (ANTI) with rows from T1

Do you know why Oracle did all this somehow complicated gymnastic?

It did it because I asked it to do an impossible operation: NESTED LOOP doesn’t support FULL OUTER join.

It doesn’t support RIGHT OUTER join as well as shown below:

select  /*+ use_nl(t1 t2) */
      t1.*
from t1 
RIGHT outer join t2
on t1.n1 = t2.n1;

---------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |
|   1 |  NESTED LOOPS OUTER|      |     4 |    40 |
|   2 |   TABLE ACCESS FULL| T2   |     4 |    12 |
|*  3 |   TABLE ACCESS FULL| T1   |     1 |     7 |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T1"."N1"="T2"."N1")

Don’t be confused here. The above RIGHT OUTER JOIN has been turned into a LEFT OUTER JOIN by switching the inner and the outer table. As such, T2 being placed in the left side of the join Oracle is able to use a NESTED LOOP to operate a LEFT OUTER JOIN. You will see this clearly explained in the corresponding SQL Server execution plan I will show later in this article.

2. PostgreSQL 10.1

Since there are no hints in PostgreSQL to make a join using a NESTED LOOP I will start by cancelling hash and merge join operations as shown below:

postgres=# set enable_mergejoin=false;
SET
postgres=# set enable_hashjoin=false;
SET

And now I am ready to show you how the PostgreSQL query planner turns a right outer join into a left outer join when a NESTED LOOP operation is used:

postgres=# explain
postgres-# select
postgres-#       t1.*
postgres-# from t1 right outer join t2
postgres-# on t1.n1 = t2.n1;

                            QUERY PLAN
-------------------------------------------------------------------
 Nested Loop Left Join  (cost=0.00..95.14 rows=23 width=42)
   Join Filter: (t1.n1 = t2.n1)
   ->  Seq Scan on t2  (cost=0.00..1.04 rows=4 width=4)
   ->  Materialize  (cost=0.00..27.40 rows=1160 width=42)
         ->  Seq Scan on t1  (cost=0.00..21.60 rows=1160 width=42)
(5 lignes)

However, in contrast to Oracle and MS-SQL Server, PostgreSQL query planner is unable to transform a full outer join into a combination of an NESTED LOOP LEFT OUTER join and an ANTI-join as the following demonstrates:

explain 
select
      t1.*
from t1 full outer join t2
on t1.n1 = t2.n1;
                                QUERY PLAN
--------------------------------------------------------------------------
 Hash Full Join  (cost=10000000001.09..10000000027.27 rows=1160 width=42)
   Hash Cond: (t1.n1 = t2.n1)
   ->  Seq Scan on t1  (cost=0.00..21.60 rows=1160 width=42)
   ->  Hash  (cost=1.04..1.04 rows=4 width=4)
         ->  Seq Scan on t2  (cost=0.00..1.04 rows=4 width=4)

Spot in passing how disabling the hash join option (set enable_hashjoin=false) is not an irreversible action. Whenever the query planner is unable to find another way to accomplish its work it will use all option available even those being explicitely disabled.

3. MS-SQL Server 2016



4. Summary
In several if not all modern Relational DataBase Management Systems, NESTED LOOP operation doesn’t support right outer and full outer join. Oracle, MS-SQL Server and PostgreSQL turn “T1 right outer join T2” into “T2 left outer join T1” by switching the inner and the outer row source. Oracle and SQL Server turn a full outer join between T1 and T2 into a T1 left outer join T2 union-all T2 anti-join T1. PostgreSQL will always use a hash/merge to full outer join T1 and T2.

Model

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
-- NESTED LOOP and full/right outer join : Oracle
--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
drop table t1;
drop table t2;
create table t1(t1_id int , t1_vc varchar2(10));

insert into t1 values (1, 't1x');
insert into t1 values (2, 't1y');
insert into t1 values (3, 't1z');
 
create index t1_idx on t1(t1_id);

create table t2 (t2_id int, t2_vc varchar(10));

insert into t2 values (2, 't2x');
insert into t2 values (3, 't2y');
insert into t2 values (3, 't2yy');
insert into t2 values (4, 't2z');

create index t2_idx on t2(t2_id);
Advertisements

December 22, 2017

Null-Accepting Semi-Join

Filed under: CBO — hourim @ 11:15 am

Introduction

Null-Accepting semi-join is a new enhancement brought to the CBO by the 12cR1 release. It extends the semi-join algorithm to qualify rows from the table in the left hand side that have a null value in the join column.It kicks in for queries like the following one:

SELECT  
   count(1)
FROM t1
   WHERE(t1.n1 is null
         OR exists (SELECT null FROM t2 where t2.n1 = t1.n1)
		);

It is recognisable in execution plans via its acronym NA (Null-Accepting)

----------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |
|*  2 |   HASH JOIN SEMI NA |      |     7 |    42 |
|   3 |    TABLE ACCESS FULL| T1   |    10 |    30 |
|   4 |    TABLE ACCESS FULL| T2   |    10 |    30 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T2"."N1"="T1"."N1")

It appears in the CBO 10053 trace file as (for its HASH JOIN version):

 Best:: JoinMethod: HashNullAcceptingSemi

Don’t get confused by the NA acronym that appears in the ANTI-JOIN operation where it refers to Null-Aware rather than to Null-Accepting as shown in the following execution plan and 10053 trace file respectively:

SELECT  
   count(1)
FROM t1
   WHERE t1.n1 NOT IN (select n1 from t2);

----------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |
|*  2 |   HASH JOIN ANTI NA |      |     1 |     6 |
|   3 |    TABLE ACCESS FULL| T1   |    10 |    30 |
|   4 |    TABLE ACCESS FULL| T2   |    10 |    30 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 – access("T1"."N1"="N1")

 Best:: JoinMethod: HashNullAwareAnti

If you want to clear this confusion then remember that the Null-Accepting transformation occurs for rows that (SEMI) join while Null-Aware transformation is for rows that would not join (ANTI).

The semi-join Null-Accepting logical operation can also be serviced by the NESTED LOOP physical operation as the following demonstrates:

SELECT  
   count(1)
FROM t1
   WHERE(t1.n1 is null
         OR exists (SELECT /*+ NL_SJ */ null FROM t2 where t2.n1 = t1.n1)
		); 

------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes |
------------------------------------------------------
|   0 | SELECT STATEMENT      |      |       |       |
|   1 |  SORT AGGREGATE       |      |     1 |     6 |
|   2 |   NESTED LOOPS SEMI NA|      |     7 |    42 |
|   3 |    TABLE ACCESS FULL  | T1   |    10 |    30 |
|*  4 |    TABLE ACCESS FULL  | T2   |     7 |    21 |
------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 – filter("T2"."N1"="T1"."N1")

Best:: JoinMethod: NestedLoopNullAcceptingSemi

It is the ability, acquired by the CBO as from 12cR1, to unnest the above kind of disjunctive subquery that makes the Null-Accepting transformation possible as shown in the corresponding10053 trace file:

*****************************
Cost-Based Subquery Unnesting
*****************************
SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.
Subquery Unnesting on query block SEL$1 (#1)
SU: Performing unnesting that does not require costing.
SU: Considering subquery unnest on query block SEL$1 (#1).
SU: Checking validity of unnesting subquery SEL$2 (#2)
SU: Transforming EXISTS subquery to a join.
Registered qb: SEL$5DA710D3 0x1d225e60 (SUBQUERY UNNEST SEL$1; SEL$2)

Prior to 12cR1 it was not possible to automatically unnest the above subquery to join it with its parent block leading to the below execution plan where the inner table T2 is scanned mutliple times:

SELECT /*+ gather_plan_statistics */ 
   count(1)
FROM t1
   WHERE(t1.n1 is null
         OR exists (SELECT /*+ no_unnest */ null FROM t2 where t2.n1 = t1.n1)
        );  
---------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |
---------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      1 |
|   1 |  SORT AGGREGATE     |      |      1 |      1 |      1 |
|*  2 |   FILTER            |      |      1 |        |     10 |
|   3 |    TABLE ACCESS FULL| T1   |      1 |     10 |     10 |
|*  4 |    TABLE ACCESS FULL| T2   |      7 |      1 |      7 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("T1"."N1" IS NULL OR  IS NOT NULL))
   4 - filter("T2"."N1"=:B1)

The Bug

The Null-Accepting semi-join transformation comes, unfortunately, with a bug already identified in MyOracle Support via number 21201446. Here’s below how it can be reproduced:


SQL> select banner from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

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

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

update t1 set n1 = null where n1 in (5,6,7);

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

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

SQL> SELECT  
         count(1)
    FROM t1
      WHERE(t1.n1 is null
         OR exists 
           (SELECT null FROM t2 where nvl(t2.n1,42) = nvl(t1.n1,42))
	    );

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

----------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |
|*  2 |   HASH JOIN SEMI NA |      |     7 |    42 |
|   3 |    TABLE ACCESS FULL| T1   |    10 |    30 |
|   4 |    TABLE ACCESS FULL| T2   |    10 |    30 |
----------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 2 - filter(NVL("T2"."N1",42)=NVL("T1"."N1",42))

Using the HASH JOIN physical operation the query returns 7 rows. But it returns 10 rows when it uses the NESTED LOOP operation a shown below:

SELECT  
   count(1)
FROM t1
   WHERE(t1.n1 is null
          OR exists 
           (SELECT /*+ NL_SJ */ null FROM t2 where nvl(t2.n1,42) = nvl(t1.n1,42))
	   );

  COUNT(1)
----------
        10
		
------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes |
------------------------------------------------------
|   0 | SELECT STATEMENT      |      |       |       |
|   1 |  SORT AGGREGATE       |      |     1 |     6 |
|   2 |   NESTED LOOPS SEMI NA|      |     7 |    42 |
|   3 |    TABLE ACCESS FULL  | T1   |    10 |    30 |
|*  4 |    TABLE ACCESS FULL  | T2   |     7 |    21 |
------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   4 – filter(NVL("T2"."N1",42)=NVL("T1"."N1",42))	

The Null-Accepting semi-join transformation is driven by the following hidden parameter which , if cancelled, will workarround this bug as shown below:

SQL> alter session set "_optimizer_null_accepting_semijoin"=false;

SELECT  
   count(1)
FROM t1
   WHERE(t1.n1 is null
          OR exists 
           (SELECT null FROM t2 where nvl(t2.n1,42) = nvl(t1.n1,42))
	   );

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

----------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     3 |
|*  2 |   FILTER            |      |       |       |
|   3 |    TABLE ACCESS FULL| T1   |    10 |    30 |
|*  4 |    TABLE ACCESS FULL| T2   |     1 |     3 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("T1"."N1" IS NULL OR  IS NOT NULL))
   4 - filter(NVL("T2"."N1",42)=NVL(:B1,42))

How this has been fixed in 12cR2?

Because of the bug n° 21201446 It seems that Oracle has completely cancelled the Null-Accepting semi-join transformation in 12cR2 for both NESTED LOOP and HASH JOIN physical operations when the NVL function is applied on the join column. Here’s below why I am thinking so:

SQL> select banner from v$version where rownum=1;

BANNER
-----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SELECT  
   count(1)
FROM t1
   WHERE(t1.n1 is null
          OR exists 
           (SELECT null FROM t2 where nvl(t2.n1,42) = nvl(t1.n1,42))
	   );

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

----------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     3 |
|*  2 |   FILTER            |      |       |       |
|   3 |    TABLE ACCESS FULL| T1   |    10 |    30 |
|*  4 |    TABLE ACCESS FULL| T2   |     1 |     3 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("T1"."N1" IS NULL OR  IS NOT NULL))
   4 – filter(NVL("T2"."N1",42)=NVL(:B1,42))

In 12cR1, as we’ve shown above, the NESTED LOOP was not concerned by the bug. As from 12cR2 the NESTED LOOP SEMI JOIN is not anymore allowed to occur if a NVL function is applied on the join column as the following proves:

SELECT  
   count(1)
FROM t1
   WHERE(t1.n1 is null
          OR exists 
           (SELECT /*+ NL_SJ */ null FROM t2 where nvl(t2.n1,42) = nvl(t1.n1,42))
	   );

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

----------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |
----------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |
|   1 |  SORT AGGREGATE     |      |     1 |     3 |
|*  2 |   FILTER            |      |       |       |
|   3 |    TABLE ACCESS FULL| T1   |    10 |    30 |
|*  4 |    TABLE ACCESS FULL| T2   |     1 |     3 |
----------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / T1@SEL$1
   4 - SEL$2 / T2@SEL$2

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      DB_VERSION('12.2.0.1')
      OPT_PARAM('_optimizer_null_accepting_semijoin' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      PQ_FILTER(@"SEL$1" SERIAL)
      FULL(@"SEL$2" "T2"@"SEL$2")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("T1"."N1" IS NULL OR  IS NOT NULL))
   4 - filter(NVL("T2"."N1",42)=NVL(:B1,42))

June 17, 2017

12cR2 OR-Expansion

Filed under: CBO — hourim @ 7:47 am

In contrast to what the title seems suggesting, the OR-Expansion is not a new 12cR2 transformation. It has existed before and has been nicely explained in this article by the Oracle optimizer group. However, let me invite you to observe with me the following simple demonstration:

create table t1 (n1 number, n2 number, c1 varchar2(10));
create index t1_idx1 on t1(n1, n2);
create index t1_idx2 on t1(n2);

explain plan for
select * 
from t1
where (n1 =1 or n2 = 42);

select * from table(dbms_xplan.display);

The above query has two different execution plans in 12cR1 and 12cR2 as shown below respectively:

-- 12cR1 : 12.1.0.2.0
---------------------------------------------------------------
| Id  | Operation                            | Name    | Rows  | 
----------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |     2 |
|   1 |  CONCATENATION                       |         |       | 
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1      |     1 |
|*  3 |    INDEX RANGE SCAN                  | T1_IDX2 |     1 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1      |     1 |
|*  5 |    INDEX RANGE SCAN                  | T1_IDX1 |     1 | 
----------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("N2"=42)
   5 - access("N1"=1)
       filter(LNNVL("N2"=42))
-- 12cR2 : 12.2.0.1.0
------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  | 
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |     2 | 
|   1 |  VIEW                                 | VW_ORE_BA8ECEFB |     2 |   
|   2 |   UNION-ALL                           |                 |       |   
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1              |     1 |   
|*  4 |     INDEX RANGE SCAN                  | T1_IDX1         |     1 | 
|*  5 |    TABLE ACCESS BY INDEX ROWID BATCHED| T1              |     1 | 
|*  6 |     INDEX RANGE SCAN                  | T1_IDX2         |     1 | 
-------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("N1"=1)
   5 - filter(LNNVL("N1"=1))
   6 – access("N2"=42)

As you can see starting from Oracle 12cR2 Oracle has enhanced the OR expansion from a simple concatenation operator to a slightly more sophisticated union-all operation. The new OR-Expansion has by now been given a new name recognizable in the execution plan via the word: VW_ORE_###.

But what performance added value this transformation is supposed to bring?

Hopefully one of my real life queries gave me the answer to the above question: in contrast to its old implementation, the new enhanced 12cR2 OR-Expansion can be combined with other transformations opening, as such, a new path to the Oracle optimizer that is simply impossible to happen with the pre-12cR2 OR-Expansion.

A picture being worth a thousand of words here’s below a simple demonstration of how such a double transformation can be combined (the model comes from the previous article):

--12cR1
explain plan for
select 
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= date'2012-06-12' and t2.end_date >= date'2012-06-07'
        or (t2.id1 > 100)
     )
  );
-------------------------------------------------------
| Id  | Operation           | Name            | Rows  |
-------------------------------------------------------
|   0 | SELECT STATEMENT    |                 | 14949 |
|*  1 |  HASH JOIN OUTER    |                 | 14949 | 
|   2 |   TABLE ACCESS FULL | T1              | 10000 |
|   3 |   VIEW              | VW_DCL_C83A7ED5 |  9950 |
|*  4 |    TABLE ACCESS FULL| T2              |  9950 |
-------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID1"="ITEM_2"(+))
   4 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=TO_DATE(' 2012-06-12 
    00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T2"."END_DATE">=TO_DATE(' 2012-06-07 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

If you have read my previous article you will recognize in the above execution plan the Decorrelated Lateral view transformation via its name at line 3 (VW_DCL_C83A7ED5). At my client site this transformation was systematically triggering a full table scan so that I was obliged to cancel it as explained in the corresponding article. I would have instead loved if Oracle had gone a step forward and or-expanded this VW_DCL_C83A7ED5 lateral view in order to eliminate the OR disjunctive predicate to open the index access path possibility. Unfortunately combining these two transformation seems to be impossible by then.

That was until the arrival of the 12cR2 and its new enhanced OR-Expansion.

-- 12cR2 : 12.2.0.1.0
explain plan for
select /*+ OR_EXPAND(@"SEL$6226B99A" (1) (2)) */
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= date'2012-06-12' and t2.end_date >= date'2012-06-07'
        or (t2.id1 > 100)
     )
  );
---------------------------------------------------------
| Id  | Operation             | Name            | Rows  | 
---------------------------------------------------------
|   0 | SELECT STATEMENT      |                 | 14950 |
|*  1 |  HASH JOIN OUTER      |                 | 14950 | 
|   2 |   TABLE ACCESS FULL   | T1              | 10000 | 
|   3 |   VIEW                | VW_DCL_C83A7ED5 |  9951 |
|   4 |    VIEW               | VW_ORE_37EAC9F1 |  9951 | 
|   5 |     UNION-ALL         |                 |       | 
|*  6 |      TABLE ACCESS FULL| T2              |  9901 | 
|*  7 |      TABLE ACCESS FULL| T2              |    50 | 
---------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 1 - access("T1"."ID1"="ITEM_2"(+))
 6 - filter("T2"."ID1">100)
 7 - filter("T2"."START_DATE"<=TO_DATE(' 2012-06-12 00:00:00', 'syyyy-mm-dd 
    hh24:mi:ss') AND "T2"."END_DATE">=TO_DATE(' 2012-06-07 00:00:00', 'syyyy-mm-dd 
              hh24:mi:ss') AND LNNVL("T2"."ID1">100))

Spot now how the 12cR2 Or-Expansion(VW_ORE_37EAC9F1) has been combined with the 12cR1 Decorrelated Lateral view(VW_DCL_C83A7ED5) opening, as such, the possibility to visit t2 table via an index access path since the disjunctive or predicate has been removed from the refactored query (the index has not been used in my case but that’s only a question of cost).

Since the 12cR2 Or-Expansion is a cost based transformation (see below in the 10053 trace file) it happens that Oracle has decided to do not use it. This is why I used the hint OR_EXPAND in order to force it kicking in for the pedagogic purpose of this article.

In order to produce the above execution plan, Oracle has gone through the following SQL refactoring steps:

 --first it has OR-Expanded the t2 table query block and named it VW_ORE_37EAC9F1

(select 
   t2.id1        item_1,
   t2.product_t1 item_2,
   t2.start_date item_3,
   t2.end_date   item_4,
   t2.padding    item_5
from
   t2 t2
where t2.id1 >100
union all
select 
   t2.id1        item_1,
   t2.product_t1 item_2,
   t2.start_date item_3,
   t2.end_date   item_4,
   t2.padding    item_5
from
   t2 t2
where 
    t2.start_date <=to_date('2012-06-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
and t2.end_date   >=to_date('2012-06-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
and lnnvl(t2.id1 >100)  VW_ORE_37EAC9F1
 --it have then Decorrelated the OR-Expanded query block and named it VW_DCL_C83A7ED5 

((select 
   t2.id1        item_1,
   t2.product_t1 item_2,
   t2.start_date item_3,
   t2.end_date   item_4,
   t2.padding    item_5
from
   t2 t2
where t2.id1 >100
union all
select 
   t2.id1        item_1,
   t2.product_t1 item_2,
   t2.start_date item_3,
   t2.end_date   item_4,
   t2.padding    item_5
from
   t2 t2
where 
    t2.start_date <=to_date('2012-06-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
and t2.end_date   >=to_date('2012-06-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
and lnnvl(t2.id1 >100)
) VW_ORE_37EAC9F1
) VW_DCL_C83A7ED5

--and finally it joined the Decorrelated OR-Expanded t2 table query with t1 table
select
  t1.id1   id1,
  t1.flag1 flag1,
  t1.flag2 flag2,
  t1.n1    n1,
  t1.v1    v1,
  vw_dcl_c83a7Ed5.item_1_0 id1,
  vw_dcl_c83a7Ed5.item_2_1 product_t1,
  vw_dcl_c83a7Ed5.item_3_2 start_date,
  vw_dcl_c83a7Ed5.item_4_3 end_date,
  vw_dcl_c83a7Ed5.item_5_4 padding
from t1 t1,
     (select
        vw_ore_37eac9f1.item_1 item_1_0,
        vw_ore_37eac9f1.item_2 item_2_1,
        vw_ore_37eac9f1.item_3 item_3_2,
        vw_ore_37eac9f1.item_4 item_4_3,
        vw_ore_37eac9f1.item_4 item_5_4
      from
        (select 
           t2.id1        item_1,
           t2.product_t1 item_2,
           t2.start_date item_3,
           t2.end_date   item_4,
           t2.padding    item_5
         from
            t2 t2
         where t2.id1 >100
        union all
         select 
           t2.id1        item_1,
           t2.product_t1 item_2,
           t2.start_date item_3,
           t2.end_date   item_4,
           t2.padding    item_5
         from
           t2 t2
         where 
           t2.start_date <=to_date('2012-06-12 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
       and t2.end_date   >=to_date('2012-06-07 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
       and lnnvl(t2.id1 >100)
      )  vw_ore_37eac9f1
     ) vw_dcl_c83a7Ed5
where t1.id1 = vw_dcl_c83a7ed5.item_2_1(+);

The OR-Expansion appears first at the Legend section of the CBO 10053 trace file:

Legend
 The following abbreviations are used by optimizer trace
 CBQT  – cost-based query transformation
 ORE   - CBQT OR-Expansion
 

It can be disabled locally using the hint NO_OR_EXPAND:

explain plan for
select /*+ NO_OR_EXPAND(@"SEL$6226B99A" (1) (2)) */
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= date'2012-06-12' and t2.end_date >= date'2012-06-07'
        or (t2.id1 > 100)
     )
  );

-------------------------------------------------------
| Id  | Operation           | Name            | Rows  |
-------------------------------------------------------
|   0 | SELECT STATEMENT    |                 | 14999 | 
|*  1 |  HASH JOIN OUTER    |                 | 14999 | 
|   2 |   TABLE ACCESS FULL | T1              | 10000 |  
|   3 |   VIEW              | VW_DCL_C83A7ED5 | 10000 | 
|*  4 |    TABLE ACCESS FULL| T2              | 10000 | 
-------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID1"="ITEM_2"(+))
   4 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=TO_DATE(' 2012-06-12 
    00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T2"."END_DATE">=TO_DATE(' 2012-06-07 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

And globally via the hidden param _optimizer_cbqt_or_expansion which defaults to on:

SQL> alter system set "_optimizer_cbqt_or_expansion"= off;

explain plan for
select /*+ OR_EXPAND(@"SEL$6226B99A" (1) (2)) */
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= date'2012-06-12' and t2.end_date >= date'2012-06-07'
        or (t2.id1 > 100)
     )
  );
  
  select * from table(dbms_xplan.display);
 --------------------------------------------------------
| Id  | Operation             | Name            | Rows  | 
---------------------------------------------------------
|   0 | SELECT STATEMENT      |                 | 14999 |
|*  1 |  HASH JOIN RIGHT OUTER|                 | 14999 |
|   2 |   VIEW                | VW_DCL_C83A7ED5 | 10000 |
|*  3 |    TABLE ACCESS FULL  | T2              | 10000 |
|   4 |   TABLE ACCESS FULL   | T1              | 10000 |
---------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID1"="ITEM_2"(+))
   3 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=TO_DATE(' 2012-06-12 
    00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T2"."END_DATE">=TO_DATE(' 2012-06-07 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

SUMMARY

Starting from Oracle 12cR2 the OR-Expansion has been enhanced from the classical concatenation to a more flexible union-all operation. This article shows that one of the advantages brought by this enhancement resides in the possibility it offers to the CBO to combine the new OR-Expansion with other transformations like with the Decorrelated Lateral view. This might increase the chance to have optimal execution plan in queries using disjunctive predicates.

June 14, 2017

Optimiser non sharing reasons

Filed under: CBO,cursor sharing — hourim @ 4:28 pm

Whenever Oracle is prevented from sharing an execution plan of an already seen parent cursor, it hard parses a new plan and externalizes the corresponding non-sharing reason in the gv$sql_shared_cursor dedicated view. As of Oracle 12cR2 there are as many as 66 non-sharing reasons of which I have already explained 10 in the following series of articles I wrote for Toad World:

June 2, 2017

_optimizer_ansi_join_lateral_enhance

Filed under: CBO — hourim @ 1:51 pm

There was a question raised at my client site shorter after I troubleshooted a performance issue due to the new 12cR1 Decorrelated Lateral View transformation: Is there a way to disable this CBO transformation without sending back the optimizer to its previous version?”

It ‘s an interesting question which has triggered a brief discussion and this article.

My short answer was: I have to test and check

And the long answer is:

10053 to the rescue

As long as I was able to engineer a reproducible case where this CBO transformation is used, I decided to generate its corresponding 10053 trace file and look for the word “lateral” in this file. I found 14 hits of this word of which one seems to correlate very well with my aim:

 *************************************
  PARAMETERS WITH DEFAULT VALUES
  ******************************
Compilation Environment Dump
optimizer_mode_hinted               = false
optimizer_features_hinted           = 0.0.0
parallel_execution_enabled          = true
parallel_query_forced_dop           = 0
parallel_dml_forced_dop             = 0
parallel_ddl_forced_degree          = 0
parallel_ddl_forced_instances       = 0
_query_rewrite_fudge                = 90
optimizer_features_enable           = 12.1.0.1
_optimizer_ansi_join_lateral_enhance = true --> this one

Although there is nothing in the name of the above parameter suggesting a decorrelation of a lateral view, it is nevertheless, the unique CBO parameter whith a name including the word lateral. This suggests that setting it off will cancel this transformation. Let’s then implement this transformation and try cancelling it with setting this parameter off.

SQL> select banner from v$version where rownum =1;

BANNER
-----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

create table t1
as
 with generator as (
   select --+ materialize
      rownum id
   from dual
   connect by level <= 1000
)
select
    rownum id1,
    mod(rownum-1,2) flag1,
    mod(rownum-1,3) flag2,
    rownum          n1,
    lpad(rownum,30) v1
from
   generator v1,
   generator v2
where
   rownum <= 1e4;
    
alter table t1 add constraint t1_pk primary key (id1);
 
create table t2
as
 select
    level id1
   ,trunc((rownum+2)/2) product_t1
   ,date '2012-06-07' + mod((level-1)*5,10) + interval '5' minute start_date
   ,date '2012-06-08' + mod((level-1)*5,10) + interval '5' minute end_date
   ,rpad('xx',10) padding
from
   dual
connect by level <=1e4;
 
alter table t2 add constraint t2_pk primary key (id1);
alter table t2 add constraint t2_t1_fk foreign key (product_t1) references t1(id1);
 
-- creating an index covering the FK deadlock threat 
create index idx_t2_usr_1 on t2(product_t1, start_date);

explain plan for 
select 
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= :1 and t2.end_date >= :2
        or (t2.id1 > 100)
     )
  );
select * from table(dbms_xplan.display(format=>'advanced'));

-------------------------------------------------------
| Id  | Operation           | Name            | Rows  |
-------------------------------------------------------
|   0 | SELECT STATEMENT    |                 | 14925 |
|*  1 |  HASH JOIN OUTER    |                 | 14925 |
|   2 |   TABLE ACCESS FULL | T1              | 10000 |
|   3 |   VIEW              | VW_DCL_1B0973D4 |  9926 |
|*  4 |    TABLE ACCESS FULL| T2              |  9926 |
-------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$A7519627
   2 - SEL$A7519627 / T1@SEL$1
   3 - SEL$6226B99A / VW_LAT_AE9E49E8@SEL$AE9E49E8
   4 - SEL$6226B99A / T2@SEL$1


Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$6226B99A" "T2"@"SEL$1")
      USE_HASH(@"SEL$A7519627" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      LEADING(@"SEL$A7519627" "T1"@"SEL$1" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      NO_ACCESS(@"SEL$A7519627" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      FULL(@"SEL$A7519627" "T1"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$9EC647DD")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$AE9E49E8")
      DECORRELATE(@"SEL$BCD4421C")
      OUTLINE(@"SEL$6226B99A")
      MERGE(@"SEL$9EC647DD")
      OUTLINE(@"SEL$0E991E55")
      OUTLINE(@"SEL$BCD4421C")
      DECORRELATE(@"SEL$6226B99A")
      OUTLINE_LEAF(@"SEL$A7519627")
      DECORRELATE(@"SEL$BCD4421C")
      OUTLINE_LEAF(@"SEL$6226B99A")
      ALL_ROWS
      DB_VERSION('12.1.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID1"="ITEM_2"(+))
   4 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=:1 AND "T2"."END_DATE">=:2)

Notice the apparition of view operation (VW_DCL_1B0973D4) at line n°3 in the above execution plan. This is Oracle way to signal the presence of a decorrelated lateral view transformation. If you want to learn more about this new transformation then you can read this US patent

DECORRELATE and NO_DECORRELATE hint

Before we go on, I want to show you very quickly how we can locally disable this new transformation using the following hint:

NO_DECORRELATE(@"SEL$BCD4421C")
explain plan for  
select /*+ NO_DECORRELATE(@"SEL$BCD4421C") */
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= :1 and t2.end_date >= :2
        or (t2.id1 > 100)
     )
  );

select * from table(dbms_xplan.display);

-------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 | 10000 |
|   1 |  NESTED LOOPS OUTER                   |                 | 10000 |
|   2 |   TABLE ACCESS FULL                   | T1              | 10000 |
|   3 |   VIEW                                | VW_LAT_1B0973D4 |     1 |
|*  4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T2              |     1 |
|*  5 |     INDEX RANGE SCAN                  | IDX_T2_USR_1    |     1 |
-------------------------------------------------------------------------  

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=:1 AND "T2"."END_DATE">=:2)
   5 - access("T1"."ID1"="T2"."PRODUCT_T1")

As you can see using the NO_DECORRELATE(@”SEL$BCD4421C”) hint we cancelled the VW_DCL_1B0973D4 decorrelated lateral view since we have now a simple VW_LAT_1B0973D4 lateral view

In passing it is important to note that the outline presented above contains three hints with the word DECORRELATE:

Outline Data
-------------
  /*+
      DECORRELATE(@"SEL$BCD4421C")
      DECORRELATE(@"SEL$6226B99A")
      DECORRELATE(@"SEL$BCD4421C")
  */

Of these three hints it is only the hint referencing the query block named @”SEL$BCD4421C” that will succeed to correlated/decorrelate the lateral view. I don’t know why this hint with that query block named is repeated two times. I don’t know also why using the other hint with the query block named @”SEL$6226B99A” doesn’t decorrelate the lateral view.

Looking back into the 10053 trace file I found the following lines for query blocks SEL$BCD4421C and SEL$6226B99A respectively:

DCL: Checking validity of lateral view decorrelation SEL$BCD4421C (#1)
DCL: Passed decorrelation validity for lateral view block SEL$BCD4421C (#1)
DCL: Decorrelation of lateral view query block SEL$BCD4421C (#1).
Registered qb: SEL$6226B99A 0x8e4a5b58 (VIEW DECORRELATED SEL$BCD4421C; SEL$BCD4421C)

This tends to suggest that SEL$6226B99A represents the query block name of the lateral view and that SEL$BCD4421C represents the query block name of the decorrelated lateral view. Simply put I think that when confronted to such a kind of situation where you want to locally cancel the lateral decorrelation view than use the hint NO_DECORRELATE with the query block name that exists in the outline and doesn’t exist in the query block name given by the dbms_xplan.display_cursor:

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$A7519627
   2 - SEL$A7519627 / T1@SEL$1
   3 - SEL$6226B99A / VW_LAT_AE9E49E8@SEL$AE9E49E8
   4 - SEL$6226B99A / T2@SEL$1

Don’t ask me why not simply using the table aliases as they appear in the original query to cancel this transformation. All my attempts have been unsuccessful.

Hidden parameter

What exactly is the parameter driving this transformation?

alter session set "_optimizer_ansi_join_lateral_enhance"=false;

explain plan for 
select 
   t1.*
  ,t2.*
from
   t1
left outer join
   t2
on
  (t1.id1 = t2.product_t1
  and
     (t2.start_date <= :1 and t2.end_date >= :2
        or (t2.id1 > 100)
     )
  );

select * from table(dbms_xplan.display(format =>'+outline'));
-------------------------------------------------------------------------
| Id  | Operation                             | Name            | Rows  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 | 10000 |
|   1 |  NESTED LOOPS OUTER                   |                 | 10000 |
|   2 |   TABLE ACCESS FULL                   | T1              | 10000 |
|   3 |   VIEW                                | VW_LAT_1B0973D4 |     1 |
|*  4 |    TABLE ACCESS BY INDEX ROWID BATCHED| T2              |     1 |
|*  5 |     INDEX RANGE SCAN                  | IDX_T2_USR_1    |     1 |
-------------------------------------------------------------------------

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$BCD4421C" "T2"@"SEL$1")
      INDEX_RS_ASC(@"SEL$BCD4421C" "T2"@"SEL$1" ("T2"."PRODUCT_T1" "T2"."START_DATE"))
      USE_NL(@"SEL$0E991E55" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      LEADING(@"SEL$0E991E55" "T1"@"SEL$1" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      NO_ACCESS(@"SEL$0E991E55" "VW_LAT_AE9E49E8"@"SEL$AE9E49E8")
      FULL(@"SEL$0E991E55" "T1"@"SEL$1")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$9EC647DD")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$AE9E49E8")
      MERGE(@"SEL$9EC647DD")
      OUTLINE_LEAF(@"SEL$0E991E55")
      OUTLINE_LEAF(@"SEL$BCD4421C")
      ALL_ROWS
      OPT_PARAM('_optimizer_ansi_join_lateral_enhance' 'false')
      DB_VERSION('12.1.0.1')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("T2"."ID1">100 OR "T2"."START_DATE"<=:1 AND "T2"."END_DATE">=:2)
   5 – access("T1"."ID1"="T2"."PRODUCT_T1")

Notice again how we have succeeded to cancel the decorrelated lateral view transformation at a global level by setting off the hidden parameter _optimizer_ansi_join_lateral_enhance.

SUMMARY
In summary, does a decorrelated lateral view causes always a performance issue? No, not necessarily always. However if you fill in need of disabling it then you can:

  • Use the corresponding no_decorrelate hint with the correct query block name for a local effect
  • Set off the _optimizer_ansi_join_lateral_enhance parameter for a global effect with the usual warning when changing a hidden parameter.

October 23, 2013

CBO and unusable unique index

Filed under: CBO — hourim @ 6:50 am

Very recently a question came up on oracle-list where the original poster was wondering about two things (a) how could a refresh on materialized view allows duplicate key to be possible in the presence of a unique index and (b) he was struggling about a particular select which is giving a wrong results.

The answer to the first question is easy and I have already blogged about it. He was refreshing the materialized view using a FALSE value for the parameter atomic refresh. With this particular refresh parameter, the materialized view is refreshed  using a rapid truncate table followed by a direct path insert. Direct path load as shown in my blog, will silently disable the unique index allowing duplicate keys to be accepted. Thought that this seems to be true (in this context of materialized view refresh) only in 11gR2. The preceding release (10gR2) is not allowing duplicate keys during this kind of refresh as I’ve shown in my answer in the oracle-list forum. Does this mean that 10gR2 is not direct path loading when atomic refresh is set to FALSE? I have to check.

But what motivated the current blog article is the second question. See with me

SQL> create table a(id int,val number);
Table created.

SQL> insert into a select 1, 1 from dual;
1 row created.

SQL> create table b(id int);
Table created.

SQL> create unique index uq_b on b(id);
Index created.

Then I will use a sqlloader to load data into table b using a direct path load in order to silently disable the unique index. The control file(c.ctl) I will be using resembles to:

LOAD DATA
INFILE *
REPLACE
INTO TABLE B
(id position(1:1) TERMINATED BY ",")
BEGINDATA
1;
1;

And now I will launch the sqlloader

C:\>sqlldr user/paswd@database control=c.ctl direct=true

SQL*Loader: Release 10.2.0.3.0 - Production on Tue Oct 22 16:46:06 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Load completed - logical record count 3.

What do you think it happens to the unique index and to the table b after this direct path load?


SQL> select index_name, status from user_indexes where index_name ='UQ_B';

INDEX_NAME                     STATUS
------------------------------ --------
UQ_B                           UNUSABLE

SQL> select count(1) from b;

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

The unique index has been disabled and there are duplicate keys in table b.

So far so good.

Let’s now start exploring the Original Poster queries problem

SQL> select a.*
from a, b
where a.id = b.id(+);

ID        VAL
---------- ----------
1          1
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    15 (100)|          |
|   1 |  TABLE ACCESS FULL| A    |     1 |    26 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2) 

The CBO knows that there is a unique index on b(id). And, as far as there is one id in table a, the CBO, obviously, assumes that there will be only one record for the corresponding id in table b. This is why table b is not present in the execution plan by the way. Unfortunately the unique index has been disabled by the direct path load and has permitted the presence of duplicate record in table b. This is the reason why the query is producing a wrong result.

If we force the CBO to access the table b the result is however correct

SQL>  select a.*,b.id
from a, b
where a.id = b.id(+);

ID        VAL         ID
---------- ---------- ----------
1          1          1
1          1          1

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    36 (100)|          |
|*  1 |  HASH JOIN OUTER   |      |     2 |    78 |    36   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A    |     1 |    26 |    15   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     2 |    26 |    20   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")

Note
-----
- dynamic sampling used for this statement (level=2)

It is clear that the CBO is not looking the unique index status during the optimization (plan generation) phase.

But, what if instead of the unusable unique index, we have a disabled unique constraint? Will the CBO consider the status of the unique constraint in this case?

SQL> alter table b add constraint b_uk unique (id) disable;
Table altered.

SQL> select a.*
from a, b
where a.id = b.id(+);

ID        VAL
---------- ----------
1          1

Oups. The CBO is still wrong. What if we drop the culprit index?

SQL> drop index uq_b;
Index dropped.

SQL> select a.*
from a, b
where a.id = b.id(+);

ID        VAL
---------- ----------
1          1
1          1

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    36 (100)|          |
|*  1 |  HASH JOIN OUTER   |      |     2 |    18 |    36   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| A    |     1 |     6 |    15   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| B    |     2 |     6 |    20   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")

Which finally gives a correct result.

Bottom line: always make sure your unique indexes are usable.

UPDATE 27-12-2013 :  following a discussion here,Ted from the Oracle Global Customer Support, pointed me to the bug number 17533502 which seems very close the problem exposed here. Ted also pointedme to a work arround solution as shown below:

SQL> select * from v$version where rownum=1;

BANNER                                                                       CON_ID
---------------------------------------------------------------------------- -------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0

SQL> select a.*
 2 from a, b
 3 where a.id = b.id(+);

ID   VAL
--- ----
 1   1

SQL> ALTER SESSION SET "_optimizer_join_elimination_enabled" = false;

Session altered.

SQL> select a.*
 2 from a, b
 3 where a.id = b.id(+);

ID  VAL
--- -----
 1  1
 1  1

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)