title: Why does the same select statement have different costs in Oracle?
body: <p>Recently I used Oracle 11g database to do my homework. I had 12 tables, like  trip_data_11 and trip_data_12. 
  They have same structure and the number of records is almost the same. I created the same indexes on each table.
  So for trip_data_11 table:</p>
  
  <pre><code>create index pick_add_11 on trip_data_11(pickup_longitude,pickup_latitude);
  create index drop_add_11 on trip_data_11(dropoff_longitude,dropoff_latitude);
  </code></pre>
  
  <p>The same operation to trip_data_12.</p>
  
  <p>Then I used the following select statement to select the taxi numbers per day.</p>
  
  <pre><code>SELECT
     COUNT(DISTINCT(td.medallion)) AS taxi_num
  FROM
     SYS.TRIP_DATA_11 td
  WHERE 
      (td.pickup_longitude &gt;= -74.2593 AND td.pickup_longitude &lt;= -73.7011
       AND td.pickup_latitude &gt;= 40.4770 AND td.pickup_latitude &lt;= 40.9171
      )
      AND
      (td.dropoff_longitude &gt;= -74.2593 AND td.dropoff_longitude &lt;=    -73.7011
      AND td.dropoff_latitude &gt;= 40.4770 AND td.dropoff_latitude &lt;=  40.9171
    )
   AND
       td.trip_distance &gt; 0
   AND
       td.passenger_count &gt; 0
    GROUP BY 
        regexp_substr(td.pickup_datetime,'\d{4}-\d{2}-\d{2}')
    ORDER BY
        regexp_substr(td.pickup_datetime,'\d{4}-\d{2}-\d{2}');
  </code></pre>
  
  <p>It costs 38sec。When I changed the table name to SYS.TRIP_DATA_12, the problem coming, it costs more than 2 hours.
  What's more, it did not end. I don't know why.</p>
  
  <p>Today I ask my classmate and he said: clear the cache. So I used the following  statements to do it.</p>
  
  <pre><code>alter system flush shared_pool;
  alter system flush buffer_cache;
  alter system flush global context;
  </code></pre>
  
  <p>Now when I use the same select statement for SYS.TRIP_DATA_11 I get the same poor performance like SYS.TRIP_DATA_12. Why? </p>