Real-Time Materialized Views in Oracle Database 12.2 Onward 

It will attempt a FAST using materialized view logs. If that is not possible it will attempt a partition change tracking refresh. If that is not possible it will do a complete refresh. Short answer. If you have materialized view logs, it's going to do a FAST.

@@oracle-base Thanks a lot for the answer

If it's too complication, as in not capable of a fast refresh, your only choice is a complete refresh. If you can do a fast refresh, but you need guaranteed up to date data, then real-time is good. If you can tolerate a little lag between refreshes, then a normal fast refresh is fine. It all depends what you need. There is rarely a "best".

@@oracle-base Thanks Tim. As you said, we have complete refresh only. But whenever we want to recreate the materialized view, it takes more time about 30 min to drop the MV. Is there anyway to drop fast and recreate.

@@kyuvarajbe1 Not sure why you would you drop and recreated, rather than just run a complete refresh. For a full refresh it's going to take as long as it takes.You can't do much about it except tun the query that populates it or get faster kit, to improve the speed of whatever is performance bottleneck.

A view is a stored query. Every time you run the view, you are running the query. If you add predicates or joins, they are merged into the stored query and run. A view is the same as a query. A materialized view stores the rows that would be returned by "select * from view" in a table segment, so when you query the materialized view, you are actually querying the materialized view segment, not the base tables. This means complex queries can be simplified as the hard work is done up front, when populating the materialized view. The problem is that population has to happen, and be kept up to date. Depending on the nature of the query, that can be a big overhead, and remember also you are storing the resulting rows, which can mean a lot of space used. Depending on how you refresh the MView, the can be a lag, so changes in the base tables are not reflected in the materialized view. A real-time materialized view solves some of he lag issue, but allowing the current session to wind forward any changes in memory. Once the query is complete, they are lost, so this doesn't refresh the materialized view. To answer your question, not, you should not replace all views with MViews. If you have specific problematic views, it might be worth considering a MView, or a real-time MView, but there is a though process you have to go through, including the additional space and processing required, and does that yield sufficient benefits from a performance perspective.

@@oracle-base Thank you soo much for your time and the knowledge