How do I force access by index rowid in Oracle?

Question

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:

---------------------------------------------------------------------------------------------------------
| 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 |
---------------------------------------------------------------------------------------------------------

By adding a FIRST_ROWS(1) hint, this changes to the much more desired plan:

----------------------------------------------------------------------------------------------------------
| 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 |
----------------------------------------------------------------------------------------------------------

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.

I tried using ROWID hints on both tables, before discovering those have been deprecated.

Any pointers will be appreciated.

=edit=

USE_NL & new Index

-----------------------------------------------------------------------------------------------------------
| 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 |
-----------------------------------------------------------------------------------------------------------

SQL:

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 <= 101;

and its plan:

----------------------------------------------------------------------------------------------------------
| 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 |
----------------------------------------------------------------------------------------------------------

A horrible, horrible cost estimate as this query completes instantly.

If you want to force the use of an index, I'd start with the `INDEX` hint. Taking a step back, though, are you certain that you can't resolve the problem by fixing whatever inaccuracy in your statistics causes the optimizer to believe that the slower plan will be more efficient? Particularly if you're dynamically assembling queries, specifying individual hints is likely to be rather problematic. — Justin Cave, May 09 '14 at 16:28
@JustinCave - all good points. You might want to be sure that table statistics are current/accurate. Older databases allow rule-based optimization "forcing". What Oracle version? — jim mcnamara, May 09 '14 at 21:13
The Oracle version in use is 10.2. I tried various index hints which didnt do anything :/ Afair statistics are updated regularly but I dont have rights to do it myself; will have to check that. — Thaylon, May 10 '14 at 13:24
Can you post some of SQL code with the hints? Hints are directives that will be followed by the optimizer if possible, they are not simply "ignored" for no good reason. If a hint does not work it's usually a syntax problem. The most common issue is that a hint must reference the alias, not the original table name. — Jon Heller, May 10 '14 at 13:38
I also updated schema statistics by: exec dbms_stats.gather_schema_stats( ownname => '&ownername',estimate_percent => 20, method_opt => 'for all columns size auto',options => 'Gather' ,cascade => true,degree => 4); — Thaylon, May 12 '14 at 15:51

score 1 · Accepted Answer · answered May 10 '14 at 11:28

1

If you want to avoid table access full on r_attributfeld, you should try a nested loop on this table. Maybe you'll have to had a leading(r_rapport r_attributfeld) (or ordered hint) if Oracle "don't understand" what you want

answered May 10 '14 at 11:28

eliatou

744
5
12

Join order didnt seem to be the problem to me, isnt it basically the same in both plans except the good using access by index rowid and the ugly by full table scans? Anyway, thanks, will try that next monday. – Thaylon May 10 '14 at 13:33
If I understand well your problem, r_rapport is the leading table. If r_attributfeld is well indexed, I'm sure a nested loop will be the solution. Could we have the full text of your query and a description of the tables? Of course it will be more complicated because your table seems to be inside view. Maybe for that we need to have a push-pred hint. But this is conjecture. Without the text it'll be difficult to find something! – eliatou May 10 '14 at 18:52
Thanks everyone! The combination of FIRST_ROWS(1) in the outermost query and USE_NL( c d ) in the innermost sub-queries was the solution. Will accept this answer for pointing out that I could do a nested loop hint, which I wasnt aware of. – Thaylon May 12 '14 at 11:35
A last detail.I don't understand why first_rows hint is mandatory in your case (I'm alway's for writing minimal hint). /*+ use_nl(d) */ where d represent the bloc including r_attributfeld doesn't work? – eliatou May 12 '14 at 14:05
I understand the motion :) Tried avoiding hints entirely at first and kept rewriting the query, but it would always freak out under certain circumstances. Even with USE_NL on r_attributfeld I didnt get the planner to do an index full scan with an index hint on r_rapport. – Thaylon May 12 '14 at 15:06
By providing a better index on r_rapport I could remove the FIRST_ROWS, but as soon as I have more than two exists subqueries its back to a full table scan on r_rapport. No idea why the number of exists are deemed so costly. – Thaylon May 12 '14 at 15:39

score -1 · Answer 2 · answered Jul 13 '16 at 06:02

Not always what you think is better plan is realy better plan. To return you first rows fast is right way to use nested loops and rowid acces. But if you want return all result set fast it is more efficient to use hash join even if you are joining by rowid("select * from tab where rowid in (...)" or "... join tab on tab.rowid = ...'). So I think Oracle picked right plan for you.

How do I force access by index rowid in Oracle?

2 Answers2