-
I have been asked to find out if there is anything in Oracle that is like an indexed view; that is, a view of data from several tables whose resulting columns (maybe computed values) can then be indexed independant of the indexes defined on the underlying tables. I have been looking through the documentation, but haven't really been able to find anything quite like this. I see the ability to use temporary tables with indexes, but can't really see where materialized views or index-tables are what is wanted.
Anyone with input about this?
Thanks much,
-
Since the views are of dynamic in nature, and does not occupy any physical space on the system, it is not worth the while to index them. But you always have to make sure that your underlying query that generates the view, better ustilize those table level indices to be more faster and efficient.
Though this is the basic theme behind the nature of views and thought of refreshing :)
Sam
-
Materialized views are similar to what you are looking for. These have been implemented as of 8i. These are nothing but snapshots of data that can be refreshed based on a schedule and can be used to create multiple views of the same base data for reporting purposes etc.
In previous versions, you could basically create a snaphot of a table in the same schema and index it differently for reporting purposes but starting with 8i, the oracle query_Rewrite option allows you to direct your queries to the materialized view if it see's any queries that can cost less when run against summarized data.
The details about them are too long to cover in the forum, but if you look in 8i docs under materialized views you should be able to find them
Check this article out on dbasupport.com
http://www.dbasupport.com/oracle/ora8/page8.shtml
and this doc on technet.oracle.com
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
