Compile Database Objects - 2

I have explained about compiling invalid objects in my earlier post for the db objects like packages, functions, views etc.

There are some object types that has a little different approach, specific to object types.

I will start with indexes.

Indexes:-

Index are not compiled but rebuilt every-time. So syntax for rebuilding index will be

Alter index <owner>.<indexname> rebuild [online];

Online can be used if index is being being used, but the best approach is that no connection should be allow to database while rebuilding indexes.

Materialized Views:-

We can compile the MV as we do it for other objects, but another option is to refresh the MV.

For understanding this first we have to understand Materialized View, which is nothing but an image of a table with desired columns having data till a particular time (last refresh).

The MV has to be refreshed timely to get the data updated in sync with table. Like a view it doesn't just store definition but the data.

The syntax for refresh is

begin dbms_mview.refresh('<MVIEW_NAME>','C'); end; -- Complete Refresh

begin dbms_mview.refresh('<MVIEW_NAME>','F'); end; -- Fast Refresh

As appears with name the  complete refreshes refreshes completely logically truncating the current data, while fast refresh checks only for the changes from last refresh.

For fast refresh the logging should be enabled and the advantage is it saves time while for complete refresh, its time consuming and doesn't need to have logging.

There are more as below for refresh type

If nothing specified, its default

? - force refresh

P - for effects of partition changes in detail table.

Public Synonyms:- 

The syntax will be

Alter public Synonym <Synonym Name> Compile; -- Synonym