Toad World Blog

Why Some SQL Tuning Rewrites are not always Correct ?

Apr 10, 2014 8:39:00 PM by Richard To

A junior developer came to me recently and asked me a SQL transformation question. He gave me an example of a SQL statement similar to this one:

select * from TableA A where A.KeyCol/10 = 12345

The problem with this statement is that in the where clause, the operation on A.KeyCol will invalidate any indexes that use A.KeyCol. His suggestion was to rewrite the SQL statements like the following to enable an index search for A.KeyCol:

select * from TableA A where A.KeyCol = 12345*10

It looks like this transformation is perfect and simple. By shifting the operation to the other side, A.KeyCol is not modified and will use an index search. But why didn't we implement this simple transformation rule in our SQL Optimizer? I asked him if this transformation is so easy then why don’t the database internal SQL optimizers implement it in their internal SQL rewrite? Then I told him that DB2 LUW optimizer has the strongest internal rewrite ability among all database platforms, but even they don't have this transformation rule in place. He was puzzled and I gave him the following two SQL statements to him and asked him to test it in our QA database :

select count(*) from department
where DPT_MANAGER/3 =278647/3;
 
select count(*) from department
where DPT_MANAGER =278647/3*3;

He came back to me in a few minutes with the following results tested in Toad. Since it is obvious that there is no way to guarantee that the transformed SQL is semantically equivalent to the original SQL statement, this is why no database internal SQL optimizer will take the risk to transform these mathematically equivalent expressions automatically. So, if you find any programmers who have written these kinds of SQL statements, please don't blame them since at first glance it appears they have a valid reason.

Tags: Toad for Oracle Oracle SQL Optimizer SQL Optimizer for Oracle

Richard To

Written by Richard To

Richard specializes in using AI technique to solve database performance problems.