如何使用10个以上的联接优化查询？

Question

我的应用程序使用单个查询来返回用户的所有权限，并且该单个查询具有10个INNER JOIN来创建整个结果集。

这是查询的预览（由于机密信息，我不得不更改表名）：

SELECT 
    TABLE9.CONTINENT, TABLE9.COD_COUNTRY, TABLE9.DES_COUNTRY, TABLE9.COD_ISO, 
    TABLE7.ID_DEL, TABLE7.COD_DEL, TABLE7.DES_DEL, TABLE7.DES_ZONE, TABLE7.GMT_MINUTES, 
    TABLE7.CANT_MIN_INI, TABLE7.CANT_MIN_SALIDA, TABLE7.CANT_MET_BASE, TABLE5.ID_TS, 
    TABLE5.COD_TS, TABLE2.ID_ROLE, TABLE2.TIMEOUT_SESION, TABLE11.ID_PERMISSION, 
    TABLE3.COD_APLICATION, TABLE3.DES_APLICATION, TABLE6.ID_PLANT, TABLE6.COD_PLANT, 
    TABLE6.DES_PLANT

FROM TABLE1

INNER JOIN TABLE2 ON TABLE2.ID_ROLE = TABLE1.ID_ROLE
INNER JOIN TABLE3 ON TABLE3.ID_APLICATION = TABLE2.ID_APLICATION 
 INNER JOIN TABLE4 ON TABLE4.ID_PTS = TABLE1.ID_PTS
INNER JOIN TABLE5 ON TABLE4.ID_TS = TABLE5.ID_TS
INNER JOIN TABLE6 ON TABLE6.ID_PLANT = TABLE4.ID_PLANT
INNER JOIN TABLE7 ON TABLE7.ID_DEL = TABLE6.ID_DEL 
 INNER JOIN TABLE8 ON (TABLE8.ID_USER = TABLE1.ID_USER)
INNER JOIN TABLE9 ON TABLE9.ID_COUNTRY = TABLE7.ID_COUNTRY
INNER JOIN TABLE10 ON TABLE10.ID_ROLE = TABLE2.ID_ROLE
INNER JOIN TABLE11 ON (TABLE11.ID_PERMISSION = TABLE10.ID_PERMISSION 
                              AND TABLE11.ID_APLICATION = TABLE3.ID_APLICATION)

 WHERE TABLE11.COD_PERMISSION <> 'PermissionCode'
   AND TABLE8.ID_USER_AD = 'e5def917-73e6-4b4e-8b5b-436794768c4b'
   AND TABLE8.BOL_ENABLED = 1

这是执行计划（创建一些索引后，成本降低了，但是返回58k行仍需要39秒）：

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                             | 129 |   118K|    62   (9)| 00:00:01 |
|   1 |  SORT ORDER BY                         |                             | 129 |   118K|    62   (9)| 00:00:01 |
|   2 |   NESTED LOOPS                         |                             | 129 |   118K|    61   (7)| 00:00:01 |
|*  3 |    HASH JOIN                           |                             |3461 |  2926K|    61   (7)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL                  | TABLE11                     | 262 | 24890 |     4   (0)| 00:00:01 |
|*  5 |     HASH JOIN                          |                             | 185 |   139K|    57   (8)| 00:00:01 |
|   6 |      TABLE ACCESS FULL                 | TABLE3                      |  14 |   840 |     4   (0)| 00:00:01 |
|*  7 |      HASH JOIN                         |                             | 185 |   128K|    52   (6)| 00:00:01 |
|   8 |       TABLE ACCESS FULL                | TABLE2                      |  65 |  5785 |     4   (0)| 00:00:01 |
|*  9 |       HASH JOIN                        |                             | 185 |   112K|    48   (7)| 00:00:01 |
|  10 |        TABLE ACCESS FULL               | TABLE5                      |  56 |  2800 |     4   (0)| 00:00:01 |
|* 11 |        HASH JOIN                       |                             | 185 |   103K|    43   (5)| 00:00:01 |
|  12 |         TABLE ACCESS FULL              | TABLE9                      |   1 |    70 |     3   (0)| 00:00:01 |
|* 13 |         HASH JOIN                      |                             | 185 | 92870 |    40   (5)| 00:00:01 |
|  14 |          TABLE ACCESS FULL             | TABLE7                      |  43 |  5375 |     3   (0)| 00:00:01 |
|* 15 |          HASH JOIN                     |                             | 185 | 69745 |    36   (3)| 00:00:01 |
|  16 |           TABLE ACCESS FULL            | TABLE6                      |  43 |  4128 |     3   (0)| 00:00:01 |
|* 17 |           HASH JOIN                    |                             | 185 | 51985 |    33   (4)| 00:00:01 |
|  18 |            NESTED LOOPS                |                             | 193 | 35126 |    20   (0)| 00:00:01 |
|* 19 |             TABLE ACCESS BY INDEX ROWID| TABLE8                      |   1 |    77 |     2   (0)| 00:00:01 |
|* 20 |              INDEX UNIQUE SCAN         | AK_TABLE8_2                 |   1 |       |     1   (0)| 00:00:01 |
|  21 |             TABLE ACCESS BY INDEX ROWID| ADPR_TABLE1                 | 193 | 20265 |    18   (0)| 00:00:01 |
|* 22 |              INDEX RANGE SCAN          | IX_TABLE1                   | 193 |       |     2   (0)| 00:00:01 |
|  23 |            INDEX FAST FULL SCAN        | IX_TABLE4                   |2281 |   220K|    12   (0)| 00:00:01 |
|* 24 |    INDEX UNIQUE SCAN                   | AK_TABLE10                  |   1 |    73 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------

