oracle – 改进SQL存在可伸缩性

假设我们有两个表,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个结果,无论表有10个匹配值还是1,000,000;因为它可以从子表中获得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")

在STOPKEY下排序UNIQUE,afaik意味着它正在读取子表中的所有结果,使得distinct最终只选择前10个,使得查询不像我们希望的那样可扩展.

我的例子中有任何错误吗？

是否有可能改进这个执行计划,以便更好地扩展？

解决方法

无论如何,只要’string％’匹配TEST_CHILD中相对较少的行数,您的表现就应该没问题.如果您的情况是’string％’经常匹配TEST_CHILD上的巨大记录数,那么在给定当前表的情况下,您可以做的事情并不多,以使SQL更高效.在这种情况下,如果这是一个任务关键型SQL,其性能与您的年度奖金相关,那么您可以使用MATERIALIZED VIEW来做一些花哨的步法,例如：在TEST_CHILD中为高基数WORD2值预先计算10个TEST行.

最后一个想法 – 一个“冒险”的解决方案,但如果你没有数千个与同一个TEST行匹配的TEST_CHILD行,它应该可以工作,如下所示：

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行的最终结果.

（编辑：东莞站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!