Re: Slow Query in Pipelined Function

Ricky Chow wrote:

> 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
And I presume the optimizer uses the indexes when you don't make the change.

If that is the case ... then why isn't it using the indexes?

Daniel Morgan Received on Fri Jul 23 2004 - 02:16:01 CDT