我该怎么做才能改善此查询？

更新

这是我创建的索引：

create index IX_TABLE11 on TABLE11 (ID_PERMISSION, ID_APLICATION) ONLINE;
create index IX_TABLE8 on TABLE8 (ID_USER, ID_USER_AD, BOL_ACTIVE) ONLINE;
create index IX_TABLE6 on TABLE6 (ID_PLANT, ID_DEL) ONLINE;
create index IX_TABLE4 on TABLE4 (ID_PTS, ID_TS, ID_PLANT) ONLINE;
create index IX_TABLE2 on TABLE2 (ID_ROLE, ID_APLICATION) ONLINE;

Answer 1

感谢您添加索引的说明。要基于表8的主要条件优化查询，您希望与WHERE子句关联的列位于最前面，而辅助字段为AFTER。由于您的条件是通过“
Table8”针对特定用户的，因此我对查询进行了稍微的重组，以将其置于主要位置，并稍稍更新了WHERE。

我还在相应的表中添加了索引，以指出您提供的索引以及应该稍加调整/添加的索引。

SELECT 
      -- Columns   
   FROM 
      TABLE8 
         INNER JOIN TABLE1
            ON TABLE8.ID_USER = TABLE1.ID_USER
            INNER JOIN TABLE2 
               ON TABLE1.ID_ROLE = TABLE2.ID_ROLE
               INNER JOIN TABLE3 
                  ON TABLE2.ID_APLICATION  = TABLE3.ID_APLICATION
               INNER JOIN TABLE10 
                  ON TABLE2.ID_ROLE = TABLE10.ID_ROLE
                  INNER JOIN TABLE11 
                     ON TABLE10.ID_PERMISSION = TABLE11.ID_PERMISSION 
                     AND TABLE3.ID_APLICATION = TABLE11.ID_APLICATION
                     AND TABLE11.COD_PERMISSION <> 'PermissionCode'
            INNER JOIN TABLE4 
               ON TABLE1.ID_PTS = TABLE4.ID_PTS
               INNER JOIN TABLE5 
                  ON TABLE4.ID_TS = TABLE5.ID_TS
               INNER JOIN TABLE6 
                  ON TABLE4.ID_PLANT = TABLE6.ID_PLANT
                  INNER JOIN TABLE7 
                     ON TABLE6.ID_DEL = TABLE7.ID_DEL
                     INNER JOIN TABLE9 
                        ON TABLE7.ID_COUNTRY = TABLE9.ID_COUNTRY
   WHERE
          TABLE8.BOL_ENABLED = 1
      AND TABLE8.ID_USER_AD = 'e5def917-73e6-4b4e-8b5b-436794768c4b'




Table    Index
TABLE1   (ID_USER, ID_ROLE, ID_PTS)
TABLE2   (ID_ROLE, ID_APPLICATION)   <- index already exists
TABLE3   (ID_APLICATION )
TABLE4   (ID_PTS, ID_TS, ID_PLANT )  <-  index already exists
TABLE5   (ID_TS )
TABLE6   (ID_PLANT, ID_DEL)          <-  index already exists
TABLE7   (ID_DEL, ID_COUNTRY)
TABLE8   (ID_USER_AD, BOL_ENABLED, ID_USER )   <- Added BOL_ENABLED, ID_USER as LAST column index
TABLE10  (ID_ROLE, ID_PERMISSION )
TABLE11  (ID_PERMISSION, ID_APLICATION, COD_PERMISSION )  <-- add COD_PERMISSION

