| Oracle 9i materialized view refresh problems [message #184453] |
Wed, 26 July 2006 10:36  |
radhakrishnane
Messages: 4
Registered: July 2006
|
Junior Member |
|
|
hi, i'm trying to create a materialized view in oracle 9i to speed up some of our reports. It creates ok and the 'refresh state' goes to 'FRESH' (seen from enterprise manager console). But, in 5 mins, when the mv is set to automatically refresh, both the 'refresh state' and 'compile state' goes to 'NEEDS_COMPILE'. I have created mv_logs on all the schema/tables that the mv uses. Below is the sql i used to create the mv. TIA.
CREATE MATERIALIZED VIEW "SCHEMA1"."MV_TEST"
BUILD IMMEDIATE
REFRESH FORCE
START WITH to_date('18-Jul-2006 11:00:00 AM','dd-Mon-yyyy HH:MI:SS AM')
NEXT sysdate + 5/1440
AS
select 'TEXT' type, to_char(actiondate,'YYYY') TRANS_YEAR, substr(get_desc(trans_type),30) DESC, decode(trans_type,'A', get_qty(table_b.param1,table_c.param2), get_diff_qty(table_b.param1,table_c.param2,table_a.param3)) QTY
from table_a, table_b, table_c
where table_a.trans_type in ('A','B')
and table_a.trans_type = table_b.trans_id
and table_a.actiondate = table_c.transdate (+)
and table_a.trans_type = table_c.type_id (+)
and table_a.fld1 = table_c.fldc (+)
and table_c.fldd (+) = 'value01'
union all
select 'TEXT' type, to_char(actiondate,'YYYY') TRANS_YEAR, substr(get_desc(trans_type),30) DESC, decode(trans_type,'A', get_qty(table_b.param1,table_d.param2), get_diff_qty(table_b.param1,table_d.param2,table_a.param3)) QTY
from table_a, table_b, table_d
where table_a.trans_type in ('C','D')
and table_a.trans_type = table_b.trans_id
and table_a.actiondate = table_d.transdate (+)
and table_a.trans_type = table_d.type_id (+)
and table_a.fld1 = table_d.fldc (+)
and table_d.fldd (+) = 'value01'
|
|
|
|
|
|
|
|
| Re: Oracle 9i materialized view refresh problems [message #184696 is a reply to message #184690] |
Thu, 27 July 2006 08:47  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Sorry - missed that bit.
Acording to the docs:
NEEDS_COMPILE - Some object upon which the materialized view depends has changed (other than normal DML changes). An ALTER MATERIALIZED VIEW...COMPILE statement is required to validate this materialized view.
Where are you getting the 'refresh state' from?
It looks like something is altering your table after the view is created.
|
|
|
|
| Re: Oracle 9i materialized view refresh problems [message #184729 is a reply to message #184453] |
Thu, 27 July 2006 10:26 |
radhakrishnane
Messages: 4
Registered: July 2006
|
Junior Member |
|
|
thanks for the help.
i'm using enterprise manager to view the refresh state.
after i create the mv, the refresh state is 'fresh'. and then, i just add a record to one of the tables and immediately the refresh state goes to 'needs_compile' and so does the compile state.
one other thing i noticed is that the 'can use log' column (on the enterprise manager) for this mv says 'no'. i have set up refresh logs on all the tables that the mv uses; not sure if that 'can use log' has anything to do with the refresh log.
pls bear with me if i'm asking basic questions. tia.
|
|
|
|
| Re: Oracle 9i materialized view refresh problems [message #184814 is a reply to message #184729] |
Fri, 28 July 2006 02:55 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I'm a bit in the dark here too.
Some questions:
1) What is the STATUS of this view in ALL_OBJECTS when its COMPILE_STAT is NEEDS_COMPILE?
2) What version of Oracle are you running
3)i) If you compile the view, add some data, commit (so the status goes to 'NEEDS_COMPILE', can you still select the original data from the view?
ii) When the next refresh has happened, can you now see the new data in the view?
|
|
|
|
| Re: Oracle 9i materialized view refresh problems [message #184872 is a reply to message #184814] |
Fri, 28 July 2006 07:31 |
radhakrishnane
Messages: 4
Registered: July 2006
|
Junior Member |
|
|
thks for the reply.
the status of that mv in all_objects in 'invalid'.
i'm running oracle 9i (9.2.0.4.0)
yeah, even when the mv is in needs_compile state, i can still select my old records; but the one i just added does not appear.
and, when i manually compile & refresh it again, i can see my new data there.
thanks for your help.
|
|
|
|
| Re: Oracle 9i materialized view refresh problems [message #184879 is a reply to message #184872] |
Fri, 28 July 2006 08:20 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The switching to 'Needs Compile' is described in metalink as normal behaviour (Doc Id 264036.1 )
I can reproduce the problem, but when my views refresh, the compile state switches backto VALID and the new data is visible.
Can you just check with
select mview_name,last_refresh,start_with from user_mviews
and ee if your mview is refreshing itself at all.
|
|
|
|
|
|
| Re: Oracle 9i materialized view refresh problems [message #204112 is a reply to message #184453] |
Fri, 17 November 2006 21:03 |
jacklhg
Messages: 1
Registered: April 2006
|
Junior Member |
|
|
To make ON COMMIT fast refresh possible,
I think you can first try to add Rowids of all the tables in the FROM list to the SELECT list of the query.
To find why fast refresh is not allowed by yourself, you may try this:
SQL>EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW('YOUR_MV_NAME');
SQL>select capability,possible from mv_capabilities_table where mivew_name='YOUR_MV_NAME';
The following is for your information:
Restrictions on Fast Refresh on Materialized Views with Joins Only
Defining queries for materialized views with joins only and no aggregates have the following restrictions on fast refresh:
All restrictions from "General Restrictions on Fast Refresh".
They cannot have GROUP BY clauses or aggregates.
Rowids of all the tables in the FROM list must appear in the SELECT list of the query.
Materialized view logs must exist with rowids for all the base tables in the FROM list of the query.
|
|
|
|
|
|
| Re: Oracle 9i materialized view refresh problems [message #204159 is a reply to message #184453] |
Sat, 18 November 2006 05:16 |
jacquesh
Messages: 6
Registered: November 2006
|
Junior Member |
|
|
The real problem is about NEEDS_COMPILE value in ALL_MVIEWS ?
From a complex query with sub-select, analytic function (LAG, LEAD,
...) I've create a materialized view with query rewrite option enabled.
my problem as follow : Each refresh of MV failed with an error in
ALL_MVIEWS ; the column 'COMPILE_STAT' is set to NEEDS_COMPILE.
If I try a manual refresh it's ok, when i compile view via an alter
mat. view ... it's ok...
any idea to fix this 'bug' ??
thanks.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
)