101ldaniels 101ldaniels - 1 month ago 11
SQL Question

oracle execution plan, trying to understand

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

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.

Comments