Optimize the query of weekday statistics between two dates

Question

I have a table with two fields: start_date and end_date. Now I want to count the total number of work overtime. I have created a new calendar table to maintain the working day status of the date.

table: workdays

id                  status
2020-01-01          4
2020-01-02          1
2020-01-03          1
2020-01-04          2

4: holiday, 1: weekday, 2: weekend

I created a function to calculate the weekdays between two dates (excluding weekends, holidays).

create or replace function get_workday_count (start_date in date, end_date in date)
return number is
    day_count int;
begin
    select count(0) into day_count from WORKDAYS
    where TRUNC(ID) >= TRUNC(start_date)
    and TRUNC(ID) <= TRUNC(end_date)
    and status in (1, 3, 5);
    return day_count;
end;

When I execute the following query statement, it takes about 5 minutes to display the results, erp_sj table has about 200000 rows of data.

select count(0) from ERP_SJ GET_WORKDAY_COUNT(start_date, end_date) > 5;

The fields used in query statements are indexed.

How to optimize? Or is there a better solution?

Answer 1

First of all, optimizing your function: 1.adding pragma udf (for faster execution in sql 2. Adding deterministic clause(for caching) 3. Replacing count(0) to count(*) (to allow cbo optimize count) 4. Replacing return number to int

create or replace function get_workday_count (start_date in date, end_date in date)
return int deterministic is
    pragma udf;
   day_count int;
begin
    select count(*) into day_count from WORKDAYS w
    where w.ID >= TRUNC(start_date)
    and w.ID <= TRUNC(end_date)
    and status in (1, 3, 5);
    return day_count;
end; 

Then you don't need to call your function in case of (end_date - start_date) < required number of days. Moreover, ideally it would be to use scalar subquery instead of function:

select count(*) 
from ERP_SJ 
where 
case 
   when trunc(end_date) - trunc(start_date) > 5 
      then GET_WORKDAY_COUNT(trunc(start_date) , trunc(end_date)) 
   else 0
 end > 5

Or using subquery:

select count(*) 
from ERP_SJ e
where 
case 
   when trunc(end_date) - trunc(start_date) > 5 
      then (select count(*) from WORKDAYS w
    where w.ID >= TRUNC(e.start_date)
    and w.ID <= TRUNC(e.end_date)
    and w.status in (1, 3, 5)) 
   else 0
 end > 5