sql - 如何优化这个耗时 30 秒 1746 行的查询？

Question

查询:

SELECT A.USER_ID, A.ROLE_ID, C.SUBGROUP, MAX(A.STATUS_ID)
                 FROM USER_ROLE A, USER B, ROLE C
                WHERE A.ROLE_ID = C.ROLE_ID
                  AND C.GROUP_ID = 3
                  AND A.USER_ID = B.USER_ID
                  AND B.TEMPLATE_IND = 'N'
                  AND B.ONAP_PARTCODE IS NULL
                  AND A.PARTCODE ='005'
                GROUP BY A.PARTCODE,
                         A.USER_ID,
                         A.ROLE_ID,
                         C.SUBGROUP;

解释计划:

--------------------------------------------------------------------------------
| Id  | Operation                      | Name               | Rows  | Bytes | Co
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                    |     1 |    74 |
|   1 |  HASH GROUP BY                 |                    |     1 |    74 |
|   2 |   NESTED LOOPS                 |                    |     1 |    74 |
|   3 |    NESTED LOOPS                |                    |    56 |  3024 |
|   4 |     TABLE ACCESS BY INDEX ROWID| ROLE               |     8 |   240 |
|*  5 |      INDEX RANGE SCAN          | N_ROLE_IDX2        |     8 |       |
|   6 |     TABLE ACCESS BY INDEX ROWID| USER_ROLE          |     7 |   168 |
|*  7 |      INDEX RANGE SCAN          | N_USER_ROLE_IDX6   |     7 |       |
|   8 |    REMOTE                      | MV_PT_USER         |     1 |    20 |
--------------------------------------------------------------------------------

Answer 1

我重新编写了您的查询以使用 ANSI-92 语法:

  SELECT A.USER_ID, A.ROLE_ID, C.SUBGROUP, MAX(A.STATUS_ID)
    FROM USER_ROLE a
    JOIN USER b ON b.user_id = a.user_id
               AND b.template_ind = 'N'
               AND b.onap_partcode IS NULL
    JOIN ROLE c ON c.role_id = a.role_id
               AND c.group_id = 3
   WHERE a.PARTCODE ='005'
GROUP BY a.USER_ID, a.ROLE_ID, c.SUBGROUP, a.PARTCODE;

这并没有更快，只是对我来说更清楚 suggest the following covering indexes :

CREATE INDEX ur_idx ON USER_ROLE (user_id, role_id, partcode) COMPUTE STATISTICS;
CREATE INDEX u_idx ON USER (user_id, template_ind) COMPUTE STATISTICS;
CREATE INDEX r_idx ON ROLE (role_id, group_id) COMPUTE STATISTICS;