oracle - 一种在枚举数据之前减少结果集的方法

Question

我有一个包含几百万行数据的 ORACLE 表。其中一个属性是 DATE 类型。我需要在函数中使用该 DATE 属性针对该表进行选择。该函数告诉我哪些行符合我的条件。问题是当我运行这个查询时，它必须通过函数传递表中的每一行(显然)以确定哪些行匹配。这一点都不好。我正试图找到一个好的解决方案来使这个过程执行得更快。

以下是我打算尝试的几个想法:

1. 创建一个包含数据子集的 View ，然后将这些行传递到功能。
2. 将数据的子集转储到新的单独表中，然后将这些行传递到函数中。
3. 使用数据子集创建实体化 View ，然后将这些行传递给函数。

我还应该提一下，我可以添加到 WHERE 子句中以减少结果的内容不多，只有这个 DATE 和函数的使用。

任何关于这些的意见或其他人已经成功使用的东西都会很棒。如果可能，SQL 解决方案将是我的首选。

编辑函数:

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;

函数是这样调用的:

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

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;

解决方案:

我在这里输入解决方案是因为其他人没有给出确切的解决方案，但是@JustinCave 给出了正确的概念。归结为使函数具有确定性。所以我只是将现有函数包装在一个新的、确定性的函数中。然后我在必要的表上为这个函数创建了一个索引。从 22 分钟开始，它现在运行不到一秒。此外，我确实使用了@Sebas 公式来减少结果集。

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

函数是确定性的吗？如果是这样，它是否被标记为确定性的？它可以是表上基于函数的索引的一部分吗？

如果您可以识别您可以使用的数据子集而不是查询整个表，这意味着您可以在查询中应用一些额外的谓词。无论您应用什么条件来生成 View /物化 View /单独的表，似乎都适合作为谓词添加到您的查询中。