select get_user_spcs(b.id, '2016-11-01', '2016-11-30') spcs from
t_user b
where b.area_id = 201
and company_id is not null這個函式查詢效率很不好
get_user_spcs函式內容為：
CREATE OR REPLACE FUNCTION get_user_spcs(userId number,startDate varchar,endDate varchar)
RETURN number IS
icount number;
BEGIN
select sum(case when cs>=20 then 20 else cs end) into icount from (
select user_id,to_char(create_date,'yyyy-mm-dd'),count(distinct video_id) cs
from t_video_review
where user_id=userId
and (startDate is null or create_date>=to_date(startDate,'yyyy-mm-dd'))
and (endDate is null or create_date<=to_date(endDate||' 23:59:59','yyyy-mm-dd hh24:mi:ss'))
group by user_id,to_char(create_date,'yyyy-mm-dd'));
RETURN nvl(icount,0);
END;
函式大意是統計從11月1號到31號的每個用戶對視頻的評論次數，
如果要改用left outer join 的方式，該怎么改？
像這個陳述句就用了這個方式select * from t_user a left ourer join (select count(1) as dz1,userid
from t_praise
where createtime >=to_date('2016-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')
and createtime <=to_date('2016-11-30 23:59:59','yyyy-mm-dd hh24:mi:ss')
group by userid) b on a.id=b.userid
where b.area_id = 201 and nvl(company_id,' ')<>' '
uj5u.com熱心網友回復：
求大神指導！！uj5u.com熱心網友回復：
or 換成 union 試試uj5u.com熱心網友回復：
createtime 上有索引嗎？然后再觀察一下，表連接走的是NL,還是HASH JOIN
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/109200.html
標籤：基礎和管理
上一篇：oracle