Refreshing Materialized view's

ALTER MATERIALIZED VIEW mview_name COMPILE;

exec DBMS_MVIEW.REFRESH('owner.mview_name','C');


SELECT * FROM DBA_MVIEW_REFRESH_TIMES WHERE 1=1 AND NAME ='HOLX_ACTIVE_INSTL_CONTRACTS_MV';
select * from DBA_SNAPSHOT_REFRESH_TIMES where NAME ='HOLX_ACTIVE_INSTL_CONTRACTS_MV';

SELECT * FROM DBA_MVIEWS WHERE MVIEW_NAME ='HOLX_ACTIVE_INSTL_CONTRACTS_MV';;
select * from All_MVIEWS where mview_NAME ='HOLX_ACTIVE_INSTL_CONTRACTS_MV';;

1)

DBMS_REFRESH.REFRESH ('holx.holx_oic_calculated_detail_mv');

ORA-23404: refresh group "HOLX"."HOLX_OIC_CALCULATED_DETAIL_MV" does not exist

DBMS_REFRESH.REFRESH is used for a refresh group containing a list of mviews.

Please refer "http://docs.oracle.com/cd/E11882_01/server.112/e10707/rarrefreshpac.htm#i94176" for details.

Now check from DBA_REFRESH if its a refresh group or a single mview.

a) select ROWNER,RNAME from DBA_REFRESH where ROWNER='HOLX' and RNAME='HOLX_OIC_CALCULATED_DETAIL_MV';

> If it returns a result, then check step (b).

b) select OWNER,NAME,REFGROUP from sys.rgroup$ where OWNER='HOLX' and NAME='HOLX_OIC_CALCULATED_DETAIL_MV';

If step (a) does Not return a result, then 

c) select * from dba_mviews where owner='HOLX' and MVIEW_NAME='HOLX_OIC_CALCULATED_DETAIL_MV';

( Please send me the step (c) output in a xl format for better reading. )

It should be a single mview.Then it is recommended to use DBMS_MVIEW.REFRESH instead of DBMS_REFRESH.REFRESH procedure while refreshing a single mview.

2) 

ORA-12004: REFRESH FAST cannot be used for materialized view "HOLX"."HOLX_OIC_CALCULATED_DETAIL_MV"

The result from previous step (c) would clarify this.

> Check for the "FAST_REFRESHABLE" column value for this mview.

> Also ensure you have a MLOG$ in place for this mview with below query.

A MLOG$ is necessary condition for performing FAST refresh on a mview.

COLUMN LOG_TABLE HEADING 'Mview|Log Table' FORMAT A20

COLUMN LOG_OWNER HEADING 'Mview|Log Owner' FORMAT A10

COLUMN MASTER HEADING 'Master' FORMAT A20

COLUMN MVIEW_ID HEADING 'Mview|ID' FORMAT 9999

COLUMN NAME HEADING 'Mview Name' FORMAT A20

SELECT L.LOG_TABLE, L.LOG_OWNER, B.MASTER, B.MVIEW_ID, R.NAME ,R.UPDATABLE,R.REFRESH_METHOD

FROM DBA_MVIEW_LOGS L,DBA_BASE_TABLE_MVIEWS B,DBA_REGISTERED_MVIEWS R

WHERE B.MVIEW_ID = R.MVIEW_ID

AND B.OWNER = L.LOG_OWNER

AND B.MASTER = L.MASTER

AND R.NAME='HOLX_OIC_CALCULATED_DETAIL_MV';

> Now if you do not have a MLOG$ table from above query, then please create one as below and retry the FAST refresh.

CREATE MATERIALIZED VIEW LOG ON owner_name.master_table_name; ---- Simpleset way to create one.

Refer: http://docs.oracle.com/cd/E11882_01/server.112/e10707/rarmanmv.htm#REPMA389

3)

DBMS_SNAPSHOT.REFRESH( 'HOLX.HOLX_OIC_CALCULATED_DETAIL_MV','C')

This command is similar to using DBMS_MVIEW.REFRESH.

However, the option 'C' here forces a manual complete refresh on the mview for which you do not need a MLOG$ table on the master.

Hence it is going through successfully.

see the below examples  ..

create table TEST_TABLE_JK 

( rnum1 number,

rname1 varchar2(100));

Insert into TEST_TABLE_JK values (1,'A');

Insert into TEST_TABLE_JK values (2,'B');

commit;

Create view TEST_TABLE_JK_V as select * from TEST_TABLE_JK;

Create Materialized view TEST_TABLE_JK_MV as select * from TEST_TABLE_JK_V;

select * from TEST_TABLE_JK; -- 2records

select * from TEST_TABLE_JK_V; -- 2 records

select * from TEST_TABLE_JK_MV;  -- 2 records

-- you will get 2 records in 3 select statements

Insert into TEST_TABLE_JK values (3,'C');

commit;

select * from TEST_TABLE_JK; -- 3records

