I would like to see more basic sql optimizations. Most of the optimization is done using hints. An example is to take a piece of sql that references view a which references view b, and not only transform the inline view, but trim out the columns and tables that are not needed for this particular query. This way you’re trimming down bloated sql.
Another enhancement I would like to see is the elimination of joins that are not needed. Maybe the programmer removed columns from the select portion and not tables.
Another scenario is tables that are joined but no columns are used. The tables are simply joined in as a lookup table. Re-write it to use a where exists clause.
I would like to see options for using subqueries instead of joins, in vs exists, nested subqueries, and from clause subquery nesting.
These topics are discussed in detail from the Gavin Powell Oracle Performance Tuning for 10gR2, Chapter 7. No, I’m not related, but I do think that it’s an excellent chapter.
I’m sure that Steven Feuerenstein has similar published works on tuning sql.