Materialized Views and Functional
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
WHERE (revenue - cost) > 1000;
We have to enable Function-based indexes by enableing the following
ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
Have a Oracle Question
Do you have
an Oracle Question?
Certification, Database Administration, SQL, Application, Programming Reference
Application Hints and Tips
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
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.