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:

  • 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

    • 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 (eg #3), that'll be executed "first", its results are fed to the parent (eg #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 (eg #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.

    Steps seem to be basically correct but should be buttom-up. The projection (choosing the relevant columns) is optimally done as early as possible at the scan phase. The index operation is SEEK (you are not scanning the whole index)

    NOTE: THIS ANSWER REFERS TO THE ORIGINAL VERSION OF THE QUESTION.

    Oracle is reading the two tables in their entirety.

    It is hashing each of the tables based on the join keys -- "re-ordering" the tables so similar keys appear near each other.

    It is doing the join.

    It is then doing the calculations for the final select and returning the results to the user.

    链接地址: http://www.djcxy.com/p/60722.html

    上一篇: SQL调优，长时间运行查询+ rownum

    下一篇: oracle执行计划，试图理解