As you are looking for Oracle specific information, I would recommend the Ask Tom blog at Oracle. In general, I think you will find the advice is not to tune the query. You will get good advice on how to write a query the optimizer can optimize. The Oracle documentation is online as well, and I usually look there for up to date information on Oracle. I haven't worked with SQLServer so I don't have any recommendations for it.
I haven't seen a lot new in the field of optimizing queries over the last few years. The big change is the deprecation of the rule based optimizer, which I can barely remember working with. However, I understand SQLServer still uses a rule based optimizer, so understanding its rules can help.
A tool where you can edit a query, execute it, and generate an explain plan helps in understanding what changes get you a query that performs well. I have had good results with AquaData Studio, and really like its tree view. SQL Developer should do as well.
As with any optimization, you need to have quantitative data about it's performance. Then you can determine if you actually did optimize it.
How to optimize a query depends in part on how the parser builds and optimizes the query. To a larger extent it depends on the distribution of the data you are querying. In an Oracle database if the result set makes up four percent or more of a table and are randomly distributed, a table scan is usually faster than an index.
I have worked to optimizing queries for a team of developers. Only two or three queries a year required serious optimization. Most queries are simple enough that they don't need optimization. The rest could usually be handled by adding missing join paths.
For Oracle there are three tunable settings which can significantly impact performance. The costing for index and data lookups interact to change the conditions under which an in index will or won't be used. These two can be tuned on a per session basis. The defaults are often not optimal. The other value controls how many alternatives the optimizer will try. Increasing this value often helps.
Optimization is significantly impacted by data distribution and volume. When optimizing it it best to use a copy of the production database, or at least a database with the same data distribution and volumes. I have severely broken the testing environment, optimizing a query for production order database. The testing and development databases had significantly different data distribution which caused the query to fail even with significantly less data.