12c

Are you familiar with de-correlated lateral view query transformation (DCL)? If not, I recommend first reading Mohamed Houri’s article on this topic.

The presence of both AND and OR predicates in the join condition is a prerequisite for a lateral correlated subquery. I’ll be focusing on a very specific case – when the join predicate contains filters on both outer and inner table. In particular, I’ll show what has improved in Oracle 19c.

Here’s the slightly changed query from Mohamed’s test case:

select t1.flag2, t2.padding
  from t1 left outer join t2 on
  (
    t1.id1 = t2.product_t1 and
    (
      t2.start_date <= sysdate and
      t1.flag1 in (1,2)
    )
);

The plan in 12c is:

----------------------------------------------------------+-----------------------------------+
| Id  | Operation                        | Name           | Rows  | Bytes | Cost  | Time      |
----------------------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT                 |                |       |       |   29K |           |
| 1   |  MERGE JOIN OUTER                |                |   10K |  166K |   29K |  00:06:01 |
| 2   |   TABLE ACCESS FULL              | T1             |   10K |   98K |    22 |  00:00:01 |
| 3   |   BUFFER SORT                    |                |     1 |     7 |   29K |  00:06:01 |
| 4   |    VIEW                          | VW_LAT_C83A7ED5|     1 |     7 |     3 |  00:00:01 |
| 5   |     FILTER                       |                |       |       |       |           |
| 6   |      TABLE ACCESS BY INDEX ROWID | T2             |     1 |    27 |     3 |  00:00:01 |
| 7   |       INDEX RANGE SCAN           | IDX_T2_USR_1   |     1 |       |     2 |  00:00:01 |
----------------------------------------------------------+-----------------------------------+
Query Block Name / Object Alias(identified by operation id):
------------------------------------------------------------
 1 - SEL$F7AF7B7D
 2 - SEL$F7AF7B7D         / T1@SEL$1
 4 - SEL$BCD4421C         / VW_LAT_AE9E49E8@SEL$AE9E49E8
 5 - SEL$BCD4421C
 6 - SEL$BCD4421C         / T2@SEL$1
 7 - SEL$BCD4421C         / T2@SEL$1

The optimizer rewrote the inner table as a lateral subquery:

SELECT "T1"."FLAG2" "FLAG2","VW_LAT_AE9E49E8"."ITEM_4_3" "PADDING" FROM "VBNOV"."T1" "T1", LATERAL( (SELECT "T2"."PADDING" "ITEM_4_3" FROM "VBNOV"."T2" "T2" WHERE "T1"."ID1"="T2"."PRODUCT_T1" AND "T2"."START_DATE"<=SYSDATE@! AND ("T1"."FLAG1"=1 OR "T1"."FLAG1"=2)))(+) "VW_LAT_AE9E49E8"

However, it bypassed the DCL:

DCL: Checking validity of lateral view decorrelation SEL$BCD4421C (#1)
DCL: Bypassed: view has non-well-formed predicate
DCL: Failed decorrelation validity for lateral view block SEL$BCD4421C (#1)

The entry “non-well-formed predicate” appears when we mix outer and inner table predicates in the join clause. But, what’s the point of producing a lateral subquery which can’t be transformed? This can have a detrimental impact on performance!

19c

The handling has significantly improved in 19c. No lateral subqeries in the execution plan:

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      | 13751 |  1074K|    42   (3)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT OUTER|      | 13751 |  1074K|    42   (3)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL   | T2   |  5001 |   170K|    19   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | T1   | 10000 |   439K|    22   (0)| 00:00:01 |
------------------------------------------------------------------------------

The enhancement is implemented with the following bug fix:

select sql_feature,description, optimizer_feature_enable
  from v$system_fix_control where bugno = 28012754 ;

SQL_FEATURE                                                      DESCRIPTION                                                      OPTIMIZER_FEATURE_ENABLE
---------------------------------------------------------------- ---------------------------------------------------------------- -------------------------
QKSFM_TRANSFORMATION_28012754                                    LATERAL view merge enhancement                                   19.1.0

There are, unfortunately, some limitations. The improvement is implemented only for stand-alone queries. CTAS and insert/select perform poorly, like in 12c. The case reported by Dominic Brooks doesn’t benefit from the improvement either.

Workaround

The optimizer produces a lateral subquery only if there are both AND and OR predicates in the JOIN clause. We can rewrite the query, so that those prerequisites aren’t fulfilled, for example:

select t1.flag2, t2.padding
  from t1 left outer join t2 on
  (
    t1.id1 = t2.product_t1 and
    t2.start_date <= sysdate
  ) where t1.flag1 in (1,2) 
union all
select t1.flag2, null
  from t1 
where t1.flag1 not in (1,2) ;

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      | 14999 |   351K|    63   (0)| 00:00:01 |
|   1 |  UNION-ALL          |      |       |       |            |          |
|*  2 |   HASH JOIN OUTER   |      |  9999 |   322K|    41   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| T1   |  5001 | 50010 |    22   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| T2   | 10000 |   224K|    19   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL | T1   |  5000 | 30000 |    22   (0)| 00:00:01 |
----------------------------------------------------------------------------

Summary

The optimizer sometimes generates a lateral subquery which it can’t de-correlate. A large query block named VW_LAT_.. might be a sign of a bypassed de-correlation and a poor execution plan. Although the handling has improved in 19c, there are still suboptimal cases. We can rewrite such queries to avoid the preconditions for lateral subqueries.

Updates

August 18, 2020

Many thanks to Ivica Arsov for identifying the unpublished bug 31009032 FIX FOR BUGS 28012754 AND 30776676 DO NOT WORK FOR DML AND DDL OPERATIONS as the root cause. The patch 31683181: MERGE ON DATABASE RU 19.7.0.0.0 OF 30776676 31009032 resolves the issue. Don’t forget to explicitly enable the bug fix (that’s not mentioned in the README):

alter system set "_fix_control"='31009032:ON';

Unfortunately, there’s a conflict with the patch for another bug affecting the lateral views performance: Bug 30786641 : QUERY PERFORMANCE REGRESSION AFTER UPGRADING FROM 11.2.0.4 TO 19.5.

We’re waiting on the merge request.

October 15, 2020

The patch 31939459: MERGE ON DATABASE RU 19.7.0.0.0 OF 30786641 31009032 contains both bug fixes. It works well.

September 15, 2021: Oracle 21c

The problem is fixed in 21c.