Re: Plan_Table problem

My plan_table query's where clause looks a little different:

          connect by prior id          = parent_id
          and            statement_id = 'user'
          start with    id                 = 0
          and            statement_id = 'user';

I have used " explain plan set statement_id = 'user' for" the_sql, and always execute a delete from plan_table where statement_id = 'user' in the skeleton script I pass out. We use one shared plan_table for everyone. The duplicate line above "and statement_id = 'user' " is not an error. Both statements are required or the data is duplicated on output. I hope this helps. You may want to consider adding the cost column, new with 7.3, to your select. I find it useful.

Kevin Bass <akil1_at_mindspring.com> wrote in article <6fumla$tlr_at_camel12.mindspring.com>...
> I am attempting to develop a script but encountered problems testing the
> results of my sql statement. I am getting strange results using CONNECT
 BY
> PRIOR command. Assistance is needed!!
>
> SELECT LPAD(' ',LEVEL-1)||OPERATION||' '||OPTIONS||' '||OBJECT_NAME
> Query_plan
> FROM PLAN_TABLE
> CONNECT BY PRIOR ID = PARENT_ID
> START WITH parent_id IS NULL
> ORDER BY ID;
>
>
> Kevin
>
>
>
Received on Thu Apr 02 1998 - 00:00:00 CST