Search

Top 60 Oracle Blogs

Recent comments

The challenge of optimization

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 :-)