Re: Materialized View - Performance

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. Received on Sat Dec 06 2008 - 02:45:12 CST