0

When materialized views are created in Oracle, do they store indices or do they store actual table values?

I am asking this as creating index on table and using views on that table and using materialized views (created with refresh complete start with (sysdate) next (sysdate+1) with rowid as) on unindexed table gives similar performance.

Where as I would expect materialized views to be far more faster.

Update

I slightly modified the content/title. My current concern after discussion is if materialized views are actual real tables or virtual tables with some optimization.

Dinesh
  • 1,555
  • 1
  • 16
  • 18
  • Your question doesn't make a lot of sense, but in any case: Are you aware that you can also create indexes ON MATERIALIZED VIEWS? See if that helps you with your performance. –  Jul 05 '19 at 15:42
  • Yeah, I am aware of that. The materialized view is really small table and I am not creating views on top of materialized views. – Dinesh Jul 05 '19 at 16:04

2 Answers2

4

Materialized views create a copy of the data. To all intents and purposes they are actual tables. In fact we can create a materialized view from an existing table using the PREBUILT clause. The only difference is how the data is mastered - a materialized view doesn't own its data, a table does.

As to your performance conundrum:

When you say "on unindexed table" do you literally mean one table? If so, we wouldn't expect any difference in the time to query a view, a materialized view or the actual data: they all execute a full table scan on the same volume of data.

Consider the case where views have expecting select * from <table> where <condition>.

We would a SELECT against a materialised view built on that query to execute quicker than the same SELECT against the actual table, provided the WHERE clause restricts the data to a significantly smaller subset of the original data. Simply because a full table scan over a small table (materialised view) takes less time than a full table scan over a big table. Same applies if the materialised view's projection has fewer columns than the base table.

Indexing is a different matter. Unless the query selects a very small subset of the data it's not going to be more efficient than a full table scan and a filter.

To sum up: the only universal tuning heuristic is: it takes less time to do less work. Beyond that it is impossible to generalise. We can't discuss some vague "consider the case where views have select * from <table> where <condition>." It's all about the specifics.

APC
  • 144,005
  • 19
  • 170
  • 281
  • Thanks for the comment. Consider the case where views have select * from where . I understood from answers on https://stackoverflow.com/questions/1108/how-does-database-indexing-work , that indexing or materialized views would make such statements perform efficiently.
     – Dinesh Jul 05 '19 at 16:12
  • I meant create index on a column of a table if that was the point. – Dinesh Jul 05 '19 at 16:15
  • Thanks for the clarification. I agree it is about the details. – Dinesh Jul 05 '19 at 17:10
  • Just to wrap up and clarify, so materialized views are real tables with actual values and not some optimizations and virtual tables right? – Dinesh Jul 05 '19 at 17:14
  • Cheers. I really appreciate your time and patience. – Dinesh Jul 05 '19 at 17:18
2

Fundamentally, a materialized view is just a table with an associated query to populate it.

Given static data, one would generally expect the performance of a SELECT * from the materialized view (with no WHERE clause) to be at least as fast as running the query that underlies the materialized view, regardless of indexing.

If we add a WHERE clause to a SELECT * against the mview, however, that query could perform significantly slower than running the query that underlies the mview with the same WHERE clause. That's because the tables referenced in the query underlying the mview could have indexes to support the conditions in the WHERE clause, where as the mview might not have such indexes.

cdub
  • 1,420
  • 6
  • 10