从调整后的索引来看，您对它的评论仍然需要太长时间，我将提供以下内容。看来您的应用程序是基于浏览器的。如果是这样，您的表具有特定的应用程序。我建议做的是以下几点。精简您的查询以获得一个人可以访问的DISTINCT应用程序。他们可能在屏幕上有一些东西可以让他们选择…。然后，一旦用户选择了他们想要的特定应用程序，然后运行查询，还包括他们选择的SINGLE应用程序的条件。因此，如果您有10个应用程序，则您的58k权限现在可能会减少到5-6k个记录。

因此，可以将第一个查询简化为用户可用的应用程序的代码和描述。

SELECT DISTINCT
      TABLE3.COD_APLICATION, 
      TABLE3.DES_APLICATION
   FROM 
      TABLE8 
         INNER JOIN TABLE1
            ON TABLE8.ID_USER = TABLE1.ID_USER
            INNER JOIN TABLE2 
               ON TABLE1.ID_ROLE = TABLE2.ID_ROLE
               INNER JOIN TABLE3 
                  ON TABLE2.ID_APLICATION  = TABLE3.ID_APLICATION
   WHERE
          TABLE8.BOL_ENABLED = 1
      AND TABLE8.ID_USER_AD = 'e5def917-73e6-4b4e-8b5b-436794768c4b'

然后，一旦从用户界面中选择了特定的应用程序，就将该特定的应用程序添加到主查询中（通知更改仅在连接到table2时进行）

SELECT DISTINCT
      TABLE9.CONTINENT, 
      TABLE9.COD_COUNTRY, 
      TABLE9.DES_COUNTRY, 
      TABLE9.COD_ISO, 
      TABLE7.ID_DEL, 
      TABLE7.COD_DEL, 
      TABLE7.DES_DEL, 
      TABLE7.DES_ZONE, 
      TABLE7.GMT_MINUTES, 
      TABLE7.CANT_MIN_INI, 
      TABLE7.CANT_MIN_SALIDA, 
      TABLE7.CANT_MET_BASE, 
      TABLE5.ID_TS, 
      TABLE5.COD_TS, 
      TABLE2.ID_ROLE, 
      TABLE2.TIMEOUT_SESION, 
      TABLE11.ID_PERMISSION, 
      TABLE3.COD_APLICATION, 
      TABLE3.DES_APLICATION, 
      TABLE6.ID_PLANT, 
      TABLE6.COD_PLANT, 
      TABLE6.DES_PLANT
   FROM 
      TABLE8 
         INNER JOIN TABLE1
            ON TABLE8.ID_USER = TABLE1.ID_USER


            INNER JOIN TABLE2 
               ON TABLE1.ID_ROLE = TABLE2.ID_ROLE
              AND TABLE2.ID_APLICATION = [specific application user selected]


               INNER JOIN TABLE3 
                  ON TABLE2.ID_APLICATION  = TABLE3.ID_APLICATION
               INNER JOIN TABLE10 
                  ON TABLE2.ID_ROLE = TABLE10.ID_ROLE
                  INNER JOIN TABLE11 
                     ON TABLE10.ID_PERMISSION = TABLE11.ID_PERMISSION 
                     AND TABLE3.ID_APLICATION = TABLE11.ID_APLICATION
                     AND TABLE11.COD_PERMISSION <> 'PermissionCode'
            INNER JOIN TABLE4 
               ON TABLE1.ID_PTS = TABLE4.ID_PTS
               INNER JOIN TABLE5 
                  ON TABLE4.ID_TS = TABLE5.ID_TS
               INNER JOIN TABLE6 
                  ON TABLE4.ID_PLANT = TABLE6.ID_PLANT
                  INNER JOIN TABLE7 
                     ON TABLE6.ID_DEL = TABLE7.ID_DEL
                     INNER JOIN TABLE9 
                        ON TABLE7.ID_COUNTRY = TABLE9.ID_COUNTRY
   WHERE
          TABLE8.BOL_ENABLED = 1
      AND TABLE8.ID_USER_AD = 'e5def917-73e6-4b4e-8b5b-436794768c4b'