Home » SQL & PL/SQL » SQL & PL/SQL » EXPLAIN PLAN doesn't show view names
EXPLAIN PLAN doesn't show view names [message #263034] Tue, 28 August 2007 18:23 Go to next message
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 Go to previous messageGo to next message
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 #263074 is a reply to message #263034] Wed, 29 August 2007 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
A view is not a physical object, it is just a name for a query.
"select * from view" is rewritten to "select * from (view definition)" before optimization.

Regards
Michel
Re: EXPLAIN PLAN doesn't show view names [message #263251 is a reply to message #263039] Wed, 29 August 2007 09:00 Go to previous messageGo to next message
dandormont
Messages: 12
Registered: April 2006
Junior Member
Is there some tool other than EXPLAIN PLAN that will show me that information (the actual view names)?
Re: EXPLAIN PLAN doesn't show view names [message #263252 is a reply to message #263251] Wed, 29 August 2007 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
No Oracle one.
What you actually want is a tool that graphically show you the query and not the execution plan.

Regards
Michel
Re: EXPLAIN PLAN doesn't show view names [message #263266 is a reply to message #263252] Wed, 29 August 2007 09:35 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: PL/SQL to read date modified of a textfile
Next Topic: query
Goto Forum:
  


Current Time: Thu Dec 05 17:06:19 CST 2024