Re: **Help! Help! **

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Mon, 19 Apr 1999 18:48:55 +0200
Message-ID: <7ffmpd$afd$1@weber.a2000.nl>

Haruka Suko wrote
> I got another question which you may know the solution.

I guess it is always better to start a new thread, and instead of Help! Help! put your question in the subject as well. You'll sure get more response...

> want to know how the query is excecuted

Check if your Oracle user has a plan_table:

    desc plan_table

If not:

    create table plan_table
    ( statement_id varchar2(30)
, timestamp date
, remarks varchar2(80)
, operation varchar2(30)
, options varchar2(30)
, object_node varchar2(30)
, object_owner varchar2(30)
, object_name varchar2(30)
, object_instance number
, object_type varchar2(30)
, search_columns number
, id number
, parent_id number
, position number
, other long

    );

Next, make sure you delete the old information in the table...

    delete from plan_table
    where statement_id = '<some_name>';

... and have Oracle fill the table for your query using...

    explain plan
    set statment_id = '<some name>' for       select * from emp;

... and check the result:

    select lpad( ' ', 2*(level - 1)) || operation ||

            ' ' || options || ' ' || object_name || ' ' ||
            decode(id, 0, 'Cost = ' || position) "Execution Plan"

    from plan_table
    start with id = 0
    and statement_id = '<some name>'     connect by prior id = parent_id
    and statement_id = '<some_name>';

Finally, clean up the table:

    delete from plan_table
    where statement_id = '<some_name>';

Change <some name> into something meaningfull.

Arjan. Received on Mon Apr 19 1999 - 11:48:55 CDT