oracle - 提高 SQL Exists 的可扩展性

Question

假设我们有两个表，TEST 和 TEST_CHILDS，如下所示:

creat TABLE TEST(id1 number PRIMARY KEY, word VARCHAR(50),numero number);
creat TABLE TEST_CHILD (id2 number references test(id), word2 VARCHAR(50));

CREATE INDEX TEST_IDX ON TEST_CHILD(word2);
CREATE INDEX TEST_JOIN_IDX ON TEST_CHILD(id);

insert into TEST SELECT ROWNUM,U1.USERNAME||U2.TABLE_NAME, LENGTH(U1.USERNAME) FROM ALL_USERS U1,ALL_TABLES U2;
INSERT INTO TEST_CHILD SELECT MOD(ROWNUM,15000)+1,U1.USER_ID||U2.TABLE_NAME FROM ALL_USERS U1,ALL_TABLES U2;

我们想要查询以从 TEST 表中获取满足子表中某些条件的行，因此我们选择:

SELECT /*+FIRST_ROWS(10)*/* FROM TEST T WHERE EXISTS (SELECT NULL FROM TEST_CHILD TC WHERE word2 like 'string%' AND TC.id = T.id ) AND ROWNUM < 10;

我们总是只想要前 10 个结果，而不是更多。因此，无论表有 10 个匹配值还是 1,000,000 个匹配值，我们都希望获得相同的响应时间来读取 10 个结果；因为它可以从子表中获取 10 个不同的结果，并获取父表上的值(或者至少这是我们想要的计划)。但是当检查实际的执行计划时我们看到:

-----------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |     1 |    54 |     5  (20)| 00:00:01 |
|*  1 |  COUNT STOPKEY                  |             |       |       |            |          |
|   2 |   NESTED LOOPS                  |             |       |       |            |          |
|   3 |    NESTED LOOPS                 |             |     1 |    54 |     5  (20)| 00:00:01 |
|   4 |     SORT UNIQUE                 |             |     1 |    23 |     3   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| TEST_CHILD  |     1 |    23 |     3   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | TEST_IDX    |     1 |       |     2   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN           | SYS_C005145 |     1 |       |     0   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID  | TEST        |     1 |    31 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<10)
   6 - access("WORD2" LIKE 'string%')
       filter("WORD2" LIKE 'string%')
   7 - access("TC"."ID"="T"."ID")

SORT UNIQUE在STOPKEY下，据我所知，它正在从子表中读取所有结果，使distinct最终只选择前10个，从而使查询的可扩展性不如我们希望的那样。

我的例子有什么错误吗？

是否可以改进此执行计划，使其能够更好地扩展？

Answer 1

SORT UNIQUE 将查找并排序 TEST_CHILD 中与“string%”匹配的所有记录 - 它不会读取子表中的所有结果。你的逻辑需要这个。如果您只从 TEST_CHILD 中选择了与“string%”匹配的前 10 行，并且这 10 行都具有相同的 ID，那么 TEST 的最终结果将只有 1 行。

无论如何，只要“string%”与 TEST_CHILD 中相对较少的行数匹配，您的性能就应该很好。如果您的情况是“string%”经常与 TEST_CHILD 上的大量记录匹配，则在给定当前表的情况下，您无法采取太多措施来提高 SQL 的性能。在这种情况下，如果这是一个关键任务 SQL，并且性能与您的年度奖金相关，那么您可能可以使用物化 View 执行一些奇特的操作，例如为 TEST_CHILD 中的高基数 WORD2 值预先计算 10 个 TEST 行。

最后一个想法 - 一种“有风险”的解决方案，但如果您没有数千个 TEST_CHILD 行与同一 TEST 行匹配，则该解决方案应该有效，如下所示:

SELECT  * 
FROM    TEST 
WHERE   ID1 IN 
            (SELECT ID2 
             FROM   TEST_CHILD 
             WHERE  word2 like 'string%' 
                    AND ROWNUM < 1000) 
         AND ROWNUM <10;

当然，您可以向上或向下调整 1000，但如果太低，您可能会发现少于 10 个不同的 ID 值，这会给您带来少于 10 行的最终结果。