Re: File full error in simple select statement
Posted by: Peter Brawley
Date: April 21, 2021 08:39PM

800 x 350k 10-byte decimal amount & date values take up about 4G, if code is an int then your temp file is big enough, something else is wrong.

Is the tmpdir file system too small, did you try other tmpdir settings?

Of course the join is problematic, difficult to index---what does Explain say, what does performance_schema (https://dev.mysql.com/doc/refman/8.0/en/performance-schema-query-profiling.html) say?

Does this version of the query also throw the temp file error?

select weekend, code, sum(amount) as owing
from (
  select w.weekend, d2.code as family, d2.amount
  from weekendings w  
  join dr02        d2 on (w.weekend >= d2.date and d2.t1 <> "g") 
                      or (w.weekend >= d2.weekend and d2.t1 = "g")
  where d2.code = "smi1" and d2.amount is not null
) x
group by weekend, code
order by weekend, code;

Options: ReplyQuote


Subject
Written By
Posted
Re: File full error in simple select statement
April 21, 2021 08:39PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.