通過一個函式抽取所有資料時效率過慢，求改進方案-有解無憂

查詢陳述句是：

select xwcs

  from (select  get_user_xwcs(b.id, '2016-11-01', '2016-11-30') as xwcs

          from t_user b

         where b.area_id = 201

                 and b.company_id is not null)

這個查詢陳述句是從t_detail_review表中抽取出每個用戶從11月1號到11月30號的資料，執行該陳述句時，當在plsql中展開所有資料時特別慢，影響了效率，求改進方案！
get_user_xwcs函式：



CREATE OR REPLACE FUNCTION get_user_xwcs(user_id number,startDate varchar,endDate varchar)

  RETURN number IS

  icount number;

BEGIN

  select sum(case when cs>=40 then 40 else cs end) into icount from (

  select userid,to_char(create_date,'yyyy-mm-dd'),count(distinct dyn_detail_id) cs

        from t_detail_review

      where userid=user_id 

			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 userid,to_char(create_date,'yyyy-mm-dd'));

  RETURN nvl(icount,0);

END;

uj5u.com熱心網友回復：

兩張表的索引及結構如下：
t_user:

t_detail_review:

uj5u.com熱心網友回復：

先把startDate 、endDate 為空的記錄處理了
你目前的陳述句會導致全表掃描

另外，你的函式好像有問題存在回傳多條記錄的的情況

uj5u.com熱心網友回復：

CREATE OR REPLACE FUNCTION get_user_xwcs(user_id number,startDate varchar,endDate varchar)
  RETURN number IS
  icount number;
BEGIN
  select sum(case when cs>=40 then 40 else cs end) into icount from (
  select userid,count(distinct dyn_detail_id) cs
        from t_detail_review
      where userid=user_id
            and create_date>=to_date(startDate,'yyyy-mm-dd')
      and  create_date<=to_date(endDate||' 23:59:59','yyyy-mm-dd hh24:mi:ss')
      group by userid);
  RETURN nvl(icount,0);
END;

uj5u.com熱心網友回復：

參考 3樓sych888 的回復:

CREATE OR REPLACE FUNCTION get_user_xwcs(user_id number,startDate varchar,endDate varchar)
  RETURN number IS
  icount number;
BEGIN
  select sum(case when cs>=40 then 40 else cs end) into icount from (
  select userid,count(distinct dyn_detail_id) cs
        from t_detail_review
      where userid=user_id
            and create_date>=to_date(startDate,'yyyy-mm-dd')
      and  create_date<=to_date(endDate||' 23:59:59','yyyy-mm-dd hh24:mi:ss')
      group by userid);
  RETURN nvl(icount,0);
END;

沒什么效果呢，還有什么更好的辦法嗎？

uj5u.com熱心網友回復：

不要用函式，試試這個：
with t as
(select userid,
         sum(case
               when cs >= 40 then
                40
               else
                cs
             end) xwcs
    from (select userid, trunc(create_date), count(distinct dyn_detail_id) cs
             from t_detail_review
            where create_date between to_date(startdate, 'yyyy-mm-dd') and
                  to_date(enddate || ' 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
            group by userid, trunc(create_date))
   group by userid)
select b.id, c.xwcs
  from t_user b
  left join t c
    on b.id = c.userid
where b.area_id = 201
   and b.company_id is not null

uj5u.com熱心網友回復：

1、建索引的列務必確保不要有null的記錄,一旦有null值,索引可能失效，你可以給列一個default，比如'0'
2、我看這個陳述句，好像真不必用get_user_xwcs這個函式，其實就是兩個表t_user和t_detail_review做下join的事情，加個函式中轉下可能浪費了效率，把問題搞復雜了

uj5u.com熱心網友回復：

參考 3 樓 sych888 的回復:

CREATE OR REPLACE FUNCTION get_user_xwcs(user_id number,startDate varchar,endDate varchar)
  RETURN number IS
  icount number;
BEGIN
  select sum(case when cs>=40 then 40 else cs end) into icount from (
  select userid,count(distinct dyn_detail_id) cs
        from t_detail_review
      where userid=user_id
            and create_date>=to_date(startDate,'yyyy-mm-dd')
      and  create_date<=to_date(endDate||' 23:59:59','yyyy-mm-dd hh24:mi:ss')
      group by userid);
  RETURN nvl(icount,0);
END;

uj5u.com熱心網友回復：

參考 4 樓 yuhao9071 的回復:

Quote: 參考 3樓sych888 的回復:
CREATE OR REPLACE FUNCTION get_user_xwcs(user_id number,startDate varchar,endDate varchar)
  RETURN number IS
  icount number;
BEGIN
  select sum(case when cs>=40 then 40 else cs end) into icount from (
  select userid,count(distinct dyn_detail_id) cs
        from t_detail_review
      where userid=user_id
            and create_date>=to_date(startDate,'yyyy-mm-dd')
      and  create_date<=to_date(endDate||' 23:59:59','yyyy-mm-dd hh24:mi:ss')
      group by userid);
  RETURN nvl(icount,0);
END;
沒什么效果呢，還有什么更好的辦法嗎？

你十一月份有多少記錄？全表多少記錄
正常情況應該會走索引

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/107626.html

標籤：基礎和管理

上一篇：存盤程序中如何取游標中的資料插入到表中

下一篇：關于單位工資統計的復合查詢不會寫，求各路大神支援，指點迷津