Are you familiar with de-correlated lateral view query transformation (DCL)? If not, I recommend first reading Mohamed Houri’s article on this topic<\/a>.<\/p>\n 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.<\/p>\n Here’s the slightly changed query from Mohamed’s test case:<\/p>\n The plan in 12c is:<\/p>\n The optimizer rewrote the inner table as a lateral subquery:<\/p>\n However, it bypassed the DCL:<\/p>\n 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!<\/p>\n The handling has significantly improved in 19c. No lateral subqeries in the execution plan:<\/p>\n The enhancement is implemented with the following bug fix:<\/p>\n 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<\/a> doesn’t benefit from the improvement either.<\/p>\n 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:<\/p>\n 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.<\/p>\n Many thanks to Ivica Arsov<\/a> for identifying the unpublished bug 31009032\tFIX FOR BUGS 28012754 AND 30776676 DO NOT WORK FOR DML AND DDL OPERATIONS<\/i> as the root cause. The patch 31683181: MERGE ON DATABASE RU 19.7.0.0.0 OF 30776676 31009032<\/i> resolves the issue. Don’t forget to explicitly enable the bug fix (that’s not mentioned in the README):<\/p>\n 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<\/i>.<\/p>\n We’re waiting on the merge request.<\/p>\n The patch 31939459: MERGE ON DATABASE RU 19.7.0.0.0 OF 30786641 31009032<\/i> contains both bug fixes. It works well.<\/p>\n The problem is fixed in 21c.<\/p>\n","protected":false},"excerpt":{"rendered":" Suboptimal handling of lateral views has improved in Oracle 19c. Continue Reading select t1.flag2, t2.padding\n from t1 left outer join t2 on\n (\n t1.id1 = t2.product_t1 and\n (\n t2<\/span>.start_date <= sysdate and\n t1<\/span>.flag1 in (1,2)\n )\n);<\/code><\/pre>\n----------------------------------------------------------+-----------------------------------+\n| Id | Operation | Name | Rows | Bytes | Cost | Time |\n----------------------------------------------------------+-----------------------------------+\n| 0 | SELECT STATEMENT | | | | 29K | |\n| 1 | MERGE JOIN OUTER | | 10K | 166K | 29K | 00:06:01 |\n| 2 | TABLE ACCESS FULL | T1 | 10K | 98K | 22 | 00:00:01 |\n| 3 | BUFFER SORT | | 1 | 7 | 29K | 00:06:01 |\n| 4 | VIEW | VW_LAT_C83A7ED5<\/span>| 1 | 7 | 3 | 00:00:01 |\n| 5 | FILTER | | | | | |\n| 6 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 27 | 3 | 00:00:01 |\n| 7 | INDEX RANGE SCAN | IDX_T2_USR_1 | 1 | | 2 | 00:00:01 |\n----------------------------------------------------------+-----------------------------------+\nQuery Block Name \/ Object Alias(identified by operation id):\n------------------------------------------------------------\n 1 - SEL$F7AF7B7D\n 2 - SEL$F7AF7B7D \/ T1@SEL$1\n 4 - SEL$BCD4421C \/ VW_LAT_AE9E49E8@SEL$AE9E49E8<\/span>\n 5 - SEL$BCD4421C\n 6 - SEL$BCD4421C \/ T2@SEL$1\n 7 - SEL$BCD4421C \/ T2@SEL$1<\/code><\/pre>\nSELECT \"T1\".\"FLAG2\" \"FLAG2\",\"VW_LAT_AE9E49E8\".\"ITEM_4_3\" \"PADDING\" FROM \"VBNOV\".\"T1\" \"T1\", LATERAL<\/span>( (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<\/span>\"<\/code><\/pre>\nDCL: Checking validity of lateral view decorrelation SEL$BCD4421C (#1)\nDCL: Bypassed: view has non-well-formed predicate\nDCL: Failed decorrelation validity for lateral view block SEL$BCD4421C (#1)<\/code><\/pre>\n19c<\/h1>\n
------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 13751 | 1074K| 42 (3)| 00:00:01 |\n|* 1 | HASH JOIN RIGHT OUTER| | 13751 | 1074K| 42 (3)| 00:00:01 |\n|* 2 | TABLE ACCESS FULL | T2 | 5001 | 170K| 19 (0)| 00:00:01 |\n| 3 | TABLE ACCESS FULL | T1 | 10000 | 439K| 22 (0)| 00:00:01 |\n------------------------------------------------------------------------------<\/code><\/pre>\nselect sql_feature,description, optimizer_feature_enable\n from v$system_fix_control where bugno = 28012754 ;\n\nSQL_FEATURE DESCRIPTION OPTIMIZER_FEATURE_ENABLE\n---------------------------------------------------------------- ---------------------------------------------------------------- -------------------------\nQKSFM_TRANSFORMATION_28012754 LATERAL view merge enhancement 19.1.0<\/code><\/pre>\nWorkaround<\/h1>\n
select t1.flag2, t2.padding\n from t1 left outer join t2 on\n (\n t1.id1 = t2.product_t1 and\n t2.start_date <= sysdate\n ) where t1.flag1 in (1,2) \nunion all\nselect t1.flag2, null\n from t1 \nwhere t1.flag1 not in (1,2) ;\n\n----------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n----------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 14999 | 351K| 63 (0)| 00:00:01 |\n| 1 | UNION-ALL | | | | | |\n|* 2 | HASH JOIN OUTER | | 9999 | 322K| 41 (0)| 00:00:01 |\n|* 3 | TABLE ACCESS FULL| T1 | 5001 | 50010 | 22 (0)| 00:00:01 |\n|* 4 | TABLE ACCESS FULL| T2 | 10000 | 224K| 19 (0)| 00:00:01 |\n|* 5 | TABLE ACCESS FULL | T1 | 5000 | 30000 | 22 (0)| 00:00:01 |\n----------------------------------------------------------------------------<\/code><\/pre>\nSummary<\/h1>\n
Updates<\/h1>\n
August 18, 2020<\/h2>\n
alter system set \"_fix_control\"='31009032:ON';<\/code><\/pre>\nOctober 15, 2020<\/h2>\n
September 15, 2021: Oracle 21c<\/h2>\n