时间戳

首页 > 解决方案 > 通过 FETCH NEXT 子句加速 WHERE EXISTS 子句

sql - 通过 FETCH NEXT 子句加速 WHERE EXISTS 子句

问题描述

我有一个长期运行的 Oracle Query，它使用了一堆：

WHERE EXISTS (SELECT NULL FROM Table WHERE TableColumn IN (...))

而不是 using SELECT NULL，它遍历整个表来查找标准，我不能只放在FETCH NEXT 1 ROW ONLY它之后，因为我只关心 if TableColumnisIN (...)吗？

像这样：

WHERE EXISTS (SELECT NULL FROM Table WHERE TableColumn IN (...) FETCH NEXT 1 ROW ONLY)

因此，WHERE EXISTS将更快地评估。

编辑：

下面是没有FETCH NEXT附加子句的查询计划：

------------------------------------------------------------------------------------------------
| Id | Operation                     | Name            | Rows      | Bytes     | Cost   | Time |
------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |                 |         1 |        75 | 521611 |      |
|  1 |   SORT AGGREGATE              |                 |         1 |        75 |        |      |
|  2 |    HASH JOIN                  |                 |    531266 |  39844950 | 521611 |      |
|  3 |     TABLE ACCESS FULL         | ACCT            |     47574 |    523314 |    418 |      |
|  4 |     HASH JOIN                 |                 |    531224 |  33998336 | 521185 |      |
|  5 |      INDEX FAST FULL SCAN     | PK_ACTVTYP      |       454 |      2270 |      2 |      |
|  6 |      HASH JOIN                |                 |    531224 |  31342216 | 521177 |      |
|  7 |       INDEX FULL SCAN         | PK_ACTVCAT      |        67 |       335 |      1 |      |
|  8 |       HASH JOIN SEMI          |                 |    531224 |  28686096 | 521169 |      |
|  9 |        NESTED LOOPS SEMI      |                 |    531224 |  28686096 | 521169 |      |
| 10 |         STATISTICS COLLECTOR  |                 |           |           |        |      |
| 11 |          HASH JOIN RIGHT SEMI |                 |    531224 |  25498752 | 112887 |      |
| 12 |           TABLE ACCESS FULL   | AMSACTVGRPEMPL  |      2364 |     35460 |     10 |      |
| 13 |           TABLE ACCESS FULL   | ACTV            |  12779986 | 421739538 | 112712 |      |
| 14 |         INDEX RANGE SCAN      | ACTVSUBACTV_DX2 | 163091724 | 978550344 | 251246 |      |
| 15 |        INDEX FAST FULL SCAN   | ACTVSUBACTV_DX2 | 163091724 | 978550344 | 251246 |      |
------------------------------------------------------------------------------------------------

以下是FETCH NEXT附加子句的查询计划：

------------------------------------------------------------------------------------------------
| Id | Operation                      | Name            | Rows     | Bytes     | Cost   | Time |
------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT               |                 |        1 |        69 | 113148 |      |
|  1 |   SORT AGGREGATE               |                 |        1 |        69 |        |      |
|  2 |    FILTER                      |                 |          |           |        |      |
|  3 |     HASH JOIN                  |                 |   531221 |  36654249 | 113144 |      |
|  4 |      TABLE ACCESS FULL         | ACCT            |    47574 |    523314 |    418 |      |
|  5 |      HASH JOIN                 |                 |   531179 |  30808382 | 112718 |      |
|  6 |       INDEX FAST FULL SCAN     | PK_ACTVTYP      |      454 |      2270 |      2 |      |
|  7 |       HASH JOIN                |                 |   531179 |  28152487 | 112710 |      |
|  8 |        INDEX FULL SCAN         | PK_ACTVCAT      |       67 |       335 |      1 |      |
|  9 |        HASH JOIN RIGHT SEMI    |                 |   531179 |  25496592 | 112702 |      |
| 10 |         TABLE ACCESS FULL      | AMSACTVGRPEMPL  |     2167 |     32505 |     10 |      |
| 11 |         TABLE ACCESS FULL      | ACTV            | 12778893 | 421703469 | 112527 |      |
| 12 |     VIEW                       |                 |        1 |        13 |      4 |      |
| 13 |      WINDOW BUFFER PUSHED RANK |                 |        8 |        48 |      4 |      |
| 14 |       INDEX RANGE SCAN         | ACTVSUBACTV_DX2 |        8 |        48 |      4 |      |
------------------------------------------------------------------------------------------------

从我所见，它看起来好像没有FETCH NEXT增加更多的开销TABLE ACCESS FULL

编辑#2

添加AND ROWNUM = 1而不是FETCH NEXT 1 ROW ONLY：

------------------------------------------------------------------------------------------------
| Id | Operation                    | Name              | Rows     | Bytes     | Cost   | Time |
------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |                   |        1 |        54 | 128114 |      |
|  1 |   SORT AGGREGATE             |                   |        1 |        54 |        |      |
|  2 |    FILTER                    |                   |          |           |        |      |
|  3 |     HASH JOIN                |                   | 12779902 | 690114708 | 113296 |      |
|  4 |      TABLE ACCESS FULL       | ACCT              |    47574 |    523314 |    418 |      |
|  5 |      HASH JOIN               |                   | 12778893 | 549492399 | 112713 |      |
|  6 |       MERGE JOIN CARTESIAN   |                   |    30418 |    304180 |     31 |      |
|  7 |        INDEX FULL SCAN       | PK_ACTVCAT        |       67 |       335 |      1 |      |
|  8 |        BUFFER SORT           |                   |      454 |      2270 |     30 |      |
|  9 |         INDEX FAST FULL SCAN | PK_ACTVTYP        |      454 |      2270 |      0 |      |
| 10 |       TABLE ACCESS FULL      | ACTV              | 12778893 | 421703469 | 112517 |      |
| 11 |     COUNT STOPKEY            |                   |          |           |        |      |
| 12 |      INLIST ITERATOR         |                   |          |           |        |      |
| 13 |       INDEX UNIQUE SCAN      | PK_AMSACTVGRPEMPL |        1 |        15 |      2 |      |
| 14 |     COUNT STOPKEY            |                   |          |           |        |      |
| 15 |      INDEX RANGE SCAN        | ACTVSUBACTV_DX2   |        2 |        12 |      4 |      |
------------------------------------------------------------------------------------------------

标签： sqloraclequery-optimization

解决方案

这FETCH NEXT是 12c 中的新功能，为了避免性能问题导致它添加如下提示

 WHERE EXISTS (SELECT /*+ first_rows(1)*/* FROM Table WHERE TableColumn IN (...) FETCH NEXT 1 ROW ONLY)

试一试并检查它的查询计划

注意：我建议在 ACCT ,ACTV 表上添加索引以提高其性能。

推荐阅读