title: How do I force access by index rowid in Oracle?
body: <p>I need help forcing Oracle to always use table access by index row id on table "r_rapport" (~60k rows) to subsequently avoid full table scans on "r_attributfeld" (~8m rows) . I have a query resulting in the following plan:</p>
  
  <pre><code>---------------------------------------------------------------------------------------------------------
  | Id  | Operation                      | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
  ---------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT               |                |   101 | 22220 |       | 63518   (2)| 00:12:43 |
  |*  1 |  COUNT STOPKEY                 |                |       |       |       |            |          |
  |   2 |   VIEW                         |                |  2870 |   616K|       | 63518   (2)| 00:12:43 |
  |*  3 |    SORT ORDER BY STOPKEY       |                |  2870 |   313K|   696K| 63518   (2)| 00:12:43 |
  |*  4 |     FILTER                     |                |       |       |       |            |          |
  |*  5 |      HASH JOIN SEMI            |                |  2871 |   314K|       | 51920   (2)| 00:10:24 |
  |*  6 |       HASH JOIN RIGHT SEMI     |                |  2871 |   299K|       | 26084   (2)| 00:05:14 |
  |   7 |        VIEW                    | VW_NSO_1       |   214 |  1070 |       |     5  (20)| 00:00:01 |
  |*  8 |         HASH JOIN              |                |   214 |  5350 |       |     5  (20)| 00:00:01 |
  |*  9 |          INDEX RANGE SCAN      | TEST7          |   141 |  1269 |       |     2   (0)| 00:00:01 |
  |* 10 |          INDEX RANGE SCAN      | TEST8          |   228 |  3648 |       |     2   (0)| 00:00:01 |
  |* 11 |        HASH JOIN SEMI          |                |  5848 |   582K|       | 26079   (2)| 00:05:13 |
  |* 12 |         HASH JOIN              |                |  6547 |   620K|       |   243   (2)| 00:00:03 |
  |* 13 |          INDEX RANGE SCAN      | TEST5          |    47 |   470 |       |     2   (0)| 00:00:01 |
  |  14 |          TABLE ACCESS FULL     | R_RAPPORT      | 60730 |  5159K|       |   240   (1)| 00:00:03 |
  |  15 |         VIEW                   | VW_SQ_3        |   334K|  1633K|       | 25834   (2)| 00:05:11 |
  |* 16 |          HASH JOIN             |                |   334K|    14M|    10M| 25834   (2)| 00:05:11 |
  |  17 |           INDEX FAST FULL SCAN | TEST4          |   476K|  4656K|       |   368   (2)| 00:00:05 |
  |* 18 |           HASH JOIN            |                |   343K|    11M|    11M| 24214   (2)| 00:04:51 |
  |* 19 |            TABLE ACCESS FULL   | R_ATTRIBUTFELD |   343K|  7722K|       | 20483   (2)| 00:04:06 |
  |  20 |            INDEX FAST FULL SCAN| TEST3          |  1670K|    17M|       |  1324   (1)| 00:00:16 |
  |  21 |       VIEW                     | VW_SQ_2        |   334K|  1633K|       | 25834   (2)| 00:05:11 |
  |* 22 |        HASH JOIN               |                |   334K|    14M|    10M| 25834   (2)| 00:05:11 |
  |  23 |         INDEX FAST FULL SCAN   | TEST4          |   476K|  4656K|       |   368   (2)| 00:00:05 |
  |* 24 |         HASH JOIN              |                |   343K|    11M|    11M| 24214   (2)| 00:04:51 |
  |* 25 |          TABLE ACCESS FULL     | R_ATTRIBUTFELD |   343K|  7722K|       | 20483   (2)| 00:04:06 |
  |  26 |          INDEX FAST FULL SCAN  | TEST3          |  1670K|    17M|       |  1324   (1)| 00:00:16 |
  |* 27 |      INDEX RANGE SCAN          | TEST6          |     1 |     8 |       |     8   (0)| 00:00:01 |
  ---------------------------------------------------------------------------------------------------------
  </code></pre>
  
  <p>By adding a FIRST_ROWS(1) hint, this changes to the much more desired plan:</p>
  
  <pre><code>----------------------------------------------------------------------------------------------------------
  | Id  | Operation                         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
  ----------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                  |                      |     1 |   220 |    96   (0)| 00:00:02 |
  |*  1 |  COUNT STOPKEY                    |                      |       |       |            |          |
  |   2 |   VIEW                            |                      |     1 |   220 |    96   (0)| 00:00:02 |
  |*  3 |    FILTER                         |                      |       |       |            |          |
  |   4 |     NESTED LOOPS                  |                      |     1 |    97 |    16   (0)| 00:00:01 |
  |   5 |      TABLE ACCESS BY INDEX ROWID  | R_RAPPORT            | 60730 |  5159K|     6   (0)| 00:00:01 |
  |   6 |       INDEX FULL SCAN DESCENDING  | IDX_R_RAPPORT_3      |    10 |       |     2   (0)| 00:00:01 |
  |*  7 |      INDEX RANGE SCAN             | TEST5                |     1 |    10 |     1   (0)| 00:00:01 |
  |   8 |     NESTED LOOPS                  |                      |     1 |    25 |     2   (0)| 00:00:01 |
  |*  9 |      INDEX RANGE SCAN             | TEST7                |   141 |  1269 |     2   (0)| 00:00:01 |
  |* 10 |      INDEX UNIQUE SCAN            | TEST8                |     1 |    16 |     0   (0)| 00:00:01 |
  |* 11 |       TABLE ACCESS BY INDEX ROWID | R_ATTRIBUTFELD       |     1 |    23 |     3   (0)| 00:00:01 |
  |  12 |        NESTED LOOPS               |                      |     2 |    88 |    35   (0)| 00:00:01 |
  |  13 |         NESTED LOOPS              |                      |    10 |   210 |     7   (0)| 00:00:01 |
  |* 14 |          INDEX RANGE SCAN         | TEST4                |     9 |    90 |     3   (0)| 00:00:01 |
  |* 15 |          INDEX RANGE SCAN         | TEST3                |     3 |    33 |     2   (0)| 00:00:01 |
  |* 16 |         INDEX RANGE SCAN          | IDX_R_ATTRIBUTFELD_1 |     6 |       |     2   (0)| 00:00:01 |
  |* 17 |        TABLE ACCESS BY INDEX ROWID| R_ATTRIBUTFELD       |     1 |    23 |     3   (0)| 00:00:01 |
  |  18 |         NESTED LOOPS              |                      |     2 |    88 |    35   (0)| 00:00:01 |
  |  19 |          NESTED LOOPS             |                      |    10 |   210 |     7   (0)| 00:00:01 |
  |* 20 |           INDEX RANGE SCAN        | TEST4                |     9 |    90 |     3   (0)| 00:00:01 |
  |* 21 |           INDEX RANGE SCAN        | TEST3                |     3 |    33 |     2   (0)| 00:00:01 |
  |* 22 |          INDEX RANGE SCAN         | IDX_R_ATTRIBUTFELD_1 |     6 |       |     2   (0)| 00:00:01 |
  |* 23 |         INDEX RANGE SCAN          | TEST6                |     1 |     8 |     8   (0)| 00:00:01 |
  ----------------------------------------------------------------------------------------------------------
  </code></pre>
  
  <p>Unfortunately this query is composed at runtime, and should there be three subrequests instead of the two here, it will ignore the FIRST_ROWS hint and again do multiple full table scans on 8 million rows. With the given data distribution access by rowid will always be faster (almost instant) while the plan Oracle prefers takes several seconds.</p>
  
  <p>I tried using ROWID hints on both tables, before discovering those have been deprecated.</p>
  
  <p>Any pointers will be appreciated.</p>
  
  <p>=edit=</p>
  
  <p>USE_NL &amp; new Index</p>
  
  <pre><code>-----------------------------------------------------------------------------------------------------------
  | Id  | Operation                          | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
  -----------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                   |                      |   101 | 22220 |  3994   (1)| 00:00:48 |
  |*  1 |  COUNT STOPKEY                     |                      |       |       |            |          |
  |   2 |   VIEW                             |                      |   102 | 22440 |  3994   (1)| 00:00:48 |
  |   3 |    NESTED LOOPS SEMI               |                      |     1 |   102 |    16   (7)| 00:00:01 |
  |   4 |     NESTED LOOPS                   |                      |     1 |    97 |    11   (0)| 00:00:01 |
  |   5 |      TABLE ACCESS BY INDEX ROWID   | R_RAPPORT            | 58985 |  5011K|    10   (0)| 00:00:01 |
  |*  6 |       INDEX FULL SCAN DESCENDING   | TEST12               |     1 |       |     9   (0)| 00:00:01 |
  |*  7 |        TABLE ACCESS BY INDEX ROWID | R_ATTRIBUTFELD       |     1 |    23 |     3   (0)| 00:00:01 |
  |   8 |         NESTED LOOPS               |                      |     2 |    88 |    35   (0)| 00:00:01 |
  |   9 |          NESTED LOOPS              |                      |    10 |   210 |     7   (0)| 00:00:01 |
  |* 10 |           INDEX RANGE SCAN         | TEST4                |     9 |    90 |     3   (0)| 00:00:01 |
  |* 11 |           INDEX RANGE SCAN         | TEST3                |     3 |    33 |     2   (0)| 00:00:01 |
  |* 12 |          INDEX RANGE SCAN          | IDX_R_ATTRIBUTFELD_1 |     6 |       |     2   (0)| 00:00:01 |
  |* 13 |         TABLE ACCESS BY INDEX ROWID| R_ATTRIBUTFELD       |     1 |    23 |     3   (0)| 00:00:01 |
  |  14 |          NESTED LOOPS              |                      |     2 |    88 |    35   (0)| 00:00:01 |
  |  15 |           NESTED LOOPS             |                      |    10 |   210 |     7   (0)| 00:00:01 |
  |* 16 |            INDEX RANGE SCAN        | TEST4                |     9 |    90 |     3   (0)| 00:00:01 |
  |* 17 |            INDEX RANGE SCAN        | TEST3                |     3 |    33 |     2   (0)| 00:00:01 |
  |* 18 |           INDEX RANGE SCAN         | IDX_R_ATTRIBUTFELD_1 |     6 |       |     2   (0)| 00:00:01 |
  |* 19 |          INDEX RANGE SCAN          | TEST6                |     1 |     8 |     8   (0)| 00:00:01 |
  |* 20 |      INDEX RANGE SCAN              | TEST5                |     1 |    10 |     1   (0)| 00:00:01 |
  |* 21 |     VIEW                           | VW_NSO_1             |   105 |   525 |     5  (20)| 00:00:01 |
  |* 22 |      HASH JOIN                     |                      |   214 |  5350 |     5  (20)| 00:00:01 |
  |* 23 |       INDEX RANGE SCAN             | TEST7                |   141 |  1269 |     2   (0)| 00:00:01 |
  |* 24 |       INDEX RANGE SCAN             | TEST8                |   228 |  3648 |     2   (0)| 00:00:01 |
  -----------------------------------------------------------------------------------------------------------
  </code></pre>
  
  <p>SQL:</p>
  
  <pre><code>select /*+ FIRST_ROWS */ * from ( 
  select *
  from   r_rapport a
  where  rb_id in (
    select obj_id from obj_recht where obj_typ = 20 and obj_pid = 10065 and maske_id in (
        select  distinct maske_id
        from    obj_rechtmaske
        where   subj_pid = 10065
      ) )
  and    rb_id in (
    select id from rb_buch where pid = 10065
  )
  and exists (
    select /*+ USE_NL( c d ) */ 1
    from   r_teilanlage b, r_attribut c, r_attributfeld d
    where  a.id = b.r_id
    and    b.id = c.r_teilanlage_id
    and    c.id = d.r_attribut_id
    and    d.attributfeld_typ not in ( 20, 25, 40, 78, 79, 90, 92, 123, 124, 125, 126, 127 )
    and    lower( d.wert ) like lower( '%ä%' )
  )
  and exists (
    select /*+ USE_NL( c d ) */ 1
    from   r_teilanlage b, r_attribut c, r_attributfeld d
    where  a.id = b.r_id
    and    b.id = c.r_teilanlage_id
    and    c.id = d.r_attribut_id
    and    d.attributfeld_typ not in ( 20, 25, 40, 78, 79, 90, 92, 123, 124, 125, 126, 127 )
    and    lower( d.wert ) like lower( '%ö%' )
  )
  and exists (
    select /*+ USE_NL( c d ) */ 1
    from   r_teilanlage b, r_attribut c, r_attributfeld d
    where  a.id = b.r_id
    and    b.id = c.r_teilanlage_id
    and    c.id = d.r_attribut_id
    and    d.attributfeld_typ not in ( 20, 25, 40, 78, 79, 90, 92, 123, 124, 125, 126, 127 )
    and    lower( d.wert ) like lower( '%ä%' )
  )
  and exists (
    select /*+ USE_NL( c d ) */ 1
    from   r_teilanlage b, r_attribut c, r_attributfeld d
    where  a.id = b.r_id
    and    b.id = c.r_teilanlage_id
    and    c.id = d.r_attribut_id
    and    d.attributfeld_typ not in ( 20, 25, 40, 78, 79, 90, 92, 123, 124, 125, 126, 127 )
    and    lower( d.wert ) like lower( '%ö%' )
  )
  and a.id not in (
    select r_id from r_gelesen where ma_id = 144
  )
  order by a.open_stamp desc
   ) where rownum &lt;= 101;
  </code></pre>
  
  <p>and its plan:</p>
  
  <pre><code>----------------------------------------------------------------------------------------------------------
  | Id  | Operation                         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
  ----------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                  |                      |     1 |   220 |  1195K  (1)| 03:59:08 |
  |*  1 |  COUNT STOPKEY                    |                      |       |       |            |          |
  |   2 |   VIEW                            |                      |     1 |   220 |  1195K  (1)| 03:59:08 |
  |*  3 |    FILTER                         |                      |       |       |            |          |
  |   4 |     NESTED LOOPS SEMI             |                      |  3213 |   320K|  1018K  (1)| 03:23:47 |
  |   5 |      NESTED LOOPS                 |                      |  6547 |   620K| 82249   (1)| 00:16:27 |
  |   6 |       TABLE ACCESS BY INDEX ROWID | R_RAPPORT            | 60730 |  5159K| 21493   (1)| 00:04:18 |
  |   7 |        INDEX FULL SCAN DESCENDING | IDX_R_RAPPORT_3      | 60730 |       |   152   (1)| 00:00:02 |
  |*  8 |       INDEX RANGE SCAN            | TEST5                |     1 |    10 |     1   (0)| 00:00:01 |
  |*  9 |      VIEW                         | VW_NSO_1             |   105 |   525 |   143   (0)| 00:00:02 |
  |  10 |       NESTED LOOPS                |                      |   214 |  5350 |   143   (0)| 00:00:02 |
  |* 11 |        INDEX RANGE SCAN           | TEST7                |   141 |  1269 |     2   (0)| 00:00:01 |
  |* 12 |        INDEX RANGE SCAN           | TEST8                |     2 |    32 |     1   (0)| 00:00:01 |
  |* 13 |     TABLE ACCESS BY INDEX ROWID   | R_ATTRIBUTFELD       |     1 |    23 |     3   (0)| 00:00:01 |
  |  14 |      NESTED LOOPS                 |                      |     6 |   264 |    97   (0)| 00:00:02 |
  |  15 |       NESTED LOOPS                |                      |    30 |   630 |    13   (0)| 00:00:01 |
  |* 16 |        INDEX RANGE SCAN           | TEST4                |     9 |    90 |     3   (0)| 00:00:01 |
  |* 17 |        INDEX RANGE SCAN           | TEST3                |     3 |    33 |     2   (0)| 00:00:01 |
  |* 18 |       INDEX RANGE SCAN            | IDX_R_ATTRIBUTFELD_1 |     6 |       |     2   (0)| 00:00:01 |
  |* 19 |      TABLE ACCESS BY INDEX ROWID  | R_ATTRIBUTFELD       |     1 |    23 |     3   (0)| 00:00:01 |
  |  20 |       NESTED LOOPS                |                      |     6 |   264 |    97   (0)| 00:00:02 |
  |  21 |        NESTED LOOPS               |                      |    30 |   630 |    13   (0)| 00:00:01 |
  |* 22 |         INDEX RANGE SCAN          | TEST4                |     9 |    90 |     3   (0)| 00:00:01 |
  |* 23 |         INDEX RANGE SCAN          | TEST3                |     3 |    33 |     2   (0)| 00:00:01 |
  |* 24 |        INDEX RANGE SCAN           | IDX_R_ATTRIBUTFELD_1 |     6 |       |     2   (0)| 00:00:01 |
  |* 25 |       TABLE ACCESS BY INDEX ROWID | R_ATTRIBUTFELD       |     1 |    23 |     3   (0)| 00:00:01 |
  |  26 |        NESTED LOOPS               |                      |     6 |   264 |    97   (0)| 00:00:02 |
  |  27 |         NESTED LOOPS              |                      |    30 |   630 |    13   (0)| 00:00:01 |
  |* 28 |          INDEX RANGE SCAN         | TEST4                |     9 |    90 |     3   (0)| 00:00:01 |
  |* 29 |          INDEX RANGE SCAN         | TEST3                |     3 |    33 |     2   (0)| 00:00:01 |
  |* 30 |         INDEX RANGE SCAN          | IDX_R_ATTRIBUTFELD_1 |     6 |       |     2   (0)| 00:00:01 |
  |* 31 |        TABLE ACCESS BY INDEX ROWID| R_ATTRIBUTFELD       |     1 |    23 |     3   (0)| 00:00:01 |
  |  32 |         NESTED LOOPS              |                      |     6 |   264 |    97   (0)| 00:00:02 |
  |  33 |          NESTED LOOPS             |                      |    30 |   630 |    13   (0)| 00:00:01 |
  |* 34 |           INDEX RANGE SCAN        | TEST4                |     9 |    90 |     3   (0)| 00:00:01 |
  |* 35 |           INDEX RANGE SCAN        | TEST3                |     3 |    33 |     2   (0)| 00:00:01 |
  |* 36 |          INDEX RANGE SCAN         | IDX_R_ATTRIBUTFELD_1 |     6 |       |     2   (0)| 00:00:01 |
  |* 37 |         INDEX RANGE SCAN          | TEST6                |     1 |     8 |     8   (0)| 00:00:01 |
  ----------------------------------------------------------------------------------------------------------
  </code></pre>
  
  <p>A horrible, horrible cost estimate as this query completes instantly.</p>