Re: Materialized View - Performance

raja wrote:
> Hi,
>
> I am just exploring the ways to tune a MV.
> I have a MV which is a union of data from 2 tables ( no where
> conditions), so its going for full table scan for both the tables.
> I want to know whether we can use the following in MV :
> 1. Hints - can we use hints like fast full scan,.....
> 2. With Clause - since in another MV, looks like a query is getting
> repeated more than once.
>
> With Regards,
> Raja.

so the MV is something like:
create mv .... as
select * from a
union
select * from b
<storage clause> etc...
;

???

If so, the only thing it **can** do is a full table-scan as well as write to the MV TS and no hint will help you there... If you are not using ASM, hopefully you are writing to different tablespaces on different file systems. Received on Thu Dec 04 2008 - 21:20:13 CST