当我使用用户定义的函数并在 oracle 选择中按顺序排序时，查询需要很长时间

Question

我有一个函数，它将从表中获取三个日期中的最大一个。

create or replace FUNCTION fn_max_date_val(
    pi_user_id IN number)
  RETURN DATE
IS
  l_modified_dt      DATE;
  l_mod1_dt  DATE;
  l_mod2_dt DATE;
  ret_user_id        DATE;
BEGIN
  SELECT MAX(last_modified_dt)
  INTO l_modified_dt
  FROM table1
  WHERE id = pi_user_id;
  -- this table contains a million records
  SELECT nvl(MAX(last_modified_ts),sysdate-90)
  INTO l_mod1_dt
  FROM table2
  WHERE table2_id=pi_user_id;
  -- this table contains clob data, 800 000 records, the table 3 does not have user_id and has to fetched from table 2, as shown below
  SELECT nvl(MAX(last_modified_dt),sysdate-90)
  INTO l_mod2_dt
  FROM table3
  WHERE table2_id IN
    (SELECT id FROM table2 WHERE table2_id=pi_user_id
    );
  execute immediate 'select greatest('''||l_modified_dt||''','''||l_mod1_dt||''','''||l_mod2_dt||''') from dual' into ret_user_id;
  RETURN ret_user_id;
EXCEPTION
WHEN OTHERS THEN
   return SYSDATE;
END;

这个函数工作得很好，并在一秒钟内执行。

-- random user_id , just to test the functionality
SELECT fn_max_date_val(100) as max_date FROM DUAL 
MAX_DATE
--------
27-02-14 

出于参考目的，我将表名称用作表1，表2和表3，但我的业务案例与下面所述的类似。

我需要获取 table1 的详细信息以及三个表中最高的修改日期。

我做了这样的事情。

SELECT a.id,a.name,a.value,fn_max_date_val(id) as max_date 
FROM table1 a where status_id ='Active';  

上面的查询执行得很好，并得到了毫秒的结果。但是当我尝试使用顺序时，问题就来了。

SELECT a.id,a.name,a.value,a.status_id,last_modified_dt,fn_max_date_val(id) as max_date 
FROM table1 where status_id ='Active' a 
order by status_id desc,last_modified_dt desc ;  
-- It took almost 300 seconds to complete

我尝试使用索引也使用status_id和last_modified的所有值，但没有运气。这能以正确的方式完成吗？

Answer 1

如果你的查询是这样的呢？

select a.*, fn_max_date_val(id) as max_date 
  from
    (SELECT a.id,a.name,a.value,a.status_id,last_modified_dt
       FROM table1 where status_id ='Active' a 
      order by status_id desc,last_modified_dt desc) a;

Answer 2

如果您不使用该功能并执行以下操作怎么办：

SELECT a.id,a.name,a.value,a.status_id,last_modified_dt x.max_date
FROM table1 a
(
  select max(max_date) as max_date
  from (
    SELECT MAX(last_modified_dt) as max_date
    FROM table1 t1
    WHERE t1.id = a.id
    union
    SELECT nvl(MAX(last_modified_ts),sysdate-90) as max_date
    FROM table2 t2
    WHERE t2.table2_id=a.id
     ...
  ) y
) x
where a.status_id ='Active'
order by status_id desc,last_modified_dt desc;  

语法可能包含错误，但类似这样的内容 + 派生表中的第三个表也是如此。