With every release of Oracle, more and more power comes to the optimizer. Many of these are new features (such as adaptive cursor sharing, adaptive optimization, dynamic sampling, etc)…but also within the "core" of the optimizer, there are continuing efforts to transform and interrogate SQL’s to try derive a ‘smarter’ query and hence hopefully a smarter plan.

Its always a balancing act…how much can you re-jig a query without running the risk of actually changing what the query does…

Here’s an example of where that balance is slightly wrong in 12c

SQL> drop table T; Table dropped. SQL> create table T ( seq number primary key, x number, y number , u number); Table created. SQL> declare 2 s number := 1; 3 begin 4 for i in 1 .. 9 loop 5 for j in i+1 .. 10 loop 6 insert into T values (s,i,j, 0); 7 s := s + 1; 8 end loop; 9 end loop; 10 end; 11 / PL/SQL procedure successfully completed.

So we’ve put 45 rows into the table, the values of which are not particularly important. We are going to compare these rows with those in another table

SQL> drop table F; Table dropped. SQL> create table F ( g number, n number, x number, y number ); Table created. SQL> insert into F values (1,1,3,4); 1 row created. SQL> insert into F values (1,1,5,7); 1 row created. SQL> insert into F values (1,1,7,8); 1 row created. SQL> select * from F; G N X Y ---------- ---------- ---------- ---------- 1 1 3 4 1 1 5 7 1 1 7 8

So lets look at all the possible values in F in either X or Y columns.

SQL> select x from F where g = 1 union all select y from F where g = 1; X ---------- 3 5 7 4 7 8 6 rows selected.

So before we bring T and F into a query, lets use those values above to find all the rows in T for which X and Y both so not match that list

SQL> select seq,x,y 2 from T 3 where u = 0 4 and x not in ( 3,5,7,4,7,8) 5 and y not in ( 3,5,7,4,7,8); SEQ X Y ---------- ---------- ---------- 1 1 2 5 1 6 8 1 9 9 1 10 13 2 6 16 2 9 17 2 10 38 6 9 39 6 10 45 9 10 10 rows selected.

So at this point – you can see that all appears well. Now… I replace the: and x not in ( 3,5,7,4,7,8) and y not in ( 3,5,7,4,7,8); with and x not in ( select x from F where g = 1 union all select y from F where g = 1) and y not in ( select x from F where g = 1 union all select y from F where g = 1); which should yield the same result, because as we saw earlier, "select x from F where g = 1 union all select y from F where g = 1" yields "3,5,7,4,7,8" But we dont…we get ADDITIONAL rows.

SQL> select seq,x,y 2 from T 3 where u = 0 4 and x not in ( select x from F where g = 1 union all select y from F where g = 1) 5 and y not in ( select x from F where g = 1 union all select y from F where g = 1); SEQ X Y ---------- ---------- ---------- 1 1 2 2 1 3 3 1 4 4 1 5 5 1 6 6 1 7 7 1 8 8 1 9 9 1 10 10 2 3 11 2 4 12 2 5 13 2 6 14 2 7 15 2 8 16 2 9 17 2 10 36 6 7 37 6 8 38 6 9 39 6 10 45 9 10

Somewhere in the optimization, we re-jigged just that little too much…and we got wrong results.

The is a good lesson for you own programs…optimize plenty…but not too much :-)

- January 2020 (68)
- December 2019 (43)
- November 2019 (43)
- October 2019 (61)
- September 2019 (36)
- August 2019 (40)
- July 2019 (45)
- June 2019 (37)
- May 2019 (43)
- April 2019 (43)
- March 2019 (55)
- February 2019 (25)
- January 2019 (35)
- December 2018 (39)
- November 2018 (53)
- October 2018 (69)
- September 2018 (36)
- August 2018 (68)
- July 2018 (58)
- June 2018 (59)
- May 2018 (64)
- April 2018 (40)
- March 2018 (61)
- February 2018 (67)
- January 2018 (57)
- December 2017 (37)
- November 2017 (45)
- October 2017 (57)
- September 2017 (46)
- August 2017 (61)

## Recent comments

2 years 1 day ago

2 years 12 weeks ago

2 years 16 weeks ago

2 years 17 weeks ago

2 years 22 weeks ago

2 years 43 weeks ago

3 years 11 weeks ago

3 years 41 weeks ago

4 years 25 weeks ago

4 years 25 weeks ago