Oracle equivalent of 'indexed views' ??
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Oracle equivalent of 'indexed views' ??

  1. #1
    Join Date
    Jan 2001
    Posts
    63
    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,

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Feb 2001
    Posts
    176
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width