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:

First the select statement is evaluated and attributes not in the select list are ignored for the output
Nested loop then computes the inner join on spotters.spotters_id = sightings.spotter_id
Table access by index rowid retrieves the rows with the rowids that were returned by step 3 from the spotters table
Index unique scan, scans spotter_id in PK_SPOTTER_ID index and finds rowids associated rows in the spotters table
Table access full, then scans through sightings completely untill sighting_id = 1255 is found

Read execution plan from the bottom up left to right. Id column doesn't indicate that the operation is carried out first. Even though the execution plan is presented to you in the list(tabular) form it's actually is in tree-shaped form. so you read it starting with leaves. 1. Index unique scan goes first then 2. sightings table access full 3. spotters is accessed by index rowid 4. nested loop and finally 5. select find out more — Nick Krasnov

Lukas Eder Lukas Eder · Accepted Answer · 2016-10-23T16:31:10

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.

oracle execution plan, trying to understand

3 Answers