Here are the resources I find most useful for Oracle SQL query optimisartion, ordered from basic to advanced.
Guide to understanding the basics of indexing and the various merges in line with their effects on execution plans
How-to manual from Oracle themselves on how to use autotrace in SQLPlus
The most complete reference on Oracle hints I’ve come across
A collection of specific, and advanced, query tuning techniques
Also, here is the 1979 seminal paper from P. Selinger of IBM, which forms the roots of access plan optimisation in most current day databases; and addresses the basic concepts that need to be taken into account when optimising queries.
Access path selection in a relational database management system
This one here does not have anything to do directly with query optimisation, but I found that attempting to optimise queries without first understanding the algorithms behind them is a fool’s errand. So here is a great post on the basic algorithms behind nested loop join, merge join and hash join (code in C#).
The Reinvigorated Programmer (Mike Taylor) quotes from Jon Bentley’s 1986 classic Programming Pearls:
Only 10% of programmers can write a binary search
Better yet, he has a coding challenge for you to take part in (post answers as comments):
Here are the rules:
- Use whatever programming language you like.
- No cutting, pasting or otherwise copying code. Don’t even look at other binary search code until you’re done.
- I need hardly say, no calling bsearch(), or otherwise cheating :-)
- Take as long as you like — you might finish, and feel confident in your code, after five minutes; or you’re welcome to take eight hours if you want (if you have the time to spare).
- You’re allowed to use your compiler to shake out mechanical bugs such as syntax errors or failure to initialise variables, but …
- NO TESTING until after you’ve decided your program is correct.
- Finally, the most important one: if you decide to begin this exercise, then you must report — either to say that you succeeded, failed or abandoned the attempt. Otherwise the figures will be skewed towards success.
If you haven’t yet, try it. Read just the article, but do not look at the others’ comments just yet.
After you have attempted this, there is his redux, on why coding exercises like this matter; and his redux of the redux, on why you should not test while attempting exercises like this. They are all worth a good read.