Re: create materialized view

fmarchioni_at_libero.it wrote:
: Hi all Oracle users !
: I'm trying to build a materialized view in
: order to import daily data from another DB

: CREATE MATERIALIZED VIEW VW_LINKS_155
: BUILD IMMEDIATE
: REFRESH FAST NEXT sysdate + 1

     ^^^^^^^^^^^^

A "fast" refresh means that oracle will track the changes in a table so that only the changes need to be replicated. To do that is has to be able to track the changes, and that requires things like a primary key (and a snapshot log perhaps - I forget).

: AS SELECT * FROM VW_LINKS_155_at_snap_dblink

: The matter is that Oracle complains that VW_LINKS_155_at_snap_dblink (a
: remote view accessed via snap_dblink ) doesn't have a PK constraint.

You can't do a fast refresh of that view then.

You can do a slow refresh (forget the words for that), or you might be able to change what you are asking oracle to do so there is an efficient way to do it, like build a snapshot on the snap_dblink host and index that snapshot and then replicate that snapshot to your snapshot, or add additional columns so oracle "sees" more information about the original table.

Master tables have to have snapshot logs also, not sure off hand exactly where that fits in to the above.

Someone mentioned ROWID, not sure off hand if it can help here - but the thing is to realize that snapshots can have various setups, some more efficient than others. You need to read the manual to see what options are available, and try to match them to the functionality you can provide for setting up your table, perhaps adding an intermediate view or step to get all the efficient options into the picture.

: Forgive my ignorance but views cannot inherit PK from their tables nor
: it's possible to target a PK on a view ?

I think that oracle is smart enough to use any available indexes in accessing a view as long as the indexing can be mapped to what the view shows. Oracle may or may not be able to pass various optimizations "down" into a view, but that is a different thing.

: Can you give me a good advice ?

good advice? I won't claim this is good advice, simply the things I have found useful to think about in the past.

--

This space not for rent.