写了一个oracle 函数，带到sql查询里面，速度很慢，不知何故，求高人解答

Question

表结构是这样的
create table TableA
(
  SERIALNO        VARCHAR2(32) not null,
  OBJECTNO        VARCHAR2(32) not null,
  RESULT1         VARCHAR2(18),
  RESULT2         VARCHAR2(18),
  RESULT3         VARCHAR2(18),
  INPUTORGID      VARCHAR2(20),
  INPUTUSERID     VARCHAR2(20),
  INPUTDATE       VARCHAR2(10),
  FINALLYRESULT   VARCHAR2(18),
  DATE     VARCHAR2(10) not null
)
create or replace function getRealResult(pNO varchar2,pDate varchar2)
/***********************************************************************
 * Purpose:  得到近期最新结果
 * Parameter:pNO 
 *           pate 格式YYYY/MM/DD
 ***********************************************************************/
return varchar2
is
sResult varchar2(20);
begin
select result into sResult from (
Select result From (
select accountDATE,nvl(FinallyResult,nvl(Result3,nvl(Result2,nvl(Result1,'X')))) result
from TableA
where accountDATE<=pDate and objectNO=pNO
) Where result<>'X'
order by accountDATE desc
) where RowNum=1;
return sResult;
EXCEPTION
WHEN NO_DATA_FOUND THEN
return '01';
WHEN OTHERS THEN
return '01';
end;执行里面的sql时 执行计划看着是挺快的
但是带入到查询的sql语句里 查询就慢了 用这个函数 和不用这个函数 速度就完全不一样
不知道什么原因 索引也都用上了求高人解答

Answer 1

你在 order by accountDATE desc 这里有问题了，你order by的时候，是全表扫描啊！
建议换成这样的排序方式。 用分析函数来做。

Answer 2

create or replace function getRealResult(pNO varchar2, pDate varchar2)
/*********************************************************************** 
  * Purpose:  得到近期最新结果 
  * Parameter:pNO 
  *          pate 格式YYYY/MM/DD 
  ***********************************************************************/
 return varchar2 is
  sResult varchar2(20);
begin
  select result
    into sResult
    from (Select result,
                 row_number() over(order by accountDATE desc) rn--排序记录标号。
            From (select accountDATE,
                         nvl(FinallyResult,
                             nvl(Result3, nvl(Result2, nvl(Result1, 'X')))) result
                    from TableA
                   where accountDATE <= pDate
                     and objectNO = pNO)
           Where result <> 'X'
           /*order by accountDATE desc*/)
   where rn = 1;
  return sResult;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    return '01';
  WHEN OTHERS THEN
    return '01';
end;

Answer 3

1，order by order by accountDATE desc是全表排序扫描之后取前N条，一定走的是table span
        
2，row_number() over(order by accountDATE desc) 走的是索引扫描，只排出前N条，而且只取前N条记录。

Answer 4

这个未必的
ORDER BY 字段是和你索引有关的，如果排序字段本身有索引那是很快，本身没有就很慢的
分析函数并不是肯定走索引扫描的，ORDER 也不是肯定走全表的
并且 ROW_NUMBER这个分析函数并没有只排出前N条，而且只取前N条的记录这个说法

Answer 5

说说你TableA的索引字段
你这张表的数据量是多少？
你的查询语句是怎么样的？

Answer 6

难道你在你的查询语句里面用了getRealResult这个function？

Answer 7

建议你将accountDATE 的数据类型改成date型的，入库的时候做个处理，比较的时候将pDate
用to_date函数格式化成你入库的格式，试下。希望能对你有帮助

Answer 8

为什么还要先order by ,再取第一条记录，不就是最大日期吗，多此一举

Answer 9

CREATE OR REPLACE FUNCTION getRealResult
    ( pNO   VARCHAR2,
      pDate VARCHAR2)
    /***********************************************************************
    * Purpose:  得到近期最新结果
    * Parameter:pNO
    *          pate 格式YYYY/MM/DD
    ***********************************************************************/
    RETURN VARCHAR2
  IS
    w_max_accountDate VARCHAR2(64);
    sResult           VARCHAR2(20);
  BEGIN
    SELECT MAX(accountdate)
    INTO w_max_accountdate
    FROM tablea
    WHERE accountDATE <=pDate
    AND objectNO       =pNO
    AND finallyresult IS NOT NULL
    AND result3       IS NOT NULL
    AND result2       IS NOT NULL
    AND result1       IS NOT NULL;
    
    SELECT NVL(FinallyResult,NVL(Result3,NVL(Result2,NVL(Result1,'X'))))
    INTO sResult
    FROM tablea
    WHERE accountDATE  = w_max_accountdate
    AND objectNO       = pNO
    AND finallyresult IS NOT NULL
    AND result3       IS NOT NULL
    AND result2       IS NOT NULL
    AND result1       IS NOT NULL
    AND rownum         =1;
 
    RETURN sResult;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN '01';
  WHEN OTHERS THEN
    RETURN '01';
  END;
/
  

Answer 10

你的这个函数要除了要查objectNo和accountDate之外，还有加在4个Result列的NOT NULL条件，我怀疑这些列没有index。所以最好的结果就是走objectNo和accountDate上的index range scan然后做table scan by rowid，运行一次开销不会很大，但是如果查询是对很多条记录的话，效率就很低下了。Oracle无法对函数内的查询作成本分析。假如你的查询是针对很多objectNo的，比如另有一个表放所有的objectNo
create table tableb
(objectNo varchar2(32)
, col2
, col3
, ...);现在你做类似于如下的查询
select objectNo, getRealResult(objectNo, to_char(sysdate, 'yyyy/mm/dd')
  from tableb;那对于每个objectNo，oracle都必须对tableA进行大量扫描，效率当然很差。这种情况下不要用函数，直接做联合查询，那就只需要对tableA做一次扫描。