Using the following schema:
CREATE TABLE TKRawData
(
EmpNo text collate nocase not null,
CustNo integer not null,
JobNo integer not null,
RawYear integer not null,
RawMonth integer not null,
RawDays real not null,
primary key (EmpNo, JobNo, CustNo, RawYear, RawMonth)
);
CREATE TABLE TKClassRates
(
RateClass integer not null,
Year integer not null,
Month integer not null,
Rate real not null,
primary key (RateClass, Year, Month)
);
CREATE TABLE TKEmpRateClass
(
EmpNo text not null,
Year integer not null,
Month integer not null,
RateClass integer not null,
primary key (EmpNo, Year, Month)
);
CREATE INDEX TKClassRatesCover on TKClassRates (RateClass, Year desc, Month
desc, Rate);
CREATE INDEX TKEmpRateClassCover on TKEmpRateClass (EmpNo, Year desc, Month
desc, RateClass);
CREATE VIEW TKRawDataRate
as
select *,
RawDays*RawRate as RawCharge
from ( select *,
coalesce(( select Rate
from TKClassRates
where RateClass = RawRateClass
and Year*12+Month-1 <= RawYear*12+RawMonth-1
order by Year desc, Month desc limit 1), 1) as
RawRate
from ( select *,
coalesce(( select RateClass
from TKEmpRateClass
where EmpNo = TKRawData.EmpNo
and Year*12+Month-1 <=
RawYear*12+RawMonth-1
order by Year desc, Month desc limit 1),
1) as RawRateClass
from TKRawData
) as T1
) as T2;
Doing the following:
SELECT * FROM TKRawDataRate;
results in the following plan:
sqlite> explain query plan select * from tkrawdatarate;
0|0|0|SCAN TABLE TKRawData
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE TKEmpRateClass USING COVERING INDEX TKEmpRateClassCover
(EmpNo=?)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SEARCH TABLE TKClassRates USING COVERING INDEX TKClassRatesCover
(RateClass=?)
2|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3
3|0|0|SEARCH TABLE TKEmpRateClass USING COVERING INDEX TKEmpRateClassCover
(EmpNo=?)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 4
4|0|0|SEARCH TABLE TKClassRates USING COVERING INDEX TKClassRatesCover
(RateClass=?)
4|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 5
5|0|0|SEARCH TABLE TKEmpRateClass USING COVERING INDEX TKEmpRateClassCover
(EmpNo=?)
sqlite>
Results are correct and the plan is the same with or without analyze (there is
really only one way to answer the query). The query planner seems to be
substituting the correlated subqueries each time the intermediate is referenced
rather than executing the subquery once and passing the result up/along.
create view TKRawDataTest
as
select *,
RawDays * coalesce(( select Rate
from TKClassRates
where RateClass = coalesce(( select RateClass
from
TKEmpRateClass
where EmpNo =
TKRawData.EmpNo
and
Year*12+Month-1 <= RawYear*12+RawMonth-1
order by Year desc,
Month desc limit 1), 1)
and Year*12+Month-1 <= RawYear*12+RawMonth-1
order by Year desc, Month desc limit 1), 1) as
RawCharge
from TKRawData;
however, does generate a plan with only one execution of each correlated
subquery, but does not give me access to the intermediate results (it also
generates correct results)
SELECT * FROM TKRawDataTest;
sqlite> explain query plan select * from tkrawdatatest;
0|0|0|SCAN TABLE TKRawData
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE TKClassRates USING COVERING INDEX TKClassRatesCover
(RateClass=?)
1|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SEARCH TABLE TKEmpRateClass USING COVERING INDEX TKEmpRateClassCover
(EmpNo=?)
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
