title: understanding explain plan in oracle
body: <p>I was trying to understand the explain plan in oracle and wanted to know what conditions oracle considers while forming the explain plan</p>
  
  <p>I was testing a simple query in HR schema present in oracle 11g</p>
  
  <pre><code>select * from countries 
  where region_id in (select region_id from regions where region_name = 'Europe');
  </code></pre>
  
  <p>When I ran the following queries:</p>
  
  <pre><code>explain plan for 
  select * from countries 
  where region_id in (select region_id from regions where region_name = 'Europe');
  
  SELECT * FROM table(dbms_xplan.display(null,null,'basic'));
  </code></pre>
  
  <p>I got the following output in the explain table:</p>
  
  <pre><code>--------------------------------------------------------
  | Id  | Operation                    | Name            |
  --------------------------------------------------------
  |   0 | SELECT STATEMENT             |                 |
  |   1 |  NESTED LOOPS                |                 |
  |   2 |   INDEX FULL SCAN            | COUNTRY_C_ID_PK |
  |   3 |   TABLE ACCESS BY INDEX ROWID| REGIONS         |
  |   4 |    INDEX UNIQUE SCAN         | REG_ID_PK       |
  --------------------------------------------------------
  </code></pre>
  
  <p>Here I observed that the outer query was executed first, i.e countries table was executed first as indicated by Row 3.</p>
  
  <p>Now I added an index on the region_name of the regions table and ran the explain plan again
  and got the following output</p>
  
  <pre><code>--------------------------------------------------------------
  | Id  | Operation                    | Name                  |
  --------------------------------------------------------------
  |   0 | SELECT STATEMENT             |                       |
  |   1 |  NESTED LOOPS                |                       |
  |   2 |   TABLE ACCESS BY INDEX ROWID| REGIONS               |
  |   3 |    INDEX RANGE SCAN          | REGIONNAME_REGIONS_IX |
  |   4 |   INDEX UNIQUE SCAN          | COUNTRY_C_ID_PK       |
  |   5 |    INDEX RANGE SCAN          | COUNTRIES_REGIONID_IX |
  --------------------------------------------------------------
  </code></pre>
  
  <p>Now my question is:</p>
  
  <ol>
  <li>Shouldn't the inner query be executed first irrespective of whether index is present or not</li>
  <li>If the adding an index alters the execution plan, what other features can alter it?</li>
  <li>In general case what is the execution process like is it sequential (first executes the join which occurs first and then goes to next join in the query) ?</li>
  </ol>
  
  <p>Thanks in advance for your help.</p>
  
  <p>-Varun</p>