Re: 10gR1 Materialized View - RowNum and Index
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 17 Dec 2009 16:10:44 +0100
Message-ID: <4b2a49f2$0$22917$e4fe514c_at_news.xs4all.nl>
raja schreef:
> 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 ...
>
> 2. What will be the performance impact, if we create MV with rownum ?
>
> 3. What will be the performance impact, if we create MV with rownum
> and Index created on :
> a. all the columns, including rownum ?
> b. all the columns, without rownum ?
>
> Thanks.
>
> With Regards,
> Raja.
Date: Thu, 17 Dec 2009 16:10:44 +0100
Message-ID: <4b2a49f2$0$22917$e4fe514c_at_news.xs4all.nl>
raja schreef:
> 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 ...
>
> 2. What will be the performance impact, if we create MV with rownum ?
>
> 3. What will be the performance impact, if we create MV with rownum
> and Index created on :
> a. all the columns, including rownum ?
> b. all the columns, without rownum ?
>
> Thanks.
>
> With Regards,
> Raja.
I would define an alias for this column, and you may have to prefix it with the table you want the rownum from.
Shakespeare Received on Thu Dec 17 2009 - 09:10:44 CST