10gR1 Materialized View - RowNum and Index

[raja]

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.

[ddf]

Comments embedded,

On Dec 17, 2:17 am, raja <dextersu...@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

[Shakespeare]

raja schreef:

I would define an alias for this column, and you may have to prefix it
with the table you want the rownum from.

Shakespeare

[ddf]

> 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

[Shakespeare]

ddf schreef:

Right, my mistake..... was thinking about rowid.

Shakespeare

[raja]

Hi,

Thanks for all ur responses.
Sorry, but I think, still i am not convinced with the answers.
The question that i asked is related to PERFORMANCE impact.

Will there be an impact in performance ( either increase or decrease )
when we create a MV with ROWNUM as one of the columns :
1) having ROWNUM as one of the INDEXED columns,
2) having ROWNUM not indexed

Thanks.

With Regards,
Raja.