oracle execution plan, trying to understand

Question

EXPLAIN PLAN FOR
  SELECT sightings.sighting_id, spotters.spotter_name,
         sightings.sighting_date
    FROM sightings
         INNER JOIN spotters
                 ON sightings.spotter_id = spotters.spotter_id
   WHERE sightings.spotter_id = 1255;

SELECT plan_table_output
  FROM table(dbms_xplan.display('plan_table',null,'basic'));




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

Im trying to understand whats exactly going on here does this sound right:

1. First the select statement is evaluated and attributes not in the select list are ignored for the output

2. Nested loop then computes the inner join on spotters.spotters_id = sightings.spotter_id

3. Table access by index rowid retrieves the rows with the rowids that were returned by step 3 from the spotters table

4. Index unique scan, scans spotter_id in PK_SPOTTER_ID index and finds rowids associated rows in the spotters table

5. Table access full, then scans through sightings completely untill sighting_id = 1255 is found

Answer 1

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

• Bottom-up
• First sibling first

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.