Know your SQL optimization tricks

I’m not a database specialist. I have my fair knowledge of database design, can create simple and even quite complex SQL queries and have knowledge of some of the typical relational database concepts such as Stored Procedures, Triggers, Transactions, etc…

But one thing I don’t know anything about is database and SQL optimization. And though, it is a VERY important issue, especially when you’re working on large distributed and/or enterprise applications. Last week, I got again reminded of this.

I had been struggling with a SQL query that just took an awful lot of time to run. I wrote 3 versions of it, all taking the same cost. The query looks like this (I changed the table and field names of course).

select cu.cuid
from cuall cu 
where cu.cuid in (
    select co.cuid
    from coall co, coes cse
    where co.coid = cse.coid
    and cse.sde = 1053 
    and SUBSTR (NVL (cse.csng, 'x'), -1) in ('a','s')
    and cse.csno = (select max(cse2.cno) 
                    from coes cse2 
                    where cse.coid = cse2.coid)
)

It took 38 minutes to run the query and the execution map showed a cost of 62800.

Oracle SQL hints

I have to say, the query is developed on an Oracle database. Now I remembered something a colleague of mine showed me just a few week ago, called Oracle SQL hints. These hints are small pieces of text that looks like comments and are inserted right after the select word of an SQL statement. Oracle uses these hints to optimize the execution of the query.
There are quite a lot of hints, organized in different categories, but the one I remembered was the PUSH_SUBQ. This hint will try (if possible) to execute first the subquery before any other operation.

Adding this hint to my query gave me the following results:

select /*+ push_subq */ cu.cuid
from cuall cu 
where cu.cuid in (
    select co.cuid
    from coall co, coes cse
    where co.coid = cse.coid
    and cse.sde = 1053 
    and SUBSTR (NVL (cse.csng, 'x'), -1) in ('a','s')
    and cse.csno = (select max(cse2.cno) 
                    from coes cse2 
                    where cse.coid = cse2.coid)
)

It took still 10 minutes to run, but the execution has now only a cost of 1744. As you can see, a huge difference than before.

Conclusion

Database developing is something very large and mostly underestimated by developers. This is true in all database environments but especially in Oracle. I’m very glad to have touched it during this project, but I guess I’ll be leaving the real die-hard database and layer developing to those who love to specialize themselves in it and make a career out of it. For my side, I’m missing too much the architecture part, the layers of abstractions and most of all the object orientation.