question on using materialized view that will be based on table having billions of rows [message #677661] |
Thu, 03 October 2019 15:16 |
wtolentino
Messages: 404 Registered: March 2005
|
Senior Member |
|
|
i was asked about materialized view that will be based on a table with billions of rows. the query is simple that does not have aggregate and sorts. it does not even have a inline views or subquery. but it do have a where clause.
this is just an example not the actual query it kind similar to this:
select col1, col2, col3
from mv_t_sample
where col2 in ('B','C','D');
completes in 11 seconds.
when the query is execute against a table that has billion of rows it also returns billions of rows. it was fast and returns rows in about seconds. the question is if the same query is used to build a materialized view would the response/completion time to build/refresh is the same as when executing the query?
for example
create materialized view mv_v_sample
using index
refresh complete
start with sysdate
next sysdate + 1
as
select col1, col2, col3
from mv_t_sample
where col2 in ('B','C','D');
when the materialized view is build/refresh will it also takes about 11 seconds?
thank you.
|
|
|
|
|