sql - Oracle - 查询运行速度很慢

Question

我有一个永远运行的简单查询。有一个日期条件，一旦我删除，查询就会立即返回结果。它是格式为“31-MAR-15”的日期字段。我不明白为什么这种情况会使查询如此缓慢。提前致谢。

SELECT
  substr(a.id, 1, 2)   AS country,
  count(DISTINCT a.id) AS id_count,
  sum(a.amount)        AS amount
FROM table1 a
  JOIN table2 b ON a.id = b.id
  JOIN table3 c ON b.party_id = c.party_id
WHERE a.prod_type = 'INS'
  AND c.acct_type = 'LON'
  AND substr(a.id, 1, 2) = 'US'
  AND a.dump_dt = '31-MAR-15'
  AND substr(id, 4, 8) = '20150303'
GROUP BY substr(a.id, 1, 2);

解释计划:

PLAN_TABLE_OUTPUT
Plan hash value: 255044277

------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                        |     1 |   121 |   125K  (1)| 00:25:08 |
|   1 |  HASH GROUP BY                    |                        |     1 |   121 |   125K  (1)| 00:25:08 |
|   2 |   VIEW                            | VW_DAG_0               |     1 |   121 |   125K  (1)| 00:25:08 |
|   3 |    HASH GROUP BY                  |                        |     1 |    98 |   125K  (1)| 00:25:08 |
|   4 |     NESTED LOOPS                  |                        |       |       |            |          |
|   5 |      NESTED LOOPS                 |                        |     1 |    98 |   125K  (1)| 00:25:08 |
|   6 |       MERGE JOIN CARTESIAN        |                        | 12613 |   800K| 21133   (2)| 00:04:14 |
|*  7 |        TABLE ACCESS BY INDEX ROWID| TABLE1                 |     1 |    45 |    46   (0)| 00:00:01 |
|*  8 |         INDEX RANGE SCAN          | DATA_DATE__STG_BACKUP2 |  1040 |       |     6   (0)| 00:00:01 |
|   9 |        BUFFER SORT                |                        |   182K|  3564K| 21087   (2)| 00:04:14 |
|* 10 |         TABLE ACCESS FULL         | TABLE3                 |   182K|  3564K| 21087   (2)| 00:04:14 |
|* 11 |       INDEX RANGE SCAN            | BSB_PARTYID_IDX        |    22 |       |     3   (0)| 00:00:01 |
|* 12 |      TABLE ACCESS BY INDEX ROWID  | TABLE2                 |     1 |    33 |    10   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

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

   7-filter(SUBSTR(A.ID, 4, 8) = '20150303' AND SUBSTR(A.ID, 1, 2) = 'US'
              AND A.PROD_TYPE = 'INS')
   8 - access(A.DUMP_DT = '31-MAR-15')
  10 - filter(C.ACCT_TYPE = 'LON')
  11 – access(B.PARTY_ID = C.PARTY_ID)
  12 - filter(A.ID = B.ID)

Answer 1

看起来优化器明显低估了在 TABLE1 上应用这 4 个谓词后返回的行数。

A.PROD_TYPE = 'INS'
SUBSTR(A.ID, 1, 2) = 'US'
A.DUMP_DT = '31-MAR-15'
SUBSTR(ID, 4, 8) = '20150303'

(稍微偏离主题:使用 ANSI 文字 date '2015-03-31' 而不是隐式转换的字符串 '31-MAR-15' 更安全code>。并且该语句有一些错误，例如在前 2 个谓词之间缺少条件以及在最后一个谓词前面缺少 A.。)

首先，确保所有表都有准确的统计信息，看看这是否会改变解释计划:

begin
    dbms_stats.gather_table_stats(user, 'TABLE1');
    dbms_stats.gather_table_stats(user, 'TABLE2');
    dbms_stats.gather_table_stats(user, 'TABLE3');
end;
/

“智能列”ID 使得估计应用条件后返回的行数变得困难。如果更改数据模型为时已晚，您至少可以向 Oracle 提供一些扩展统计信息以帮助它处理谓词:

select dbms_stats.create_extended_stats(user, 'TABLE1', '(SUBSTR(ID, 1, 2))') from dual;
select dbms_stats.create_extended_stats(user, 'TABLE1', '(SUBSTR(ID, 4, 8))') from dual;

我猜测 SUBSTR(A.ID, 1, 2) = 'US' 是一个流行的值，但如果没有扩展统计信息，Oracle 将不会知道这一点。额外的直方图可能会显着增加基数。那么优化器就不会选择两个不相关的表之间的笛卡尔连接。