Manual Concatenation and LNNVL
July 23, 2012 3 Comments
A quick example about things to consider when considering rewriting a SQL OR condition to a UNION/UNION ALL, as pointed out to me by Jonathan Lewis referencing this post on this recent OTN forum thread.
At the heart of the issue are:
- how to deal with the possibility of duplicates
- the potential impact of NULLS
The impact of NULLS is best described by Jonathan in the article already mentioned so I’m going to use a noddy example to illustrate:
- the problem of duplicates
- why suggesting a UNION is not an appropriate generic approach (although it may work for specific examples)
- why that generic approach should be a UNION ALL plus LNNVL
So… if, for whatever reason, we want to rewrite this statement:
select * from t2, t1 where t2.col1 = t1.par or t2.col1 = t1.child;
If this is our setup that includes a noddy duplicate:
SQL> create table t1 2 (par number 3 ,child number); Table created. SQL> create table t2 2 (col1 number 3 ,col2 varchar2(1)); Table created. SQL> insert into t1 values (1,1); 1 row created. SQL> insert into t1 values (1,1); 1 row created. SQL> insert into t2 values (1,'A'); 1 row created. SQL> select * from t1; PAR CHILD ---------- ---------- 1 1 1 1 2 rows selected. SQL> select * from t2; COL1 C ---------- - 1 A 1 row selected. SQL>
Such that our target statement returns this:
SQL> select * 2 from t2, t1 3 where t2.col1 = t1.par 4 or t2.col1 = t1.child; COL1 C PAR CHILD ---------- - ---------- ---------- 1 A 1 1 1 A 1 1 2 rows selected. SQL>
We can see that rewriting with a UNION is not equivalent to the original:
SQL> select * 2 from t2, t1 3 where t2.col1 = t1.par 4 union 5 select * 6 from t2, t1 7 where t2.col1 = t1.child; COL1 C PAR CHILD ---------- - ---------- ---------- 1 A 1 1 1 row selected. SQL>
And if we use a UNION ALL:
SQL> select * 2 from t2, t1 3 where t2.col1 = t1.par 4 union all 5 select * 6 from t2, t1 7 where t2.col1 = t1.child; COL1 C PAR CHILD ---------- - ---------- ---------- 1 A 1 1 1 A 1 1 1 A 1 1 1 A 1 1 4 rows selected.
Then we need LNNVL to eliminate the duplicates from the second branch, the lower half of the statement:
SQL> select * 2 from t2, t1 3 where t2.col1 = t1.par 4 union all 5 select * 6 from t2, t1 7 where t2.col1 = t1.child 8 and lnnvl(t1.par=t1.child); COL1 C PAR CHILD ---------- - ---------- ---------- 1 A 1 1 1 A 1 1 2 rows selected. SQL>
Recent Comments