title: Oracle SQL query taking too long like 60 minutes to execute
body: <p>I have one query that taking too long, that is 60 minutes. I am programmer but i am not quite sure if all the table are indexes out. </p>
  
  <p>here is the query, any table that ends with _V is view:</p>
  
  <pre><code>SELECT prod_eff.facility
       , prod_eff.product
       , (SELECT MIN (cbv1.bucket_header) 
            FROM AMD_OARS.CURRENT_BUCKETS_V cbv1
           WHERE cbv1.version_id  = 1
             AND cbv1.min_datetime &gt;= prod_eff.EFF_START_DATETIME
         ) min_eff
       , (SELECT MAX (cbv1.bucket_header)
            FROM AMD_OARS.CURRENT_BUCKETS_V cbv1
           WHERE cbv1.version_id = 1
             AND cbv1.min_datetime &lt; prod_eff.EFF_END_DATETIME
         ) max_eff
       , 1 valid
    FROM (
          SELECT pf.product
               , pf.facility
               , pf.eff_start_datetime
               , pf.eff_end_datetime
            FROM AMD_OARS.BOM_PRODUCT_FACILITY pf
               , AMD_OARS.MASTER_FACILITY f
           WHERE pf.version_id = 114847
             AND pf.facility     = f.facility
             AND f.facility     != 'NONE'
             AND f.validated     = 1
         ) prod_eff
       , AMD_OARS.TEMP_SELECTED_PRODUCT tsp
   WHERE tsp.product = prod_eff.product
     AND (prod_eff.EFF_START_DATETIME BETWEEN 
              to_timestamp('07/27/2014 00:00:01.000', 'mm/dd/yyyy hh24:mi:ss.ff3') AND 
              to_timestamp('12/20/2015 00:00:00.000', 'mm/dd/yyyy  hh24:mi:ss.ff3') OR 
          prod_eff.EFF_END_DATETIME BETWEEN 
              to_timestamp('07/27/2014 00:00:01.000', 'mm/dd/yyyy hh24:mi:ss.ff3') AND 
              to_timestamp('12/20/2015 00:00:00.000', 'mm/dd/yyyy hh24:mi:ss.ff3') 
         )
   ORDER BY 1, 2, 3, 4 ;
  </code></pre>
  
  <p>This is the query I use to find out if some of the table are index or not. </p>
  
  <pre><code>select table_name 
  from dba_tables 
  where (owner, table_name) not in (select table_owner, table_name from dba_indexes)
  and table_name = 'TEMP_SELECTED_PRODUCT';
  </code></pre>
  
  <p>I am sorry I am newbie in database thing. I only know basic thing like sql join and writing basic queries. Any help would be appreciate. I am using Oracle SQL Developer. </p>
  
  <p>can anybody please guide me if I am doing anything wrong? I would appreciate it. </p>
  
  <p><strong>UPDATE</strong>:
  I saw <a href="https://www.youtube.com/watch?v=wD-RB7i-Ld8" rel="nofollow">this video</a> on how to execute 'explain plan for' and here is what i got result</p>
  
  <pre><code>Plan hash value: 1792060973
  
  -------------------------------------------------------------------------------------------------------
  | Id  | Operation                      | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
  -------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT               |                      |     1 |    78 |     4  (25)| 00:00:01 |
  |   1 |  SORT AGGREGATE                |                      |     1 |    66 |            |          |
  |*  2 |   TABLE ACCESS BY INDEX ROWID  | TEMP_CURRENT_BUCKETS |     1 |    66 |     1   (0)| 00:00:01 |
  |*  3 |    INDEX RANGE SCAN            | TCB_PK               |     1 |       |     1   (0)| 00:00:01 |
  |   4 |  SORT AGGREGATE                |                      |     1 |    66 |            |          |
  |*  5 |   TABLE ACCESS BY INDEX ROWID  | TEMP_CURRENT_BUCKETS |     1 |    66 |     1   (0)| 00:00:01 |
  |*  6 |    INDEX RANGE SCAN            | TCB_PK               |     1 |       |     1   (0)| 00:00:01 |
  |   7 |  SORT ORDER BY                 |                      |     1 |    78 |     4  (25)| 00:00:01 |
  |   8 |   NESTED LOOPS                 |                      |     1 |    78 |     3   (0)| 00:00:01 |
  |   9 |    NESTED LOOPS                |                      |     1 |    69 |     2   (0)| 00:00:01 |
  |  10 |     INDEX FULL SCAN            | TSP_PK               |     1 |    18 |     1   (0)| 00:00:01 |
  |* 11 |     TABLE ACCESS BY INDEX ROWID| BOM_PRODUCT_FACILITY |     1 |    51 |     1   (0)| 00:00:01 |
  |* 12 |      INDEX RANGE SCAN          | BPF_PK               |     1 |       |     1   (0)| 00:00:01 |
  |* 13 |    TABLE ACCESS BY INDEX ROWID | MASTER_FACILITY      |     1 |     9 |     1   (0)| 00:00:01 |
  |* 14 |     INDEX UNIQUE SCAN          | MF_F_PK              |     1 |       |     1   (0)| 00:00:01 |
  -------------------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     2 - filter("CB"."MIN_DATETIME"&gt;=:B1)
     3 - access("CB"."VERSION_ID"=1)
     5 - filter("CB"."MIN_DATETIME"&lt;:B1)
     6 - access("CB"."VERSION_ID"=1)
    11 - filter(INTERNAL_FUNCTION("PF"."EFF_START_DATETIME")&gt;=TIMESTAMP' 2014-07-27 
                00:00:01.000000000' AND INTERNAL_FUNCTION("PF"."EFF_START_DATETIME")&lt;=TIMESTAMP' 2015-12-20 
                00:00:00.000000000' OR INTERNAL_FUNCTION("PF"."EFF_END_DATETIME")&gt;=TIMESTAMP' 2014-07-27 
                00:00:01.000000000' AND INTERNAL_FUNCTION("PF"."EFF_END_DATETIME")&lt;=TIMESTAMP' 2015-12-20 
                00:00:00.000000000')
    12 - access("PF"."VERSION_ID"=114847 AND "TSP"."PRODUCT"="PF"."PRODUCT")
         filter("TSP"."PRODUCT"="PF"."PRODUCT" AND "PF"."FACILITY"&lt;&gt;'NONE')
    13 - filter("F"."VALIDATED"=1)
    14 - access("PF"."FACILITY"="F"."FACILITY")
         filter("F"."FACILITY"&lt;&gt;'NONE')
  </code></pre>