Home » SQL & PL/SQL » SQL & PL/SQL » explain plan for mview... (Oracle 10g HP Unix)
explain plan for mview... [message #340585] Wed, 13 August 2008 06:16 Go to next message
santoshorcl
Messages: 24
Registered: August 2007
Location: pune
Junior Member

Hi All,

please tell me how to invoke EXPLAIN PLAN for CREATE MATERIALIZED VIEW.
while i am trying to get plan i am getting following error

ORA-00900: invalid SQL statement

Thanks in advance.
Re: explain plan for mview... [message #340591 is a reply to message #340585] Wed, 13 August 2008 06:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can only perform an explain plan for a valid query.

Can you post the SQL you're using.
Re: explain plan for mview... [message #340600 is a reply to message #340591] Wed, 13 August 2008 06:38 Go to previous messageGo to next message
santoshorcl
Messages: 24
Registered: August 2007
Location: pune
Junior Member

Thanks for quick reply.

but i can get explain plan for create table with same parameters.
Re: explain plan for mview... [message #340603 is a reply to message #340600] Wed, 13 August 2008 06:46 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Does EXPLAIN PLAN work for DDL?
I never actually used it, since tuning DDL-statements doesn't sound like a rewarding job. (DDL is only executed once anyway)
Re: explain plan for mview... [message #340608 is a reply to message #340603] Wed, 13 August 2008 07:18 Go to previous messageGo to next message
santoshorcl
Messages: 24
Registered: August 2007
Location: pune
Junior Member

I agree with you Frank.

I need to estimate time/resources for new solution design. Hence i requires it.

Regards,
Santy.
Re: explain plan for mview... [message #340609 is a reply to message #340608] Wed, 13 August 2008 07:20 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
And how are you going to base required time on the outcome of an EXPLAIN PLAN?
Re: explain plan for mview... [message #340619 is a reply to message #340609] Wed, 13 August 2008 07:55 Go to previous message
santoshorcl
Messages: 24
Registered: August 2007
Location: pune
Junior Member

i know it is difficult to estimate correct time.
I usually compare explain plan from test and prod as well as i use total through put which generated by test. on these basis i estimate rough timings.

Regards,
Santy.
Previous Topic: List of the months according to current date
Next Topic: Need a select query for the following scenario (merged)
Goto Forum:
  


Current Time: Sat Feb 08 12:31:22 CST 2025