| Materialized view vs Table [message #163040] |
Tue, 14 March 2006 13:50  |
a_developer
Messages: 194
Registered: January 2006
|
Senior Member |
|
|
We need to create a materialized view which we build as 'DEFERRED' and later on REFRESH as COMPLETE. During the refresh, it takes a longer time, than just creating a table instead. Why is it so?? I use NOLOGGING in both operations.
I thought materialized views are better.. if so, is there something I am missing??
|
|
|
|
| Re: Materialized view vs Table [message #163074 is a reply to message #163040] |
Tue, 14 March 2006 20:55  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
A COMPLETE refresh should simply truncate the table and then fill it up again. Delays could be caused by:
- The MV is stored in a Hash Cluster (very unlikely) - Oracle has to DELETE rather than TRUNCATE.
- The MV is the base table of other fast-refresh MV's. ie. The MV has an MV Log attached. Oracle needs to update the timestamp on any rows in the MV Log.
I can't think of any other reasons off hand. You could trace the Refresh and then use TK*Prof to tell you what took all of the time. See the Oracle Utilities manual for instructions on TK*Prof and SQL Trace.
_____________
Ross Leishman
|
|
|
|
| Re: Materialized view vs Table [message #163384 is a reply to message #163040] |
Thu, 16 March 2006 09:32 |
croK
Messages: 170
Registered: April 2002
|
Senior Member |
|
|
Correct me if I am wrong:
1. you create the mv as DEFFERRED. That means that actually
no data is loaded at the creation process.
2. you then refresh the mv as complete. This is the moment
where the table is actually loaded with data, so, it will
take longer because you did not load data
in step 1.
Best luck
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
