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

Thaylon

我需要帮助来迫使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 |
----------------------------------------------------------------------------------------------------------

当此查询立即完成时，一个可怕的，可怕的成本估算。

埃利亚图

如果要避免对r_attributfeld进行完全表访问，则应在此表上尝试嵌套循环。如果Oracle“不了解”您想要的内容，也许您必须要有一个领导（r_rapport r_attributfeld）（或有序的提示）

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。

编辑于 2021-03-20

我来说两句

0 条评论

登录后参与评论

上一篇：如何识别使用fstat创建主要修订的用户？

TOP 榜单

文章

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

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

蓝屏死机没有修复解决方案

计算数据帧中每行的NA

UITableView的项目向下滚动后更改颜色，然后快速备份

Node.js中未捕获的异常错误，发生调用

在 Python 2.7 中。如何从文件中读取特定文本并分配给变量

Linux的官方Adobe Flash存储库是否已过时？

验证REST API参数

ggplot：对齐多个分面图-所有大小不同的分面

Mac OS X更新后的GRUB 2问题

通过 Git 在运行 Jenkins 作业时获取 ClassNotFoundException

带有错误“ where”条件的查询如何返回结果？

用日期数据透视表和日期顺序查询

VB.net将2条特定行导出到DataGridView

如何从视图一次更新多行（ASP.NET - Core）

Java Eclipse中的错误13，如何解决？

尝试反复更改屏幕上按钮的位置 - kotlin android studio

离子动态工具栏背景色

应用发明者仅从列表中选择一个随机项一次

当我尝试下载 StanfordNLP en 模型时，出现错误

python中的boto3文件上传

在同一Pushwoosh应用程序上Pushwoosh多个捆绑ID