Explain plans explained - Oracle

When you submit a SQL Query to Oracle the optimizer will first work out the most efficient way of running the query. The optimizer will look at the query, look at stats on the database objects then work out many ways in which the query could be run i.e. what order to access the objects. For each possible method a score is estimated (the optimizer cost) and the path, or plan with the lost score/cost is chosen.

To see the chosen method of access the objects, we can view the query plan/path known as the Explain Plan. This is very useful in understanding why a query is perhaps taking longer than we might have expected and can be used to trap sub-optimal joins that are perhaps causing a cartesian join.


A real good link for a good introduction into Explain Plans.