Home » SQL & PL/SQL » SQL & PL/SQL » question on using materialized view that will be based on table having billions of rows (Oracle Database 12c Enterprise Edition Release 12.2.0.1.0)
question on using materialized view that will be based on table having billions of rows [message #677661] Thu, 03 October 2019 15:16 Go to next message
wtolentino
Messages: 390
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.
Re: question on using materialized view that will be based on table having billions of rows [message #677663 is a reply to message #677661] Thu, 03 October 2019 20:09 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
So what is the point of even creating such an MV? What do you expect from it?

The whole purpose of an MV is to 'pre-process' the data when the result involves some intensive aggregation or other such processing. Your query does nothing but one simple filter that is easily optimized with an index. An MV based on this would be pointless. If you are really needing a performance boost on that query, you'd probably be better off simply partitioning on COL_2, so that the optimizer could use partition pruning. But, depending on the data density of COL_2, even partitioning may not yield any benefits.

As is so often the case, you need to forget your pre-conceived technical solution and tell us the root problem you are trying to solve.
Beware of the x-y problem.
Re: question on using materialized view that will be based on table having billions of rows [message #677670 is a reply to message #677661] Fri, 04 October 2019 02:43 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
The elapsed time will likely be much longer, because of all INSERT activity on the MV container table. The way you have written it means that you will get undo and redo as well.
Previous Topic: Oracle 11g: step by step guide to send email via utl_smtp with all requirements from scratch (3 merged)
Next Topic: How do I ignore row giving error?
Goto Forum:
  


Current Time: Thu Mar 28 14:10:58 CDT 2024