Re: 10gR1 Materialized View - RowNum and Index
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 17 Dec 2009 18:52:33 +0100
Message-ID: <4b2a6fdd$0$22936$e4fe514c_at_news.xs4all.nl>
ddf schreef:
>> 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 -
Date: Thu, 17 Dec 2009 18:52:33 +0100
Message-ID: <4b2a6fdd$0$22936$e4fe514c_at_news.xs4all.nl>
ddf schreef:
> 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
Right, my mistake..... was thinking about rowid.
Shakespeare Received on Thu Dec 17 2009 - 11:52:33 CST