Oracle finally desupported the rule-based optimizer with version 10g. Since 9i, users are good adviced to make themself familiar with the cost-based-optimizer (CBO). In order to understand what happens with your statement after parsing with CBO involved, and to be able to tune your queries, displaying, understanding and optimizing explain- or execution plans is essential.
First of all, what’s a explain- or execution plan of a query? It’s a set of operations that the DB has to perform in order to deliver the correct result set. The plan is computed by the (cost-based) optimizer based on several conditions in the “environment” of the queried object (usually a table): It considers indexes, optimizer statistics, histograms, load, memory, and so on, and looks for an optimal path to achieve the goal. Thus, this plan dictates if the query runs in a efficient or wasteful way. The result set itself is – hopefully – always the same.
I don’t like to elaborate the whole tuning chapter here, because you can find real pro’s on this topic on the net, just to mention two: Karen Morton, Richard Foote and many more. This post is just a quick sheet on displaying plans with Oracle’ own toolset.
But initially you should understand a basic difference: