materialized view

[helpme]

i create a mat view like:
CREATE MATERIALIZED VIEW test_name
BUILD IMMEDIATE
REFRESH
COMPLETE
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT * FROM TEST_SAM

select * from test_name;

1 s
2 a
3 r

now i create :

CREATE VIEW test_name1 AS SELECT * FROM TEST_SAM;

select * from test_name1;

1 s
2 a
3 r

now i do
insert into test_sa values (4,'h');
inserted
commit;

now i do

select * from test_name;
it gives again

1 s
2 a
3 r

while

select * from test_name1;

1 s
2 a
3 r
4 h

why does normal views refresh and the materialized view not!!!

can any1 pls explain
thanx in advance

[pipo]

you wrote :
REFRESH COMPLETE ON DEMAND

and you wonder why it does not refresh ???

RTM !!!

[helpme]

so u say that i remove the "on demand" clause and it would give the refreshed data....?
or do i have to run some package for it to happen?
thanx

[pipo]

you have to :

RTM

PS : Read The Man

[venudba]

In the Normal view, the Sql statement will be stored. Whenever you query the View, it will execute the statement and retrieves the data.

Where as in the Materialized View, It will maintains a snapshot. The snapshot will be refreshed based on the defination of the Materialized view you created.

[helpme]

I removed and recreated it.....wihtout the o demand clause
but still it doesnt give me the refeshed data..

CREATE MATERIALIZED VIEW test_name
BUILD IMMEDIATE
REFRESH
complete
ENABLE QUERY REWRITE
AS
SELECT * FROM TEST_SAM

this is wat i used....

thanx

[Raminder]

I think you have to specify intervals at which Oracle will refresh data in your materialised view, in addition to refresh type.