In SQLite: Join And Group vs Group By And Join, I mentioned about the need to rewrite the SQL to make it perform better. In this post, I am going to discuss a few more that I did.
Note that when doing performance tuning, rewriting of the SQL to make it perform is one of the ways while the other way is to create the appropriate indexes if applicable. Restructuring the query is needed from smaller databases to large and commercial databases. However, the big guys like Oracle are capable of handling a lot more scenarios. Here are two of my cases that I had to rewrite
1. co-related sub-query:
select … from p where … abc in (select xyz from ch where p.l = ch.m);
is changed to
select … from p,ch where … p.l = ch.m and abc = xyz;
2. transitive filter:
select … from p where … abc = (select id from c where n = ?) and xyz in (select lid from ch where id = abc);
Here, the index is on xyz. So, changing it to
select … from p,ch where … abc = lid and id = (select id from c where n = ?) and xyz = lid;
worked because the plan would then use the index on id and index on xyz.
SQL tuning is all about understanding the access paths and helping the databases a bit in case they are not smart enough to figure out things themselves.