如何使用10+联接优化查询？

Question

我的应用程序使用一个查询从用户返回所有权限，这个查询有10个内部联接来创建整个结果集。

以下是查询的预览(由于机密信息，我不得不更改表名)：

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

感谢添加的索引说明。要根据Table8的主要条件优化查询，您需要预先关联WHERE子句的列，以及后面的辅助字段。因为您的标准是关于通过"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

根据调整后的指数，以及您对此的评论仍然需要太长时间，我将提供以下内容。您的应用程序似乎是基于浏览器的。如果是这样，则您的表具有特定的应用程序。我建议做以下几点。删除您的查询，以获得一个人可以访问的不同应用程序。他们可能在屏幕上有什么东西可以让他们选择..。然后，一旦用户选择了他们想要的特定应用程序，然后运行查询，但也包括他们选择的单个应用程序的条件。因此，如果您有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'

Answer 2

假设您只使用大多数中间表来连接，而不是从它们中提取数据，那么在每个表中创建额外的索引，将两个ids都索引到一个索引中。这样，当db收集数据以进行连接时，它会在索引中查找，然后得到所需的所有数据。所有这些“表访问完整”记录都消失了，您将只有索引访问权限。

示例索引：

TABLE2 (ID_ROLE, ID_APLICATION)
TABLE4 (ID_PTS, ID_TS)
TABLE6 (ID_PLANT, ID_DEL)
etc...

Answer 3

几点建议：

1. 确保Oracle正在处理它可以使用的所有信息--您有所有表的最新统计信息吗？
2. 检查SELECT中实际返回的列-如果不需要数据，Oracle将优化一些联接。
3. 确保在每个联接表的联接列上都有必要的索引(并确保索引的统计信息是最新的)。有许多完整的表扫描，但目前很难说Oracle是选择它们作为表大小的最佳选项，还是有更好的索引选项。