Oracle Database Discussions

answer is version dependent & the plans for this new object.

do as below so we can know complete Oracle version & OS name.

Post via COPY & PASTE complete results of
SELECT * from v$version;

Only you can answer that question based on YOUR systems needs (why did you create the MV?). But you'll need to do some reading before you make that assessment.

http://download.oracle.com/docs/cd/E11882_01/server.112/e16579/qradv.htm#DWHSG8577
http://download.oracle.com/docs/cd/E11882_01/server.112/e16579/qradv.htm#DWHSG08011

Should be useful starters.

Unless you are planning to use dynamic sampling with that materialized view, you need to gather stats sooner than later. You (or the default stats gathering) would need to gather stats every time the volume or distribution in that MV changes significantly. Proper statistics are essential for achieving good performance.


Iordan Iotzov
http://iiotzov.wordpress.com/

select * from v$version;

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
"CORE	10.2.0.5.0	Production"
TNS for HPUX: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

Only you can answer that question based on YOUR systems needs (why did you create the MV?)

The reason behind creating materialized view is; there is select statement which user to take 2 to 3 hours for running and developers need results just ones in two months so instead of tuning it we came up the idea of creating materialized view. And this materialized view gets refreshed after every two months. I am not sure if we did the right thing.

CREATE MATERIALIZED VIEW LLNK.MVW_SAMPLE      NOCOMPRESS 
                                               NOLOGGING 
                                               TABLESPACE SAMPLE_DATA_TS 
                                               BUILD IMMEDIATE USING INDEX 
                                               REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 60 
  AS
  SELECT Name FOLDERNAME FROM EMP;

Are you really doing a straight SELECT with no WHERE clause and no aggregation in your materialized view? If so, why would it be more efficient to read N rows from your materialized view than to read N rows from your base table? If you're just eliminating some columns, it would probably be easier to do a full scan on an index on the NAME column.

Justin

Are you really doing a straight SELECT with no WHERE clause and no aggregation in your materialized view? If so, why would it be more efficient to read N rows from your materialized view than to read N rows from your base table? If you're just eliminating some columns, it would probably be easier to do a full scan on an index on the NAME column.

No, there is a where clause in the view. That is just a head for materialized view.

I analyzing mview won't impact the performance of the system(assuming that is not highly active database). It would use some resources to gather the statistics .

Thanks!
KR

If you need to run a query only once in 2 months (and it takes 3hours to run), your developer loses a max of 3 hours in 40 working days.
Does that justify creating a Materialized View ?

Hemant K Chitale