Function much slower than same statement.

Hi all:

I am facing stunning performance results with a user defined function. This is the story:
I have a function that basically performs a validation and branches into two selects. One of them (a three table select) was behaving very bad (26 secs) although the joins are PK based, and, to my surprise, another function that performs a query very similar to the bad one, but with one more 'child table' in the join performs just OK. "Let's see" I thought.
I Opened SqlPlus and executed this annoying query. Again It last 26 secs.
Then I used a hint ('ORDERED') in the select and the query executed in 0.10 secs. "OK" I thought "Next step: put the hint in the select within the function..." but the function still takes 26 secs to execute!! Same select statement, same hint but the Function seems to be 260 times slower than the single select (Is the optimizer using the hint?).
The validation issue in the function is only "if variable_val < fixed_val then SELECT... else SELECT..." so It shouldn't penalize.

Can anybody throw some light on it?

The infamous SELECT (works OK as statement -with the hint- but very bad inside the function):

   SELECT/*+ORDERED*/ NVL( SUM( ( b.N_AP_AS_A +
                                  b.N_AP_AS_M +
                                  b.N_AP_AV_A +
                                  b.N_AP_AV_M ) * c.N_CR_AP ), 0 )
           INTO v_ret
           FROM TC a, VEN_SOR_JA b, MOD_SOR_JA c
          WHERE a.ID_N_PV        = p_ID_N_PV
            AND b.ID_N_TC        = a.ID_N_TC
            AND b.ID_N_JR       >= p_jr_ini
            AND b.ID_N_JR       <= p_jr_fin
            AND c.ID_N_JGO       = b.ID_N_JGO
            AND c.ID_N_SOR       = b.ID_N_SOR
            AND c.ID_N_MOD       = b.ID_N_MOD;

The SELECT with one more table (in another user defined function) that performs OK (0.20 secs.):

         SELECT NVL ( SUM( 
                           ( 
                             ( b.N_AP_AS_A +
                               b.N_AP_AS_M +
                               b.N_AP_AV_A +
                               b.N_AP_AV_M ) * c.N_CR_AP ) 
                           * d.N_COM_VEN / 100 ) 
                     , 0 )
           INTO v_ret
           FROM TC a, VEN_SOR_JA b, MOD_SOR_JA c, JGO_TC d
          WHERE a.ID_N_PV        = p_ID_N_PV
            AND b.ID_N_TC        = a.ID_N_TC
            AND b.ID_N_JR       >= p_jornada_ini
            AND b.ID_N_JR       <= p_jornada_fin
            AND c.ID_N_JGO       = b.ID_N_JGO
            AND c.ID_N_SOR       = b.ID_N_SOR
            AND c.ID_N_MOD       = b.ID_N_MOD
            AND d.ID_N_JGO       = b.ID_N_JGO
            AND d.ID_N_TC        = a.ID_N_TC;

This is running on Oracle 8.1.6 on Win NT sp 6 (This is not my fault!!)

Thanks in advance. Received on Mon Jul 07 2003 - 04:09:19 CDT