Re: 10gR1 Materialized View - RowNum and Index

On Dec 17, 2:17 am, raja <dextersu..._at_gmail.com> wrote:
> Hi,
>
> I have a few doubts on creating Materialized Views (MV).
> Please clarify them [ Database is : Oracle 10gR1 (Datawarehouse) ] :
>
> 1. Can we create MV with rownum as one of the column ?
> for example :
> create materialized view mv1
> as
> select
> a.col1,
> b.col2,
> rownum
> from a, b
> where a.col1 = b.col2
> group by ...
>

You can but is there a valid business case for this?

> 2. What will be the performance impact, if we create MV with rownum ?
>

Try it and see. I've never had the 'need' to create such a materialized view and you do need to include either a primary key or a rowid in the create materialized view statement so I don't quite follow why you'd also want a 'counter' generated on the fly included in the data.

> 3. What will be the performance impact, if we create MV with rownum
> and Index created on :
> a. all the columns, including rownum ?

Try it and see. But remember that how you build your index is really dependent upon how you'll be querying the materialized view.

> b. all the columns, without rownum ?
>

See answer above.

> Thanks.
>
> With Regards,
> Raja.

David Fitzjarrell Received on Thu Dec 17 2009 - 08:49:59 CST