Slow Query in Pipelined Function

From: Ricky Chow <ricky_at_33tech.com>
Date: 22 Jul 2004 23:48:58 -0700
Message-ID: <781050f.0407222248.746e2931@posting.google.com>

I have written a pipelined function "teamname_split",

when i try to do a select "TEAM_ID","NAME" from table(teamname_split) only need 93 msecs

however when i do the following

select "TEAM_ID","NAME" from table(teamname_split) where name = "everton"

then it need 3 sec, what is the problem ?

why when i added a filter, the result will be so slow?

the following is the src and the explain plan

teamname_split:

CREATE OR REPLACE FUNCTION teamname_split

   RETURN teamnametypeset PIPELINED IS

   l_idx                   PLS_INTEGER;
   l_list                  VARCHAR2 (32767);
   l_del                   VARCHAR2 (1)                      := ' ';
   out_team_name_segment   teamnametype                      :=
teamnametype (NULL, NULL);
   l_team_id               v_teams_dictionary.team_id%TYPE;

BEGIN
   FOR rec IN (SELECT team_id, NAME

                 FROM v_teams_dictionary) LOOP
      l_list := rec.NAME;
      l_team_id := rec.team_id;

      LOOP
         out_team_name_segment.team_id := l_team_id;
         out_team_name_segment.NAME := TRIM(l_list);

         IF (    count_in_stopword (out_team_name_segment.NAME) = 0
             AND INSTR (out_team_name_segment.NAME, '(') = 0
             AND INSTR (out_team_name_segment.NAME, ')') = 0
            ) THEN
            PIPE ROW (out_team_name_segment);
         END IF;

         l_idx := INSTR (l_list, l_del);

         IF l_idx > 0 THEN
            out_team_name_segment.team_id := l_team_id;
            out_team_name_segment.NAME := TRIM(SUBSTR (l_list, 1,

l_idx - 1));

            IF (    count_in_stopword (out_team_name_segment.NAME) = 0
                AND INSTR (out_team_name_segment.NAME, '(') = 0
                AND INSTR (out_team_name_segment.NAME, ')') = 0


               ) THEN

               PIPE ROW (out_team_name_segment);
            END IF;

            l_list := SUBSTR (l_list, l_idx + LENGTH (l_del));
         ELSE
            out_team_name_segment.team_id := l_team_id;
            out_team_name_segment.NAME := TRIM(l_list);

            IF (    count_in_stopword (out_team_name_segment.NAME) = 0
                AND INSTR (out_team_name_segment.NAME, '(') = 0
                AND INSTR (out_team_name_segment.NAME, ')') = 0


               ) THEN

               PIPE ROW (out_team_name_segment);
            END IF;

            EXIT;
         END IF;
      END LOOP;

   END LOOP;    RETURN;
END teamname_split;
/

And the following is the explain plan of the query

Statistics

---

7567 recursive calls
0 db block gets
42751 consistent gets
34 physical reads
0 redo size
960 bytes sent via SQL*Net to client
879 bytes received via SQL*Net from client 4 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)

1 SELECT STATEMENT Optimizer Mode=CHOOSE(Cost=, Card=, Bytes= ) 2 COLLECTION ITERATOR PICKLER FETCH OF '.TEAMNAME_SPLIT' EXPLAIN PLAIN select * from v_teams_dictionary

Statistics

---

14 recursive calls
0 db block gets
30 consistent gets
0 physical reads
0 redo size
40975 bytes sent via SQL*Net to client
8406 bytes received via SQL*Net from client 62 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)

SELECT STATEMENT Optimizer Mode=CHOOSE
  VIEW GSMS_APES.V_TEAMS_DICTIONARY
    SORT UNIQUE

      UNION-ALL
        TABLE ACCESS FULL     GSMS_APES.T_TEAMS_DICTIONARY
        TABLE ACCESS FULL     GSMS_APES.T_TEAMS

Received on Fri Jul 23 2004 - 01:48:58 CDT