Re: 10gR1 Materialized View - RowNum and Index

From: ddf <oratune_at_msn.com>
Date: Thu, 17 Dec 2009 08:46:34 -0800 (PST)
Message-ID: <8840e1fe-5966-47c4-a946-a9406888e878_at_s21g2000prm.googlegroups.com>

On Dec 17, 10:10 am, Shakespeare <what..._at_xs4all.nl> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

I'd agree on the alias but ROWNUM is an assigned value to the result set, not the source data so there is no need to prefix ROWNUM with anything:

 select o.owner, o.object_name, s.tablespace_name, rownum rn  from dba_objects o, dba_segments s
 where s.segment_name = o.object_name
 and s.owner = o.owner;

returns data without error.

Here is more information on ROWNUM:

http://oratips-ddf.blogspot.com/2008/06/row-row-row.html

David Fitzjarrell Received on Thu Dec 17 2009 - 10:46:34 CST