| EXPLAIN PLAN doesn't show view names [message #263034] |
Tue, 28 August 2007 18:23  |
dandormont
Messages: 12
Registered: April 2006
|
Junior Member |
|
|
In my database, I am creating views that certain users can access. When I perform an EXPLAIN PLAN on queries that touch these views, the resulting plan shows me the underlying tables that these views touch but not the views themselves. I am looking for a way to find out the actual objects, be they tables or views, that the query itself touches. Can this be done with EXPLAIN PLAN or some other tool?
Minimal example for illustration:
create table testing_table_1 (col1 integer primary key);
create view testing_view_1 as select col1 from testing_table_1;
explain plan set statement_id = 'test1' into plan_table for select * from testing_view_1;
select * from plan_table where statement_id = 'test1';
This shows me the object being accessed as TESTING_TABLE_1. I would like it to show me TESTING_VIEW_1. Thanks.
|
|
|
|
| Re: EXPLAIN PLAN doesn't show view names [message #263039 is a reply to message #263034] |
Tue, 28 August 2007 22:03  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
It usually won't show the view name. It will always show the physical object from which the data was retrieved - either an index or table. If the view is complex, and used as part of a greater join or sub-query, then the plan MAY show its name under a VIEW step in the plan. You can't force this though.
Ross Leishman
|
|
|
|
|
|
|
|
|
|
| Re: EXPLAIN PLAN doesn't show view names [message #263266 is a reply to message #263252] |
Wed, 29 August 2007 09:35 |
dandormont
Messages: 12
Registered: April 2006
|
Junior Member |
|
|
|
I don't want it graphically, but I do want it programmatically. That is, I need to take an unknown query as input and find out the name of the tables or views it hits. There are open source modules out there that can parse SQL, for example SQL::Statement in Perl, but they don't support the full Oracle SQL syntax, so I figured maybe there was a way to get the information from the database itself. If EXPLAIN PLAN isn't the right way, is there another?
|
|
|
|
| Re: EXPLAIN PLAN doesn't show view names [message #263288 is a reply to message #263266] |
Wed, 29 August 2007 10:53 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
This is what I wanted to mean, you don't want an execution plan you want a syntax representation of your query.
There is no Oracle tools, no way to get it from database.
Maybe you can contact the authors to enhance their code with full Oracle syntax (although they say they do not intend to support full ANSI syntax, so Oracle one...).
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
