Seeking tips and tutorials for tuning Oracle SQL on larger tables

For someone new to SQL Tuning with Oracle, I am seeking pointers and informative URLs that can assist me with how to approach an SQL query as well as define indexes where necessary, when it comes to tuning SQL queries for tables holding large quantity of data, in excess of 50,000 rows?

What are the main things that I should be looking out for to speed up an SQ开发者_Go百科L statement?

It's a big topic. Anything by Tom Kyte is good:

• AskTom

• The Tom Kyte Blog

• Query Tuning

• Effective Oracle by Design

• Oracle8i Designing and Tuning for Performance (trying to find the latest version, though much of the information is the same)

• Oracle database Performance Tuning FAQ

Get the Query plan of the query using EXPLAIN PLAN and then take steps to optimize based on that.

The above link is a great help to understand EXPLAIN PLAN but I have usually used other references to understand the intricacies.

Ask Tom, as mentioned by Mitch above is a GREAT resource for all things Oracle DB related.

Jonathan Lewis Cost-Based Oracle Fundamentals is the best book on the market if you want to learn how Oracle "thinks" when selecting access paths, join mechanism, join order etcetera.

It takes a few readings to get the hang of it, but once it "clicks" you have a ridiculusly powerful tool in your box, because not only can you troubleshoot much better, but you start to design tables and indexes in a way that play to the strengths of the database.

People have already posted excellent answers - but if you're having a performance program with only 50,000 rows, I'm guessing you're doing a full table scan somewhere where you need an index.