SQL query hangs on join

Question

Hey folks,

I've written an SQL query that produces a report of some stats for each Year-Week-Mine-Product.

It works exactly as desired except for one thing - trn.wid-date isn't the correct date to be using.

I should be using td.datetime-act-comp-dump. When I replace trn.wid-date with td.datetime-act-comp-dump, it doesn't give me any errors but seems to just hang indefinitely. I let it go for a while yesterday and it came back with ORA-01652 unable to extend temp segment by 128 in tablespace TEMP, though I haven't seen that error since.

I don't understand what might be causing that considering that I'm able to successfully return MAX(td.datetime-act-comp-dump) in the query below

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

Just in order to troubleshoot, from the query above, I've tried removing everything to do with vcon and replacing trn.wid_date with td.datetime-act-comp-dump. The effect is that it only reports on Year-Week-Mine rather than Year-Week-Mine-Product. (see query below)

This new query actually executes rather than just hanging, but returns a few odd results and doesn't isn't sufficient since it doesn't break things down on 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

Any advice on what might be going wrong?

Cheers,

Tommy

Answer 1

The only thing that I can think of without more information is that the datetime_act_comp_dump column of train_details isn't indexed and wid_date is. This sounds like a pretty normal performance issue where something is not indexed or the train and train_details tables are dramatically different sizes and your join is blowing up.

I'm not sure which DB you are using, but you might want to figure out how to run the query execution plan profiler and see what the difference between the two execution plans are. I suspect that the answer is going to be something structural or maybe that the concatenation in the join statement is causing some DB-specific problems.

Answer 2

I managed to get it to run muuuuuuuch faster by creating a subquery for widsys tables and one for tpps tables. Then doing an implicit inner join on two columns instead of concatenating.

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