In fact, the cost based optimizer does that already - it rewrites our queries all of the time. They offer 'rules of thumb' (what I generally call ROT since the acronym and the word is maps to are so appropriate for each other) SQL optimizations - which if they were universally applicable - the optimizer would do it as a matter of fact. They show you different query plans for the same statement and allow you to pick one. What they do is primarily recommend indexing schemes to tune a query, suggest materialized views, offer to add hints to the query to try other access plans. Oh don't get me wrong, there are many programs that actually try to do this - Oracle Enterprise Manager with its tuning pack, SQL Navigator and others. If there were a 10 step or even 1,000,000 step process by which any query can be tuned (or even X% of queries for that matter), we would write a program to do it. What can be delivered: Knowledge about how queries are processed, knowledge you can use and apply day to day as you develop them.

What people want: The 10 step process by which you can tune any query. That is not because the material is all that complex, rather because I know what people want - and I know what can be delivered. This was probably the hardest part of the book to write - this chapter. If you want to really be aboe to tune the other 99% of the queries out there, knowledge of lots of stuff - physical storage mechanisms, access paths, how the optimizer works - thats the only way. They APPEAR to be able to tune a much larger percent but that is only because the people using these tools never look at the outcome - hence they continue to make the same basic mistakes over and over and over. They work using rules (heuristics) and can tune maybe 1% of the problem queries out there. There are tons of these tools on the market.

You are not needed in this process, anyone can put a query in, get a query out and run it to see if it is faster. If you want a 10 step guide to tuning a query, buy a piece of software. Here is a short extract from a book I am working on.

Quest sql tuning for oracle