A way to reduce a result set before its data is enumerated through

Question

I have an ORACLE table with a few million rows of data. One of the attributes is of type DATE. I need select against that table using that DATE attribute in a function. The function tells me what rows match my criteria. The problem is when I run this query it has to pass every row in the table through the function (obviously) to determine what rows match. This is not in the least bit performing well. I am trying to find a good solution to make this process perform much faster.

Here are a couple ideas I was going to try:

1. Create a view with a subset of the data, then pass those rows into the function.
2. Dumping a subset of the data into a new separate table, then pass those rows into the function.
3. Create a materialized view with a subset of the data, then pass those rows into the function.

I should also mention, there is not much I can add to the WHERE clause to reduce the result, just this DATE and the use of the function.

Any opinions on these or something someone else has used with success would be great. A SQL solution would be my first choice if possible.

EDIT The function:

FUNCTION add_business_days (in_date IN DATE, in_number_of_days IN NUMBER,in_skip_fridays IN number DEFAULT 0,in_skip_bank_holidays IN NUMBER DEFAULT 0)
  RETURN DATE
  IS
    v_return_date DATE := in_date;
  BEGIN
    FOR i IN 1..in_number_of_days
    LOOP
      v_return_date := next_business_day(v_return_date,in_skip_fridays,in_skip_bank_holidays);
    END LOOP;
    RETURN v_return_date;
  END;

The function is called like this:

SELECT * 
  FROM tableA 
 WHERE tableA.begin_dt < TRUNC(SYSDATE)
   AND CUBS_DATE_PKG.add_business_days(file_dt,15) = TRUNC(SYSDATE)

The function next_business_day

FUNCTION NEXT_BUSINESS_DAY (in_date DATE) 
RETURN DATE IS
    v_next_day DATE;
    --set up the holidays
    c_new_years_day  CONSTANT DATE := holiday_observed(TRUNC(in_date,'YYYY'));
    c_next_new_year  CONSTANT DATE := holiday_observed(TRUNC(ADD_MONTHS(in_date,12),'YYYY'));
    c_mlk_day        CONSTANT DATE := first_weekday(TRUNC(in_date,'YYYY'),'MONDAY') + 14;
    c_presidents_day CONSTANT DATE := first_weekday(ADD_MONTHS(TRUNC(in_date,'YYYY'),1),'MONDAY')+14;
    c_memorial_day   CONSTANT DATE := first_weekday(ADD_MONTHS(TRUNC(in_date,'YYYY'),5),'MONDAY')-7;
    c_july_4         CONSTANT DATE := holiday_observed(TO_DATE('04-JUL-'||TO_CHAR(in_date,'YYYY'),'DD-MON-YYYY'));
    c_pioneer_day    CONSTANT DATE := holiday_observed(TO_DATE('24-JUL-'||TO_CHAR(in_date,'YYYY'),'DD-MON-YYYY'));
    c_labor_day      CONSTANT DATE := first_weekday(ADD_MONTHS(TRUNC(in_date,'YYYY'),8),'Monday');
    c_veterans_day   CONSTANT DATE := holiday_observed(TO_DATE('11-NOV-'||TO_CHAR(in_date,'YYYY'),'DD-MON-YYYY'));
    c_thanksgiving   CONSTANT DATE := first_weekday(ADD_MONTHS(TRUNC(in_date,'YYYY'),10),'THURSDAY')+21;
    c_christmas      CONSTANT DATE := holiday_observed(TO_DATE('25-DEC-'||TO_CHAR(in_date,'YYYY'),'DD-MON-YYYY'));

  BEGIN
    IF LTRIM(RTRIM(TO_CHAR(in_date,'DAY'))) IN ('FRIDAY','SATURDAY','SUNDAY')
    THEN
      v_next_day := NEXT_DAY(in_date,'MONDAY');
    ELSE
      v_next_day := in_date + 1;
    END IF;

    v_next_day := TRUNC(v_next_day);
    --now, we have to check to see if v_next_day falls on a holiday
    IF v_next_day IN (c_new_years_day, c_next_new_year, c_mlk_day, c_presidents_day,
                      c_memorial_day,c_july_4, c_pioneer_day, c_labor_day,
                      c_veterans_day,c_thanksgiving, c_christmas)
    THEN
      v_next_day := next_business_day(v_next_day);
    END IF;
    RETURN TRUNC(v_next_day);
  END next_business_day;

SOLUTION:

I am typing the solution here because there was not an exact solution given by others, however, @JustinCave gave the proper concept. It came down to getting the function to be deterministic. So I just wrapped the existing function in a new, deterministic, function. I then created an index for this function on the necessary table. It runs in under a second now, from 22 minutes. Additionally, I did use @Sebas formula to reduce the resultset.

CREATE OR REPLACE FUNCTION deter_add_business_days (p_date DATE,p_days NUMBER)
   RETURN DATE
   DETERMINISTIC
IS
BEGIN
   RETURN cubs_owner.cubs_date_pkg.add_business_days (p_date, p_days);
END;

Answer 1

Is the function deterministic? If so, is it marked as deterministic? Can it be part of a function-based index on the table?

If you can identify a subset of the data that you can use rather than querying the entire table, that implies that there are some additional predicates that you could apply in your query. Whatever conditions you apply to generate the view/ materialized view/ separate table would seemingly be appropriate to add as predicates to your query.

Answer 2

function next_business_day:

• IF LTRIM(RTRIM(TO_CHAR(in_date,'DAY'))) IN ('FRIDAY','SATURDAY','SUNDAY') => skip LTRIM/RTRIM? Since you are dealing with preformated days by oracle you probably don't need to remove spaces

• RETURN TRUNC(v_next_day); => the TRUNC is not necessary, you just did it a few line above

ok, that's a very little things but multiplied per millions...

For the query, I suggest came up with this little hack: since you only have 11 holidays, you can be sure that the returned rows must have their file_dt inferior or equal to TRUNC(SYSDATE) + 7*(15+11)/5 days. The pl/sql block could then look like this:

DECLARE
    TYPE T_IDS IS TABLE OF tableA.id%TYPE;
    arrDays T_IDS;
    iDays NUMBER := 15;
BEGIN
    --reduce the amount of rows the gross way:
    SELECT tableA.id BULK COLLECT INTO arrDays 
      FROM tableA 
     WHERE tableA.begin_dt < TRUNC(SYSDATE)
       AND tableA.file_dt <= (TRUNC(SYSDATE) + FLOOR(7*(iDays+11)/5)));

    --use the reduced recordset against the businessdays validation to retrieve
    --correct rows:
    --here you ahve to store/process the results the way you want
    SELECT t2.* 
      FROM TABLE (CAST(arrDays) AS T_IDS) t1
        INNER JOIN tableA t2 ON t1.column_value = t2.id
     WHERE CUBS_DATE_PKG.add_business_days(t2.file_dt, iDays) = TRUNC(SYSDATE);
END;

I didn't test it at all, apologize the possible bugs. Cheers