Re: File full error in simple select statement
Posted by: Peter Brawley
Date: May 11, 2021 10:09PM

Thanks, the first Explain result, formatted for human readability ...

id select_type table type   possible_keys	key	    key_len ref	rows    filtered Extra
----------------------------------------------------------------------------- ----------------------------------------
1  SIMPLE      w     index  WeekEnd	        WeekEnd     3	        791    100.00    Using index; 
                                                                                         Using temporary
 
1  SIMPLE      d2    AL     Dates,t1Index                	        111505  27.78    Using where; 
                                	                                                 Using join buffer (hash join)

... shows that the query engine can't find a d2 index. That's not why you have a tmp file problem, but it's why the query without the `code` restriction exposes the tmp file problem.

I mistakenly included the `code` condition in the 2nd query, what's the result of ...

EXPLAIN select weekend, family, 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.amount is not null
) x
group by weekend, family
order by weekend, family;

Options: ReplyQuote


Subject
Written By
Posted
Re: File full error in simple select statement
May 11, 2021 10:09PM


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.