title: Indexes hints in a Subquery
body: <p>I have a SQL statement that has performance issues.</p>
  
  <p>Adding the following index and a SQL hint to use the index improves the performance 10 fold but I do not understand why.
  BUS_ID is part of the primary key(T1.REF is the other part fo the key) and clustered index on the T1 table.</p>
  
  <p>The T1 table has about 100,000 rows. BUS_ID has only 6 different values. Similarly the T1.STATUS column can only have a limited number of 
  possibilities and the majority of these(99%) will be the same value.</p>
  
  <p>If I run the query without the hint(/*+ INDEX ( T1 T1_IDX1) NO_UNNEST */) it takes 5 seconds and with the hint it takes .5 seconds. 
  I don't understand how the index helps the subquery as T1.STATUS isn't used in any of the 'where' or 'join' clauses in the subquery.</p>
  
  <p>What am I missing?</p>
  
  <pre><code>SELECT 
            /*+ NO_UNNEST */ 
            t1.bus_id, 
            t1.ref, 
            t2.cust, 
            t3.cust_name, 
            t2.po_number, 
            t1.status_old, 
            t1.status, 
            t1.an_status 
  FROM      t1 
  LEFT JOIN t2 
  ON        t1.bus_id = t2.bus_id 
  AND       t1.ref = t2.ref 
  JOIN      t3 
  ON        t3.cust = t2.cust 
  AND       t3.bus_id  = t2.bus_id 
  WHERE     ( 
                      status IN ('A', 'B', 'C') AND status_old IN ('X', 'Y')) 
              AND EXISTS 
            ( SELECT /*+ INDEX ( T1 T1_IDX1) NO_UNNEST */ 
                   * 
                   FROM   t1 
                   WHERE  ( EXISTS ( SELECT  /*+ NO_UNNEST */ 
                                        * 
                                        FROM   t6 
                                        WHERE  seq IN ( '0', '2' ) 
                                        AND    t1.bus_id = t6.bus_id) 
                                        OR (EXISTS
                                        (SELECT /*+ NO_UNNEST */ 
                                                      * 
                                               FROM   t6 
                                               WHERE  seq = '1' 
                                               AND    (an_status = 'Y' 
                                                      OR     
                                                      an_status = 'X') 
                                               AND    t1.bus_id = t6.bus_id)) 
                          AND t2.ref = t1.ref)) 
                          AND USER IN ('FRED') 
                          AND ( t2.status != '45' 
                              AND t2.status != '20') 
                          AND NOT EXISTS ( SELECT 
                          /*+ NO_UNNEST */ 
                          * 
                           FROM   t4 
                           WHERE  EXISTS 
                                  ( 
                                         SELECT 
                                                /*+ NO_UNNEST */ 
                                                * 
                                         FROM   t5 
                                         WHERE  pd IN ( '1', 
                                                              '0' ) 
                                         AND    appl = 'RYP' 
                                         AND    appl_id IN ( 'RL100') 
                                         AND    t4.id = t5.id) 
                           AND    t2.ref = p.ref 
                           AND    t2.bus_id = p.bus_id);
  </code></pre>
  
  <p>Edited to include Explain Plan and index.                     </p>
  
  <pre><code>Without Index hint
  
  ------------------------------------------------------|-------------------------------------
              Operation            |      Options       |Cost| # |Bytes | CPU Cost | IO COST 
  ------------------------------------------------------|-------------------------------------
  select statement                 |                    | 20 | 1 | 211  | 15534188 |    19   |
   view                            |                    | 20 | 1 | 211  | 15534188 |    19   |
     count                         |                    |    |   |      |          |         |
       view                        |                    | 20 | 1 | 198  | 15534188 |    19   |
        sort                       |  ORDER BY          | 20 | 1 | 114  | 15534188 |    19   |
          nested loops             |                    | 7  | 1 | 114  |    62487 |     7   |
           nested loops            |                    | 7  | 1 | 114  |    62487 |     7   |
            nested loops           |                    | 6  | 1 |  84  |    53256 |     6   |
             inlist iterator       |                    |    |   |      |          |         |
              TABLE access t1      |  INDEX ROWID       | 4  | 1 |  29  |    36502 |     4   |
               index-t1_idx#3      |  RANGE SCAN        | 3  | 1 |      |    28686 |     3   |
             TABLE access - t2     |  INDEX ROWID       | 2  | 1 |  55  |    16754 |     2   |
              index t2_idx#0       |  UNIQUE SCAN       | 1  | 1 |      |     9042 |     1   |
               filter              |                    |    |   |      |          |         |
                TABLE access-t1    |  INDEX ROWID       | 2  | 1 |  15  |     7433 |     2   |
                TABLE access-t6    |  INDEX ROWID       | 3  | 1 |   4  |    23169 |     3   |
                 index-t6_idx#0    |  UNIQUE RANGE SCAN | 1  | 3 |      |     7721 |     1   |
                filter             |                    |    |   |      |          |         |
                 TABLE access-t6   |  INDEX ROWID       | 2  | 2 |   8  |    15363 |     2   |
                  index-t6_idx#0   |  UNIQUE RANGE SCAN | 1  | 3 |      |     7521 |     1   |
               index-t4_idx#1      |  RANGE SCAN        | 3  | 1 |  28  |    21584 |     3   |
                inlist iterator    |                    |    |   |      |          |         |
                 index-t5_idx#1    |  RANGE SCAN        | 4  | 1 |  24  |    42929 |     4   |
            index-t3_idx#0         |  INDEX UNIQUE SCAN | 0  | 1 |      |     1900 |     0   |
           TABLE access-t3         |  INDEX ROWID       | 1  | 1 |  30  |     9231 |     1   |
  --------------------------------------------------------------------------------------------
  </code></pre>
  
  <p>With Index hint</p>
  
  <pre><code>------------------------------------------------------|-------------------------------------
              Operation            |      Options       |Cost| # |Bytes | CPU Cost | IO COST 
  ------------------------------------------------------|-------------------------------------
  select statement                 |                    | 21 | 1 | 211  | 15549142 |    19   |
   view                            |                    | 21 | 1 | 211  | 15549142 |    19   |
     count                         |                    |    |   |      |          |         |
       view                        |                    | 21 | 1 | 198  | 15549142 |    19   |
        sort                       |  ORDER BY          | 21 | 1 | 114  | 15549142 |    19   |
          nested loops             |                    | 7  | 1 | 114  |    62487 |     7   |
           nested loops            |                    | 7  | 1 | 114  |    62487 |     7   |
            nested loops           |                    | 6  | 1 |  84  |    53256 |     6   |
             inlist iterator       |                    |    |   |      |          |         |
              TABLE access t1      |  INDEX ROWID       | 4  | 1 |  29  |    36502 |     4   |
               index-t1_idx#3      |  RANGE SCAN        | 3  | 1 |      |    28686 |     3   |
             TABLE access - t2     |  INDEX ROWID       | 2  | 1 |  55  |    16754 |     2   |
              index t2_idx#0       |  UNIQUE SCAN       | 1  | 1 |      |     9042 |     1   |
               filter              |                    |    |   |      |          |         |
                TABLE access-t1    |  INDEX ROWID       | 3  | 1 |  15  |    22387 |     2   |
                 index-t1_idx#1    |  FULL SCAN         | 2  |97k|      |    14643 |         |
                TABLE access-t6    |  INDEX ROWID       | 3  | 1 |   4  |    23169 |     3   |
                 index-t6_idx#0    |  UNIQUE RANGE SCAN | 1  | 3 |      |     7721 |     1   |
                filter             |                    |    |   |      |          |         |
                 TABLE access-t6   |  INDEX ROWID       | 2  | 2 |   8  |    15363 |     2   |
                  index-t6_idx#0   |  UNIQUE RANGE SCAN | 1  | 3 |      |     7521 |     1   |
               index-t4_idx#1      |   RANGE SCAN       | 3  | 1 |  28  |    21584 |     3   |
                inlist iterator    |                    |    |   |      |          |         |
                 index-t5_idx#1    |  RANGE SCAN        | 4  | 1 |  24  |    42929 |     4   |
            index-t3_idx#0         |  INDEX UNIQUE SCAN | 0  | 1 |      |     1900 |     0   |
           TABLE access-t3         |  INDEX ROWID       | 1  | 1 |  30  |     9231 |     1   |
  --------------------------------------------------------------------------------------------
  </code></pre>
  
  <p>Table Index</p>
  
  <pre><code>CREATE INDEX T1_IDX#1 ON T1 (BUS_ID, STATUS)
  </code></pre>