EXPLAIN PLAN FOR
SELECT sightings.sighting_id, spotters.spotter_name,
INNER JOIN spotters
ON sightings.spotter_id = spotters.spotter_id
WHERE sightings.spotter_id = 1255;
id Operation Name
0 select statement
1 nested loops
2 table access by index rowid spotters
3 index unique scan pk_spotter_ID
4 table access full sightings
This is what happens, informally, in the right order:
-- The index pk_spotter_id is scanned for at most one row that satisfies spotter_id = 1255 3 index unique scan pk_spotter_ID -- The spotter_name column is fetched from the table spotters for the previously found row 2 table access by index rowid spotters -- A nested loop is run for each (i.e. at most one) of the previously found rows 1 nested loops -- That nested loop will scan the entire sightings table for rows that match the join -- predicate sightings.spotter_id = spotters.spotter_id 4 table access full sightings -- That'll be it for your select statement 0 select statement
In general (there are tons of exceptions), Oracle execution plans can be read
This means that you go down the tree until you find the first leaf operation (e.g. #3), that'll be executed "first", its results are fed to the parent (e.g. #2), all the siblings are then executed top down, all the siblings' results are also fed to the parent, then the parent result is fed to the grand parent (e.g. #1), until you reach the top operation.
This is a very informal explanation of what happens. Do note there will be many exceptions to these rules once statements become more complex.