Re: File full error in simple select statement
Posted by: Derek McKinnon
Date: May 11, 2021 09:35PM

1) 8% does sound reasonable. Yes.
3) (First). Only 200 rows have an amount of null.
2)

EXPLAIN select
w.weekend,
d2.code as family,
sum(ifnull(d2.amount, 0)) as owing
from weekendings w
join dr02 d2 on (w.weekend >= d2.date and d2.t1 <> "g")
or (w.weekend >= d2.weekend and d2.t1 = "g")
group by w.weekend, d2.code;


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


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.code = "smi1" and d2.amount is not null
) x
group by weekend, family
order by weekend, family;


id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE d2 \N ref CODE,DR02,Dates,t1Index CODE 24 const 542 45.00 Using where; Using temporary; Using filesort
1 SIMPLE w \N ALL WeekEnd \N \N \N 791 55.55 Range checked for each record (index map: 0x2)

Options: ReplyQuote


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


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.