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