Re: Materialized View - Performance

raja wrote:
> Hi,
>
> Thanks for ur response, Michael Austin.
>
> As you said, i have one of ur scenarios ( 1st scenario ) as :
> create mv
> as
> select ...
> union all
> select ...
> here it will go for full table scan only.
> In this scenario, can i create index for all the select columns and
> then also use fast-full-scan hint, to take the index alone scan, so
> that the fetching of data is faster ?
>
> 2nd scenario is that :
> create mv
> as
> select....
> from ...( subquery 1 )
> ....
> (subquery 1 )
> union all
> select .....
> In the above scenario, a subquery is getting repeated more than once
> in the 1st select clause, can i implement WITH Clause here as :
>
> create mv
> as
> with a
> (subquery 1)
> select ....
> from ...a /* instead of the subquery, implementing the WITH Clause
> */
> union all
> select .....
>
> Is the above one correct ?
>
> 3rd scenario is that : same as above, having the subqueries getting
> repeated in both the select clauses : such as :
>
> create mv
> as
> select....
> from ...( subquery 1 )
> ....
> (subquery 1 )
> union all
> select....
> from ...( subquery 1 )
> ....
> (subquery 1 )
>
> Can i implement WITH Clause here also, similar to what i have done in
> 2nd scenario solution ?
>
> With Regards,
> Raja.

If the columns in your WHERE clause in the subquer{y|ies} are indexed, it should use the index to fetch the data. Otherwise, FTS is as good as it gets. Received on Sat Dec 06 2008 - 17:53:30 CST