Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

MV without rowids specified in its select statement

709382Aug 26 2009 — edited Aug 26 2009
Hi ,

Can we create a MV With out rowids specified in its DDL's select statement when we are creating a MV with 2 tables join.
This is my requirement but it seems its not possible.Is there any chance of doing this.

Thanks
Pramod
This post has been answered by Centinul on Aug 26 2009
Jump to Answer

Comments

Tubby
The documentation
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6002.htm#SQLRF01302

Morgans Library
http://www.psoug.org/reference/materialized_views.html

Read them.
709382
HI

Rather my question is

Can we create a MV which can be refreshed FAST With out rowids specified in its DDL's select statement when we are creating a MV with 2 tables join.


Thanks
Pramod
Tubby
You changing the question doesn't modify my answer. This is information readily available in the documentation. IF you cannot determine that from the documentation, then you would need to come back with a plethora of information you've left out of your post like complete Oracle version (shockingly, what you can do on 10.2.0.4 is not the same as 7.3.4), data structure definitions / relations, etc....
Centinul
Answer
Pramod Garre wrote:
Can we create a MV which can be refreshed FAST With out rowids specified in its DDL's select statement when we are creating a MV with 2 tables join.
As noted previous you are best to check the documentation. Here is an excerpt from the documentation on this subject (emphasis added):
Restrictions on Fast Refresh on Materialized Views with Joins Only
Defining queries for materialized views with joins only and no aggregates have the following restrictions on fast refresh:

* All restrictions from "General Restrictions on Fast Refresh".
* They cannot have GROUP BY clauses or aggregates.
** Rowids of all the tables in the FROM list must appear in the SELECT list of the query.*
>

So, if the issue is that when you describe the view you have ROWIDs and you don't want them to be there then I recommend implementing a regular view on top of the materialized view that hides these columns.

HTH!
Marked as Answer by 709382 · Sep 27 2020
709382
Hi Centinul ,

I have definetly gone through the Documentation before posting .

Now you have suggested that we can have a view on top of MV to hide ROWiD s.

This is what i wanted .

Thanks
Pramod
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 23 2009
Added on Aug 26 2009
#general-database-discussions
5 comments
346 views