I serendipitously discovered a special case where the optimizer fails to perform subquery unnesting (SU) for anti join (AJ), and consequently comes up with an inferior execution plan.<\/p>\n
We’ll need two tables to reproduce the problem (tested on Oracle database version 19.10.0.0.210119):<\/p>\n
drop table t1 ;\ndrop table t2 ;\n\ncreate table t1 ( n1 number, n2 number ) ;\ninsert into t1 select level, 1 from dual connect by level <= 1000 ;\n\ncreate table t2 ( n1 number ) ;\ninsert into t2 select level from dual connect by level <= 100 ;\n\ncommit ;\n\nexec dbms_stats.gather_table_stats(null,'T1') ;\nexec dbms_stats.gather_table_stats(null,'T2') ;<\/code><\/pre>\nThe optimizer produces a good plan with AJ for the following query:<\/p>\n
explain plan for\nselect t1.n1 \n from t1 \n where not exists ( \n select 0<\/span>\n from t2 \n where t1.n1 = t2.n1\n ) ;<\/code><\/pre>\n----------------------------------------+-----------------------------------+\n| Id | Operation | Name | Rows | Bytes | Cost | Time |\n----------------------------------------+-----------------------------------+\n| 0 | SELECT STATEMENT | | | | 6 | |\n| 1 | HASH JOIN RIGHT ANTI<\/span> | | 901 | 6307 | 6<\/span> | 00:00:01 |\n| 2 | TABLE ACCESS FULL | T2 | 100 | 300 | 3 | 00:00:01 |\n| 3 | TABLE ACCESS FULL | T1 | 1000 | 4000 | 3 | 00:00:01 |\n----------------------------------------+-----------------------------------+\nPredicate Information:\n----------------------\n1 - access(\"T1\".\"N1\"=\"T2\".\"N1\")<\/code><\/pre>\nIt’s worth noting, that SU transformation is a prerequisite for AJ:<\/p>\n
SU: Checking validity of unnesting subquery SEL$2 (#2)\nSU: Passed validity checks.\nSU: Unnesting subquery query block SEL$2 (#2)\nSU: Transform ALL\/NOTEXISTS subquery into a regular antijoin.\nSU: Merge subquery block SEL$2 (#2) into outer QB SEL$1 (#1)<\/code><\/pre>\nLet’s make a small syntactic change in the subquery – replace the constant 0<\/span> with the column t1.n2<\/span>:<\/p>\nexplain plan for\nselect t1.n1 \n from t1 \n where not exists ( \n select t1.n2<\/span>\n from t2 \n where t1.n1 = t2.n1\n ) ;<\/code><\/pre>\nt1.n2<\/span> in the subquery doesn’t project, so this shouldn’t change anything. Simply put, both queries are equivalent.<\/p>\nBut the execution plan changed for worse – it uses FILTER<\/span> and has a higher host:<\/p>\n--------------------------------------+-----------------------------------+\n| Id | Operation | Name | Rows | Bytes | Cost | Time |\n--------------------------------------+-----------------------------------+\n| 0 | SELECT STATEMENT | | | | 1504<\/span> | |\n| 1 | FILTER<\/span> | | | | | |\n| 2 | TABLE ACCESS FULL | T1 | 1000 | 7000 | 3 | 00:00:01 |\n| 3 | TABLE ACCESS FULL | T2 | 1 | 3 | 3 | 00:00:01 |\n--------------------------------------+-----------------------------------+\nPredicate Information:\n----------------------\n1 - filter( IS NULL)\n3 - filter(\"T2\".\"N1\"=:B1)<\/code><\/pre>\nThe column t1.n2<\/span>, which was referenced in the SELECT clause, prevented SU:<\/p>\n942 SU: SU bypassed: Correlation column in SELECT, GROUP BY, or ORDER BY.\n943 SU: Validity checks failed.<\/code><\/pre>\nAt some point, the optimizer replaced t1.n2<\/span> with a constant, but it was too late then, because it had already bypassed SU.<\/p>\n1069 NOT EXISTS (SELECT 0<\/span> FROM \"T2\" \"T2\")<\/code><\/pre>\nIn other words, ANTI JOIN is feasible, but it didn’t happen, because of the order in which different query transformations were performed.<\/p>\n
In conclusion, make sure that you really use a constant in a NOT EXISTS subquery, because optimizer will generate a subpar plan with FILTER instead of AJ otherwise.<\/p>\n","protected":false},"excerpt":{"rendered":"
Columns instead of constants in the SELECT clause of a NOT EXISTS subquery can lead to bad execution plans. Continue Reading