Re: materialized views in Oracle 9

On Fri, 6 Feb 2004 19:55:39 +0100, Envoy <thenewone_at_freakmail.de> wrote:

>Hi,
>
>I want to use materialized views to speed up my queries. To check if it is working I used the query I
>want to accelerate in the statement to create the m-view.
>
>example:
>Query to accelerate:
> SELECT * FROM student, person
> WHERE s_ID=p_ID
> AND s_sex='female';
>
>m-view definition:
>CREATE MATERIALIZED VIEW Anfr23_MV AS
> SELECT * FROM student, person
> WHERE s_ID=p_ID
> AND s_sex='female';
>
>Unfortunately the view did not help to speed up the query. Furthermore the execution plan did not
>mention the m-view.
>
>What is my mistake?
>Thanks for any help.
>Best regards,
>Michel

First of all if you think you need to use materialized views to speed up simple joins, there is something seriously wrong with your understanding of plan optimization and materialized views.

Secondly, in order for materialized views to operate at all you need to have set
query_rewrite_enabled
to true.
(as documented, of course)

--
Sybrand Bakker, Senior Oracle DBA