MVs vs. Views

We have a check on many queries in our application that joins 3 tables together. I want to create a view or MV so as to improve performance.

After reading up on MVs and Views I am a little confused what would be best.

The end result does not do aggregates and only checks against 3 or 4 fields that lie in the 3 tables of the join.
The data does not change or update very often in any of the 3 tables.
Also, the 3 tables do not hold a large amount of data.

Are MVs MAINLY for aggregates or very large tables? Or are they more efficient than views in general?

The only requirement would be that when new rows periodically get inserted into the 3 tables (they are dependant upon each other) that the view or MV reflect the new rows.

Thanks for your advice.

A view will doing nothing for your performance -- all it does is "encapsulate" a query. It still gets executed the same as the original query would.

A materialized view could provide a pre-joined, indexed result set based on all, or a limited set of, the base table columns, but if you want it to update automatically every time a base table changes ("on commit" option) then you introduce an overhead for the query that is performaing the insert, update or delete on the base tables. You will want to minimize the impact by performing fast refreshes which means placing snapshot logs on each base table.

If you go this route, note that Oracle recommend that if you are going to insert some rows, delete some rows, and update some rows as part of the same transaction then you can improve refresh performance by commiting between each type of operation -- insert/commit/delete/commit/update/commit.

Thanks for your comments.