如何在Oracle中强制按索引rowid访问？

Question

我需要帮助强制Oracle始终使用对"r_rapport"表（约60k行）的索引行id的表访问，以此避免对"r_attributfeld"表（约8m行）进行完全扫描。我的查询结果如下所示：

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

通过添加FIRST_ROWS(1)提示，这将转换为更理想的计划:

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

很遗憾，这个查询是在运行时组合的。如果有三个子请求而不是这里的两个，则它将忽略FIRST_ROWS提示，并再次对800万行进行多次全表扫描。根据给定的数据分布，通过ROWID访问将始终更快（几乎瞬间完成），而Oracle偏爱的计划需要几秒钟。

我尝试在两个表上使用ROWID提示，后来发现这些提示已被弃用。

任何指针将不胜感激。

=编辑=

USE_NL和新索引

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

并且它的计划：

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

一份糟糕的成本估算，因为这个查询立即完成。

- Thaylon

---

如果你想强制使用索引，我建议从 INDEX 提示开始尝试。但退一步说，你确定不能通过修复统计数据中的任何不准确之处来解决问题，以便让优化器相信较慢的计划更有效吗？特别是如果你正在动态组装查询，指定单个提示可能会很棘手。 - Justin Cave

@JustinCave - 所有的观点都很好。您可能需要确保表统计信息是最新/准确的。旧的数据库允许基于规则的优化“强制”。Oracle版本是什么？ - jim mcnamara

正在使用的Oracle版本是10.2。我尝试了各种索引提示，但没有任何作用:/据我所知，统计信息定期更新，但我没有权限自己执行；我将不得不检查一下。 - Thaylon

你能发布一些带有提示的SQL代码吗？提示是指令，如果可能的话，优化器将遵循它们，它们不会仅仅因为没有好的理由而被“忽略”。如果提示无效，通常是语法问题。最常见的问题是提示必须引用别名，而不是原始表名。 - Jon Heller

我还通过以下方式更新了模式统计信息：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

Answer 1

如果你想避免在r_attributfeld表上进行全表扫描，应该尝试对该表进行嵌套循环。如果Oracle不理解你的意思，可能需要添加一个前导（r_rapport r_attributfeld）（或有序提示）。

- eliatou

---

连接顺序对我来说似乎不是问题，两个计划基本相同，除了使用索引rowid访问数据的好计划和使用全表扫描的丑计划。无论如何，谢谢，下周一会尝试这个方法。 - Thaylon

如果我理解你的问题正确，r_rapport是主表。如果r_attributfeld索引良好，我相信嵌套循环将是解决方案。我们能否获得您查询的完整文本和表的描述？当然，这将更加复杂，因为您的表似乎在视图内部。也许为此我们需要一个推送预测提示。但这只是猜测。没有文本，找到东西将会很困难！ - eliatou

谢谢大家！在最外层查询中使用FIRST_ROWS(1)，并在最内层子查询中使用USE_NL(c d)的组合是解决方案。感谢指出我可以使用嵌套循环提示，这是我不知道的。 - Thaylon

最后一个细节。我不明白为什么在你的情况下，first_rows提示是强制性的（我总是倾向于编写最小的提示）。/*+ use_nl(d) */其中d代表包括r_attributfeld的块不起作用吗？ - eliatou

我理解这个动作 :) 起初尝试完全避免提示并不断重写查询，但在某些情况下它总是会出现问题。即使在 r_attributfeld 上使用 USE_NL，我也没有让规划器在 r_rapport 上使用索引提示进行索引全扫描。 - Thaylon

通过在r_rapport上提供更好的索引，我可以删除FIRST_ROWS，但是一旦我有两个以上的exists子查询，它就会回到对r_rapport进行完全表扫描。不知道为什么存在的数量被认为是如此昂贵。 - Thaylon

Answer 2

并不是你认为的更好的计划就一定是真正更好的计划。要快速返回第一行，正确的方法是使用嵌套循环和rowid访问。但是，如果您想快速返回所有结果集，则使用哈希连接更有效，即使您是通过rowid进行连接（“select * from tab where rowid in (...)”或“... join tab on tab.rowid = ...”）。因此，我认为Oracle为您选择了正确的计划。

- Žilvinas