CODEWITHSUNDEEP
oracle-databaseperformancematerialized-views
Dinesh
Dinesh

Reputation: 1565

Are materialized views virtual tables or real tables with real data?

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.

Upvotes: 0

Views: 505

Answers (2)

cdub
cdub

Reputation: 2297

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.

Upvotes: 2

APC
APC

Reputation: 146239

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.

Upvotes: 4

Related Questions