select * from TEST_TABLE_JK_V; -- 3 records

select * from TEST_TABLE_JK_MV;  -- 2 records

--you will get only two record s from MView where as three from table and View

--Because Mview will act as a snapshot and it shows onlt the records  that exists when time of creation or Last Refresh.

begin 

DBMS_MVIEW.REFRESH   ('apps.TEST_TABLE_JK_MV'); 

end;

/

-- we can refersh the MView using above statements

select * from TEST_TABLE_JK; -- 3records

select * from TEST_TABLE_JK_V; -- 3 records

select * from TEST_TABLE_JK_MV;  -- 3 records

-- Now after Refershing we will 3 records in each

delete TEST_TABLE_JK where RNAME1='B';

commit;

select * from TEST_TABLE_JK; -- 2records

select * from TEST_TABLE_JK_V; -- 2 records

select * from TEST_TABLE_JK_MV;  -- 3 records

--you will get 3 records from MView where as only two from table and View

--Because Mview will act as a snapshot and it shows only the records  that exists when time of creation or Last Refresh.

begin 

DBMS_MVIEW.REFRESH   ('apps.TEST_TABLE_JK_MV'); 

end;

/

select * from TEST_TABLE_JK; -- 2records

select * from TEST_TABLE_JK_V; -- 2 records

select * from TEST_TABLE_JK_MV;  -- 2 records

select object_NAME, Object_type, status from all_objects where object_name like 'TEST_TABLE_JK%';

-- Now try to recreate the View

Create or replace view TEST_TABLE_JK_V as (select * from TEST_TABLE_JK where rname1 <> 'XXX');

select object_NAME, Object_type, status from all_objects where object_name like 'TEST_TABLE_JK%';

--if we modify the view the dependent Mview is will get Invalid

select * from TEST_TABLE_JK; -- 2records

select * from TEST_TABLE_JK_V; -- 2 records

select * from TEST_TABLE_JK_MV;  -- 2 records

--But the select query will return the data as there was an table create with same name of Mview and it will remain in valid status even if we modified the Mview

begin 

DBMS_MVIEW.REFRESH   ('apps.TEST_TABLE_JK_MV'); 

end;

/

-- The Mview will get come back to valid status after refershing it.

-- Try to Refrsh with other command

begin 

DBMS_SNAPSHOT.REFRESH  ('apps.TEST_TABLE_JK_MV', 'F'); 

end;

/

-- It will through the below error

--Error report:

--ORA-12004: REFRESH FAST cannot be used for materialized view "APPS"."TEST_TABLE_JK_MV"

-- Here the paramenter 'F' is for Fast refresh

select * from dba_mviews where owner='APPS' and MVIEW_NAME='TEST_TABLE_JK_MV';

-- The Fast refresh column in the above Query is Set to 'NO' and it is the reason for Refresh Failed to FAst Refresh.

-- In this Case we should do complete reeresh instead of Fast Refresh (see the below statements)

begin 

DBMS_SNAPSHOT.REFRESH  ('apps.TEST_TABLE_JK_MV', 'C'); 

end;

/

-- The Above Statement will Refersh the Complete MV and it is Similar to "DBMS_MVIEW.REFRESH   ('apps.TEST_TABLE_JK_MV'); "

--Now let us try to Refresh with other command

begin 

DBMS_REFRESH.REFRESH  ('apps.TEST_TABLE_JK_MV'); 

end;

/

--It will show the Below eRROR

--Error report:

--ORA-23404: refresh group "APPS"."TEST_TABLE_JK_MV" does not exist

--the command "DBMS_REFRESH.REFRESH  ('apps.TEST_TABLE_JK_MV'); " will refersh only refresh groups  but not Standalone Mviews or Single Mviews Which or not linked to a Refresh Group.

--Refresh group is a Group of Mviews. this group can contain 1 or More Mviews and all the Mvies in one refresh group can be refershed using 'DBMS_REFRESH.REFRESH  ('apps.TEST_TABLE_JK_MV'); '

-- we can Query the Referesh group details using below select statement it will return the record with the regersh group name 'TEST_TABLE_JK'

select ROWNER,RNAME from DBA_REFRESH where ROWNER='APPS' and RNAME='TEST_TABLE_JK_MV';

-- Now let us try to create a refersh group with Name TEST_TABLE_JK_MV ( Here we can create a refresh with same nname as Mview also we can create with our own name)

BEGIN

DBMS_REFRESH.MAKE (

name => 'TEST_TABLE_JK_MV',

list => 'TEST_TABLE_JK_MV',

next_date => NULL,

interval => NULL,

implicit_destroy => TRUE,

purge_option => 1,

parallelism => 0,

heap_size => 0);

END;

/

select ROWNER,RNAME from DBA_REFRESH where ROWNER='APPS' and RNAME='TEST_TABLE_JK_MV';

-- Also we can add MVIEW to an Existing Refersh group

