Home » SQL & PL/SQL » SQL & PL/SQL » Compile view (10.2.0.2.0 ,windows)
Compile view [message #427490] Thu, 22 October 2009 22:23 Go to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member


Hi all,

When materialized view of PUBLIC (owner) is INVALID , is it good practise to compile them or recreate ?

Please advice.

Regards,
Re: Compile view [message #427491 is a reply to message #427490] Thu, 22 October 2009 23:01 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
read this

in general, a compile of a materialized view needing revalidation is automatic the next time it is referenced. There are special situations where you might want to force a revalidation manually. These are noted in the document above.

The documentation states that a "compile" is "quick" which suggests that the MVIEW data is not actually refreshed in response to a compile event, only the definition of the matarialized view is made valid and the staleness setting updated so that query rewrite can be considered again. But this is not in fact explicitly stated in the documentation, I am inferring.

One could confirm this however by simply adding data to a table in an MVIEW and then compling the mview to see if it pulls in the new data without doing an actual refresh.

Happy reading. Kevin
Re: Compile view [message #427495 is a reply to message #427491] Thu, 22 October 2009 23:13 Go to previous messageGo to next message
Hitman11
Messages: 94
Registered: October 2009
Location: norway
Member
Kevin,

Thanks for your response.

how to determine whether public synonym based on materialized view needs
to be compiled or recreated ??


Regards,

Re: Compile view [message #427496 is a reply to message #427490] Thu, 22 October 2009 23:31 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Invalid on a synonym is not really very meaningful. For example:

if a public synonym points to a table and you drop the table then the public synonym will be marked as invalid.

If you recreate the table again, the public synonym will still be marked as invalid.

If you then reference the public synonym, the synonym will be revalidated silently by oracle in the background and its status changed to valid again.


A easiest way to force a compile of a invalid public synonym is simply to issue a sqlpus describe on it. Consider this sequence of events.

create public synonym abc for xabc;

select object_name,status from dba_objects where object_name = 'ABC' and owner = 'PUBLIC';

create table xabc (a number);

select object_name,status from dba_objects where object_name = 'ABC' and owner = 'PUBLIC';

desc abc

select object_name,status from dba_objects where object_name = 'ABC' and owner = 'PUBLIC';

Of interesting note it that we created the public synonym to point to a non-existent table but it was created as valid, go figure?

Next note that creating the missing table the synonym needs actually causes the synonym to go invalid. But if you think about it, it should. A change was made to the target object so the synonym should go invalid. This gives us a little insight into the simplicity of the architecture behind invalidation.

Lastly note that the describe against the synonym will cause oracle to compile it and thus make it valid if it is indeed valid.

To see these three things happen, try the code.

So I am not sure what your question really is. But I believe I have likely answered it anyway.

Good luck, Kevin


Previous Topic: replace
Next Topic: Somebody please explain this query for me.
Goto Forum:
  


Current Time: Fri Dec 09 03:38:13 CST 2016

Total time taken to generate the page: 0.08813 seconds