SQL 查询在连接时挂起

Question

我编写了一个 SQL 查询，它为每个年-周-矿产品生成一些统计信息的报告。

它完全按照预期工作，除了一件事 - trn.wid-date 不是要使用的正确日期。

我应该使用td.datetime-act-comp-dump。 当我用 td.datetime-act-comp-dump 替换 trn.wid-date 时，它不会给我任何错误，但似乎无限期地挂起。 昨天我让它放了一段时间，然后它返回了 ORA-01652 无法将表空间 TEMP 中的临时段扩展 128，尽管此后我没有看到该错误。

考虑到我能够在下面的查询中成功返回 MAX(td.datetime-act-comp-dump)

select to_char(trn.wid_date, 'IYYY') as dump_year,
       to_char(trn.wid_date-7/24, 'IW') as dump_week,
       SUBSTR(trn.train_control_id,1,2) as Mine,
       vcon.product_type_code as Product,
       COUNT(DISTINCT trn.train_control_id) as Trains,
       COUNT(1) as Wagons,
       MIN(trn.wid_date) as Min_WID_Hrs,
       MAX(trn.wid_date) as Max_WID_Hrs,
       MIN(td.datetime_act_comp_dump) as Min_Fin_Dump,
       MAX(td.datetime_act_comp_dump) as Max_Fin_Dump,        
       ROUND(SUM(con.weight_total-con.empty_weight_total),0) as Tot_Tonnes,       
       ROUND(AVG(con.weight_total-con.empty_weight_total),2) as Avg_Tonnes,
       ROUND(MIN(con.weight_total-con.empty_weight_total),2) as Minimum,
       ROUND(PERCENTILE_DISC(0.99) WITHIN GROUP (ORDER BY (con.weight_total-con.empty_weight_total) DESC),2) as "1st",      

from  widsys.consist con
      INNER JOIN widsys.train trn
      USING (train_record_id)
      INNER JOIN tpps.train_details td
      ON trn.train_tpps_id||trn.mine_code = td.train_id||td.mine_code
      INNER JOIN widsys.v_consist_ore_detail vcon
      USING (consist_id)

where trn.direction = 'N'
      and to_char(trn.wid_date, 'IYYY') = 2009
      and to_char(trn.wid_date-7/24, 'IW') = 25

group by to_char(trn.wid_date, 'IYYY'),
         to_char(trn.wid_date-7/24, 'IW'),
         SUBSTR(trn.train_control_id,1,2),
         vcon.product_type_code

order by to_char(trn.wid_date-7/24, 'IW') DESC

只是为了排除故障， 我不明白可能会导致这种情况对于上面的查询，我尝试删除与 vcon 相关的所有内容，并将 trn.wid_date 替换为 td.datetime-act-comp-dump。 结果是它只报告Year-Week-Mine而不是Year-Week-Mine-Product。 （参见下面的查询）

这个新查询实际上执行而不只是挂起，但返回一些奇怪的结果，并且还不够，因为它不会分解产品上的内容。

select to_char(td.datetime_act_comp_dump, 'IYYY') as dump_year,
       to_char(td.datetime_act_comp_dump-7/24, 'IW') as dump_week,
       SUBSTR(trn.train_control_id,1,2) as Mine,
       --vcon.product_type_code as Product,
       COUNT(DISTINCT trn.train_control_id) as Trains,
       COUNT(1) as Wagons,
       MIN(trn.wid_date) as Min_WID_Hrs,
       MAX(trn.wid_date) as Max_WID_Hrs,
       MIN(td.datetime_act_comp_dump) as Min_Fin_Dump,
       MAX(td.datetime_act_comp_dump) as Max_Fin_Dump,        
       ROUND(SUM(con.weight_total-con.empty_weight_total),0) as Tot_Tonnes,       
       ROUND(AVG(con.weight_total-con.empty_weight_total),2) as Avg_Tonnes,
       ROUND(MIN(con.weight_total-con.empty_weight_total),2) as Minimum,
       ROUND(PERCENTILE_DISC(0.99) WITHIN GROUP (ORDER BY (con.weight_total-con.empty_weight_total) DESC),2) as "1st"       

from  widsys.consist con
      INNER JOIN widsys.train trn
      USING (train_record_id)
      INNER JOIN tpps.train_details td
      ON trn.train_tpps_id||trn.mine_code = td.train_id||td.mine_code
      --INNER JOIN widsys.v_consist_ore_detail vcon
      --USING (consist_id)

where trn.direction = 'N'
      and to_char(td.datetime_act_comp_dump, 'IYYY') = 2009
      and to_char(td.datetime_act_comp_dump-7/24, 'IW') = 25

group by to_char(td.datetime_act_comp_dump, 'IYYY'),
         to_char(td.datetime_act_comp_dump-7/24, 'IW'),
         SUBSTR(trn.train_control_id,1,2)
         --vcon.product_type_code

order by to_char(td.datetime_act_comp_dump-7/24, 'IW') DESC

对可能出什么问题有什么建议吗？

干杯，

汤米

Answer 1

我通过为 widsys 表创建一个子查询和为 tpps 表创建一个子查询，设法让它运行得更快。 然后对两列进行隐式内部联接而不是连接。

SELECT blah FROM (widsys subquery) w, (tpps subquery) t WHERE w.mine_code = t.mine_code and w.train_id = t.train_tpps_id

Answer 2

在没有更多信息的情况下，我唯一能想到的是，train_details 的 datetime_act_comp_dump 列未建立索引，而 wid_date 已建立索引。 这听起来像是一个非常正常的性能问题，其中某些内容未建立索引，或者 train 和 train_details 表的大小显着不同，并且您的连接正在崩溃。

我不确定您正在使用哪个数据库，但您可能想弄清楚如何运行查询执行计划分析器并查看两个执行计划之间的区别。 我怀疑答案可能是结构性的，或者连接语句中的串联可能导致一些特定于数据库的问题。