SQL脚本运行速度非常缓慢，变化很小

Question

我对SQL相对较新。我有一个脚本，运行速度非常快（< 0.5秒），但如果我添加一个更改，则运行速度非常缓慢（> 120秒） - 而且我不明白为什么此更改会产生如此差异。任何帮助将非常感激！

这是脚本，它跑得快，如果我不包括“tt2.bulk_cnt ”在第26行：

with bulksum1 as 
(
select t1.membercode, 
     t1.schemecode, 
     t1.transdate 
from mina_raw2 t1 
where t1.transactiontype in ('RSP','SP','UNTV','ASTR','CN','TVIN','UCON','TRAS') 
group by t1.membercode, 
      t1.schemecode, 
      t1.transdate 
), 

bulksum2 as 
(
select t1.schemecode, 
     t1.transdate, 
     count(*) as bulk_cnt 
from bulksum1 t1 
group by t1.schemecode, 
      t1.transdate 
having count(*) >= 10 
), 

results as 
(
select t1.*, tt2.bulk_cnt 
from mina_raw2 t1 
inner join bulksum2 tt2 
on t1.schemecode = tt2.schemecode and t1.transdate = tt2.transdate 
where t1.transactiontype in ('RSP','SP','UNTV','ASTR','CN','TVIN','UCON','TRAS') 
) 

select * from results 

编辑：我在没有把足够的细节，这里以前道歉 - 虽然我使用基本的SQL代码，对于数据库，我是一个完全新手。

数据库：Oracle（我不知道哪个版本，抱歉）

执行计划：

快速查询：

Plan hash value: 1712123489 

--------------------------------------------- 
| Id | Operation    | Name  | 
--------------------------------------------- 
| 0 | SELECT STATEMENT  |   | 
| 1 | HASH JOIN    |   | 
| 2 | VIEW     |   | 
| 3 | FILTER    |   | 
| 4 |  HASH GROUP BY  |   | 
| 5 |  VIEW    | VM_NWVW_0 | 
| 6 |  HASH GROUP BY  |   | 
| 7 |  TABLE ACCESS FULL| MINA_RAW2 | 
| 8 | TABLE ACCESS FULL  | MINA_RAW2 | 
--------------------------------------------- 

慢查询：

Plan hash value: 1298175315 

-------------------------------------------- 
| Id | Operation    | Name  | 
-------------------------------------------- 
| 0 | SELECT STATEMENT  |   | 
| 1 | FILTER    |   | 
| 2 | HASH GROUP BY  |   | 
| 3 | HASH JOIN   |   | 
| 4 |  VIEW    | VM_NWVW_0 | 
| 5 |  HASH GROUP BY  |   | 
| 6 |  TABLE ACCESS FULL| MINA_RAW2 | 
| 7 |  TABLE ACCESS FULL | MINA_RAW2 | 
-------------------------------------------- 

Answer 1

一些观察，然后做一些事情：

1）需要更多信息。特别是，MINA_RAW2表中有多少行，此表上有哪些索引，以及最后一次分析的时间是？为了确定这些问题的答案，请运行：

SELECT COUNT(*) FROM MINA_RAW2; 

SELECT TABLE_NAME, LAST_ANALYZED, NUM_ROWS 
    FROM USER_TABLES 
    WHERE TABLE_NAME = 'MINA_RAW2'; 

从看计划输出，它看起来像数据库正在做MINA_RAW2两个全扫描 - 这将是很好，如果这可以减少到不超过一个，希望没有。如果没有关于表中数据的非常详细的信息，总是很难说，但是初看起来，TRANSACTIONTYPE上的索引可能会有帮助。如果这样的索引不存在，您可能需要考虑添加它。 2）假设统计数据过期（如旧，不存在或自上次分析以来已添加，删除或更新了大量数据（> 10％））运行如下：

BEGIN 
    DBMS_STATS.GATHER_TABLE_STATS(owner => 'YOUR-SCHEMA-NAME', 
           table_name => 'MINA_RAW2'); 
END; 

用正确的模式名替换上面的“YOUR-SCHEMA-NAME”。请记住大写模式名称！如果您不知道您是否应该收集统计数据，请谨慎行事，并做到这一点。它不应该花费太多时间。

3）更新表统计信息后重新尝试您的现有查询。我认为在数据库中使用最新的统计数据很有可能解决您的问题。如果不是：

4）此查询正在对GROUP BY的结果执行GROUP BY。这似乎不是必要的，因为初始GROUP BY没有进行任何分组 - 相反，看起来这是为了获得MEMBERCODE，SCHEMECODE和TRANSDATE的唯一组合，以便通过scheme和日期可以确定。我认为整个查询可以简化为：

WITH cteWORKING_TRANS AS (SELECT * 
          FROM MINA_RAW2 
          WHERE TRANSACTIONTYPE IN ('RSP','SP','UNTV', 
                 'ASTR','CN','TVIN', 
                 'UCON','TRAS')), 
    cteBULKSUM AS (SELECT a.SCHEMECODE, 
          a.TRANSDATE, 
          COUNT(*) AS BULK_CNT 
         FROM (SELECT DISTINCT MEMBERCODE, 
              SCHEMECODE, 
              TRANSDATE 
           FROM cteWORKING_TRANS) a 
         GROUP BY a.SCHEMECODE, 
           a.TRANSDATE) 
SELECT t.*, b.BULK_CNT 
    FROM cteWORKING_TRANS t 
    INNER JOIN cteBULKSUM b 
    ON b.SCHEMECODE = t.SCHEMECODE AND 
     b.TRANSDATE = t.TRANSDATE 

Answer 2

我设法删除一个不必要的子查询，但这个语法distinct里面的count可能无法在PostgreSQL之外工作，或者可能不是理想的结果。我知道我当然在那里使用它。

select t1.*, tt2.bulk_cnt 
from mina_raw2 t1 
inner join (select t2.schemecode, 
     t2.transdate, 
     count(DISTINCT membercode) as bulk_cnt 
from mina_raw2 t2 
where t2.transactiontype in ('RSP','SP','UNTV','ASTR','CN','TVIN','UCON','TRAS') 
group by t2.schemecode, 
      t2.transdate 
having count(DISTINCT membercode) >= 10) tt2 
on t1.schemecode = tt2.schemecode and t1.transdate = tt2.transdate 
where t1.transactiontype in ('RSP','SP','UNTV','ASTR','CN','TVIN','UCON','TRAS') 

当你使用这些with查询，而不是当你不需要，你kneecapping查询优化子查询。