-- Lets Create another Mview First 

Create Materialized view TEST_TABLE_JK_MV1 as select * from TEST_TABLE_JK_V;

BEGIN

   DBMS_REFRESH.ADD (

      name => 'TEST_TABLE_JK_MV',

      list => 'TEST_TABLE_JK_MV1',

      lax => TRUE);

END;

/

---now  This Statement will refersh the Group and Mviews in the group with out error

begin 

DBMS_REFRESH.REFRESH  ('apps.TEST_TABLE_JK_MV'); 

end;

/

  

Steps to generate Action Plan

Please perform the following for the debug log:

when you are ready to reproduce the issue, set the FND Debug profiles and run the following:

1. To Enable FND debug:

Navigate Responsibility: System Administrator > Profile => System

a. Find the FND profile options for User who is submitting the process to debug.

User: User submitting the process
Profile: FND:%Debug%

b. Set the following Profile values at the user level

FND: Debug Log Enabled :Yes
FND: Debug Log Filename: NULL
FND: Debug Log Level : STATEMENT
FND: Debug Log Module : %

c. Save.

2. Run the following.

select max(log_sequence)
from fnd_log_messages;

Note the value (value 1)

--Reproduce the issue and run the following

select max(log_sequence)
from fnd_log_messages;

Note the value (value 2)

Now run the following using the two values from the above:

select module, message_text
from fnd_log_messages
where log_sequence between &value1 and &value2
order by log_sequence;

upload the output in Excel format.

3. Disable Trace and FND Debug by resetting the above profiles.

Sequence of SQL statement processed -- basic knowledge to increase the performance of your script

Sequence of SQL statement processed

When a query is submitted to the database, it is executed in the following order:

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

The following diagram will show a clear picture:

So why is it important to understand this?
When a query is executed, First all the tables and their join conditions are executed filtering out invalid references between them.

Then the WHERE clause is applied which again filters the records based on the condition given. Now you have handful of records which are GROUP-ed
And HAVING clause is applied on the result. As soon as it is completed, the columns mentioned are selected from the corresponding tables. And finally sorted using ORDER BY clause. So when a query is written it should be verified based on this order, otherwise it will lead wrong result sets.

Example:
Recently I came across the following problem.

The original query wasSELECT DISTINCT so.departure_airport, so.airport_name
FROM special_offers so, special_offers_groups_join sogj
WHERE so.resort_code = '000170' AND so.accomodation_code = '015397'
AND so.departure_date = '2008-01-13'
AND so.nights = '7'
AND sogj.group_id = '1'
AND sogj.special_offer_id = so.special_offer_id
ORDER BY price ASC
Table data:
departure_airport    airport_name                       price
'MAN'                      'Manchester Airport'           344
'MAN'                      'Manchester Airport'           288
'MAN'                      'Manchester Airport'            316
'BRS'                       'Bristol'                             289
'BRS'                       'Bristol'                             345
'BRS'                       'Bristol'                             317
'BHX'                      'Birmingham Airport'             343
'BHX'                      'Birmingham Airport'             287
'BHX'                      'Birmingham Airport'             315

Here the query is executed as follows:

All the records from both the tables are taken.

Conditions mentioned in the WHERE clause is applied fetching say 9 records.
Now the distinct of departure_airport and airport_name alone is taken since price is not in SELECT clause. This gives you three rows randomly selected (mostly in the order of insertion to the table!)
And the result is sorted based on price (incorrect price column due to distinct keyword).
So the output is not ordered properly based on price!

So we need to rewrite the query as follows:

All the records from both the tables should be taken.

Conditions mentioned in the WHERE clause needs to applied on the resulting resultset.

Group the resultset based on departure_airport and airport_name to get the minimum price in each group.

Departure_airport               airport_name                                   minprice
'BHX'                                'Birmingham Airport'                       287
'BRS'                                 'Bristol'                                       289
'MAN'                                'Manchester Airport'                      288

Select the columns departure_airport, airport_name and min(so.price) from the resultset. And finally apply the ORDER By clause which orders the resultset based on the column min(so.price).

So the query should be:SELECT so.departure_airport, so.airport_name, MIN(so.price) AS minprice
FROM special_offers so, special_offers_groups_join sogj
WHERE so.resort_code = '000170'
AND so.accomodation_code = '015397'
AND so.departure_date = '2008-01-13'
AND so.nights = '7'
AND sogj.group_id = '1'
AND sogj.special_offer_id = so.special_offer_id
GROUP BY so.departure_airport,so.airport_name
ORDER BY minprice;

Where clause and from clause for better performance: In SQL

Order of tables in the FROM clause should be:

1. Larger table

2. Smaller table

Order of conditions in WHERE clause should be:

2. Less selective

1. More selective

All Boolean conditions without built-in functions or sub queries are evaluated in reverse from the order they are found in the WHERE clause, with the last predicate being evaluated first.