Home » Server Options » Replication » snapshot & materialised view in 9i
snapshot & materialised view in 9i [message #29999] Mon, 05 April 2004 23:54 Go to next message
suresh reddy
Messages: 10
Registered: May 2002
Junior Member
 

hi

what is the difference between

SNAPSHOT AND MATERIALISED VIEW IN 9I

PLZ SEND IF ANY ONE KNOW THE ANSWER

WITH REGARDS

SURESH

 
Re: snapshot & materialised view in 9i [message #30012 is a reply to message #29999] Tue, 06 April 2004 12:57 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Straight from documentation :

"
--------------------------------------------------------------------------------
Note:
In past releases of Oracle, "materialized views" were called "snapshots". The terms are synonymous. In this appendix, "materialized view" is used, even when discussing past releases.

"

"Materialized View Concepts
Oracle uses materialized views (also known as snapshots in prior releases) to replicate data to non-master sites in a replication environment
"

-Thiru
Re: snapshot & materialised view in 9i [message #30016 is a reply to message #30012] Tue, 06 April 2004 20:25 Go to previous messageGo to next message
Amit Jain
Messages: 33
Registered: August 2001
Member
See as far as I think

Snapshots are the replica of data when any Select statement or Cursor fetch the data to the client machine in client server architechture.

So if after some time say half an hour you need to work on the data fetched as Snapshot but due to some unavailability of space Operating System frees the memory occupied by Snapshot in RAM and assigns that memory to some other Select statement's data or Cursor's data and we want to work on that snapshot again, we will get the error message "Snapshot too old". So we can say snapshot is nothing but the set of records fetched through some Select Statement or Cursor.

While Materialized View are nothing but the defination of complex Select statement stored in the database with the data. So it will work for other sessions too.

So the basic difference between the snapshot and materialized view is this that the snapshot's scope is the current session only while materialized view's scope is not bound to any session.

Amit Jain
Re: snapshot & materialised view in 9i [message #30051 is a reply to message #30016] Wed, 07 April 2004 15:12 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
You couldnt be more wrong. And I think I know how all these things work,believe me!

1st mistake : " Snapshots are the replica of data when any Select statement or Cursor fetch the data to the client machine in client server architechture."

The data you are talking about is 'read consistent image' as of the snapshot SCN. Just becos you get an error 'snapshot too old' , it doesnt mean it is a SNAPSHOT. Let me know if you need more details on this.

Secondly, you are again misleading the original poster by comparing 'read consistent image' and materialized views,which is not the question. He wants to understand if there is any difference between Oracle Snapshots and Oracle Materialized views in 9i and I've answered that in my earlier posting.


-- Querying user_snapshots for any snapshots

thiru@9.2.0:SQL>select name,table_name,query from user_snapshots;

NAME                           TABLE_NAME
------------------------------ ------------------------------
QUERY
--------------------------------------------------------------------------------
T_MVIEW                        T_MVIEW
select deptno,sum(sal) from t group by deptno

-- It shows one snapshot
-- Lets drop it. 

thiru@9.2.0:SQL>drop materialized view t_mview;

Materialized view dropped.

thiru@9.2.0:SQL>select name,table_name,query from user_snapshots;

no rows selected

-- The snapshot is gone . (ie the materiazed view is gone)

-- Lets create a materialized view

thiru@9.2.0:SQL>create materialized view MVIEW as select deptno,sum(sal) from t group by deptno;

Materialized view created.

-- and now query user_snapshots

thiru@9.2.0:SQL>select name,table_name,query from user_snapshots;

NAME                           TABLE_NAME
------------------------------ ------------------------------
QUERY
--------------------------------------------------------------------------------
MVIEW                          MVIEW
select deptno,sum(sal) from t group by deptno

-- User_snapshots shows one materialized view

-- Now,lets create a Snapshot

thiru@9.2.0:SQL>create snapshot tsnapshot as select deptno,sum(sal) from t group by deptno;

<B>Materialized view created.</B>

-- Note, it says Materialized view created when you create a snapshot.

-- Now,lets query user_mviews to show materialized views

thiru@9.2.0:SQL>select mview_name,query from user_mviews;

MVIEW_NAME                     QUERY
------------------------------ -----------------------------------------------------
MVIEW                          select deptno,sum(sal) from t group by deptno
TSNAPSHOT                      select deptno,sum(sal) from t group by deptno

-- and it shows the two snapshots (materialized views) we created earlier . 


-Thiru
thanks thiru [message #30062 is a reply to message #30051] Wed, 07 April 2004 23:18 Go to previous messageGo to next message
suresh reddy
Messages: 10
Registered: May 2002
Junior Member
hi
you have give nice answer for this question
actually i faced this question in my intervuew(i-flex)
which i lost because of this question
suresh
Re: snapshot & materialised view in 9i [message #30974 is a reply to message #30051] Thu, 03 June 2004 01:11 Go to previous message
neeraj
Messages: 16
Registered: October 2001
Junior Member
Dear Friends,

We are creating some MVs. One MV's query is returning the data but after MV creations, we are not getting any data in that MV. Query is as follow :

CREATE MATERIALIZED VIEW LOS_MV_REPO18_REPAY_FIRST
NOCACHE
NOPARALLEL
REFRESH FORCE
START WITH TRUNC(SYSDATE)
NEXT TRUNC(SYSDATE)+1
WITH ROWID
USING DEFAULT LOCAL ROLLBACK SEGMENT
DISABLE QUERY REWRITE AS
SELECT AGREEMENTID AGR,AGREEMENTID,A.AGREEMENTNO,A.APP_FORMNO,C.CUSTOMERNAME,
Los_Fn_Get_Coapplicant(AGREEMENTID) CO_APP,
P.DESCRIPTION,TO_CHAR(A.AMTFIN,'99,999,999,990.99') AMTFIN,
A.TENURE,
DECODE (A.FLOATINGFLG,'Y',TO_CHAR(PLRRATE,'99,999,999,990.99'),NULL) PLRRATE,
TO_CHAR(MARKUP,'99,999,999,990.99') MARGINMONEY,TO_CHAR(EFFRATE,'99,999,999,990.99') EFFRATE,
TO_CHAR(EMI,'99,999,999,990.99') EMI
FROM LEA_AGREEMENT_DTL_LMS A,
NBFC_CUSTOMER_M_LMS C,
NBFC_PRODUCT_M_LMS P
WHERE A.LESSEEID = C.CUSTOMERID
AND A.PRODUCTFLAG = P.CODE
AND A.STATUS <> 'X';

Please help me in identifying the reason.

With Rgds..

Neeraj
Previous Topic: Oracle 8i User written Function calls from another package
Next Topic: Snapshot Issue
Goto Forum:
  


Current Time: Thu Oct 02 04:47:20 CDT 2014

Total time taken to generate the page: 0.13906 seconds