Materialized Views and Functional Index

What are materialized views? When are they used?

Materialized view is like a view but stores both definition of a view plus the rows resulting from execution of the view. It uses a query as the bases and the query is executated at the time the view is created and the results are stored in a table. 

You can define the Materialized view with the same storage parametes as any other table and place it in any tablespace of your choice. 

You can also index and partition the Materialized view table like other tables to improve performance of queries executed aginst them.  
 
Use of Meterialized view:- 
 
Expensive operations such as joins and aggregations do not need to be re executed. 
If the query is astisfied with data in a Meterialized view, the server transforms the query to reference the view rather than the base tables.
 

What is a functional index - explain?

Function-based indexes can use any Function or Object method that is declared as repeatable. 

Queries using expressions can use the index. 
Ex: - CREATE INDEX sales_margin_inx 
ON sales (revenue - cost); 
 
Sql> SELECT ordid 
FROM sales 
WHERE (revenue - cost) > 1000; 

We have to enable Function-based indexes by enableing the following initialization parameters:

ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE; 
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED; 

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.