The query is:

 select XWCS 
The from (select get_user_xwcs (b.i d, '2016-11-01', '2016-11-30') as XWCS 
The from t_user b 
Where b.a rea_id=201 
And b.com pany_id is not null) 

The query statement from t_detail_review extract each user in the table from November 1 to November 30 data, execute the statement, when all the data in PLSQL particularly slow, affect the efficiency, seeking improvements!
Get_user_xwcs function:

 
The CREATE OR REPLACE FUNCTION get_user_xwcs (user_id number, startDate varchar, endDate varchar) 
The RETURN number IS 
Icount number; 
The BEGIN 
Select sum (case when cs> 40 then 40 else cs=end) into icount the from (
The select userid, to_char (create_date '- dd yyyy - mm), count (distinct dyn_detail_id) cs 
The from t_detail_review 
Where userid user_id=
And (startDate is null or create_date & gt;=to_date (startDate, 'yyyy - mm - dd)) 
And (endDate is null or create_date & lt;=to_date (endDate | | '23:59:59', '- dd yyyy - mm hh24: mi: ss')) 
Group by userid, to_char (create_date '- dd yyyy - mm)); 
RETURN NVL (icount, 0); 
The END; 

CodePudding user response:

Index and structure of the two tables below:
T_user:



T_detail_review:

CodePudding user response:

Take the startDate, endDate is null record of handling the
Your current statement would lead to a full table scan

In addition, you as if there is a problem returns multiple records

CodePudding user response:

The CREATE OR REPLACE FUNCTION get_user_xwcs (user_id number, startDate varchar, endDate varchar)
The RETURN number IS
Icount number;
The BEGIN
Select sum (case when cs> 40 then 40 else cs=end) into icount the from (
The select userid, count (distinct dyn_detail_id) cs
The from t_detail_review
Where userid user_id=
And create_date & gt;=to_date (startDate, 'yyyy - mm - dd)
And create_date & lt;=to_date (endDate | | '23:59:59', '- dd yyyy - mm hh24: mi: ss')
Group by userid);
RETURN NVL (icount, 0);
The END;

CodePudding user response:

The

reference 3 floor sych888 response:

CREATE OR REPLACE FUNCTION get_user_xwcs (user_id number, startDate varchar, endDate varchar)
The RETURN number IS
Icount number;
The BEGIN
Select sum (case when cs> 40 then 40 else cs=end) into icount the from (
The select userid, count (distinct dyn_detail_id) cs
The from t_detail_review
Where userid user_id=
And create_date & gt;=to_date (startDate, 'yyyy - mm - dd)
And create_date & lt;=to_date (endDate | | '23:59:59', '- dd yyyy - mm hh24: mi: ss')
Group by userid);
RETURN NVL (icount, 0);
The END;

nothing effect, what better way?

CodePudding user response:

Don't use function, try this:
With t as
(select userid,
The sum (case
When cs & gt; 40 then=
40
The else
Cs
End) XWCS
The from (select userid, trunc (create_date), count (distinct dyn_detail_id) cs
The from t_detail_review
Where create_date between to_date (startdate, '- dd yyyy - mm) and
To_date (enddate | | '23:59:59', '- dd yyyy - mm hh24: mi: ss')
Group by userid, trunc (create_date))
Group by userid)
The select b.i d, c.x WCS
The from t_user b
Left the join t c
On b.i d=c.u serid
Where b.a rea_id=201
And b.com pany_id is not null

CodePudding user response:

1, indexed columns make sure don't have a null record, once have a null value, the index may fail, you can give a default, such as' 0 '
2, I look at this statement, if really don't have to use get_user_xwcs the function, is actually two table t_user and t_detail_review do next join, add a function transfer may be wasted efficiency, under the complicated problem

CodePudding user response:

The

reference 3 floor sych888 response:

CREATE OR REPLACE FUNCTION get_user_xwcs (user_id number, startDate varchar, endDate varchar)
The RETURN number IS
Icount number;
The BEGIN
Select sum (case when cs> 40 then 40 else cs=end) into icount the from (
The select userid, count (distinct dyn_detail_id) cs
The from t_detail_review
Where userid user_id=
And create_date & gt;=to_date (startDate, 'yyyy - mm - dd)
And create_date & lt;=to_date (endDate | | '23:59:59', '- dd yyyy - mm hh24: mi: ss')
Group by userid);
RETURN NVL (icount, 0);
The END;

CodePudding user response:

reference 4 floor yuhao9071 response:

Quote: reference sych888 reply: 3/f

CREATE OR REPLACE FUNCTION get_user_xwcs (user_id number, startDate varchar, endDate varchar)
The RETURN number IS
Icount number;
The BEGIN
Select sum (case when cs> 40 then 40 else cs=end) into icount the from (
The select userid, count (distinct dyn_detail_id) cs
The from t_detail_review
Where userid user_id=
And create_date & gt;=to_date (startDate, 'yyyy - mm - dd)
And create_date & lt; nullnullnullnullnullnull