In this case though I wasn't especially after the query rewrite (though that
would be nice for another app) but after a snapshot. I have some code that
has been sitting around for a while that periodically updates a table with
data from across a db link. Aha I thought that'd be better off done using a
snapshot so I issued "create snapshot ....' and got back "materialized view
created". This got me to thinking when is a snapshot not a snapshot and
thence to what exactly is and isn't a feature of standard edition.
I agree that a materialized view is more than a snapshot. At least in EE.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message
news:3c110b94$0$29051$afc38c87_at_news.optusnet.com.au...
> Pardon me for butting in.... but the killer line here is:
>
> > Materialized view rewrite FALSE
>
> Since whether to use a materialized views is a decision made by the
> opimizer, and since if it chooses to use it requires it to re-write your
> SQL, then you appear to be able to create them, but never use them. (Of
> course, you can always issue the command 'select * from mview_whatever'
> directly, but the point of these things is that you issue 'select * from
> emp' and the *optimizer* silently re-writes that to be 'select * from
> mview').
>
> Yours insomniacally
> HJR
> --
> Resources for Oracle: http://www.hjrdba.com
> ===============================
>
>
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:3c10ce37$0$227$ed9e5944_at_reading.news.pipex.net...
> > PARAMETER VALUE
>
> ---------------------------------------------------------------- ---------
> --
> > ---
> > Partitioning FALSE
> > Objects TRUE
> > Parallel Server FALSE
> > Advanced replication FALSE
> > Bit-mapped indexes FALSE
> > Connection multiplexing TRUE
> > Connection pooling TRUE
> > Database queuing FALSE
> > Incremental backup and recovery FALSE
> > Instead-of triggers TRUE
> > Parallel backup and recovery FALSE
> > Parallel execution FALSE
> > Parallel load TRUE
> > Point-in-time tablespace recovery FALSE
> > Fine-grained access control FALSE
> > N-Tier authentication/authorization TRUE
> > Function-based indexes FALSE
> > Plan Stability FALSE
> > Online Index Build FALSE
> > Coalesce Index FALSE
> > Managed Standby FALSE
> > Materialized view rewrite FALSE
> > Materialized view warehouse refresh FALSE
> > Database resource manager FALSE
> > Spatial FALSE
> > Visual Information Retrieval FALSE
> > Export transportable tablespaces FALSE
> > Transparent Application Failover FALSE
> > Fast-Start Fault Recovery FALSE
> > Sample Scan FALSE
> > Duplexed backups FALSE
> > Java FALSE
> > OLAP Window Functions FALSE
> >
> > 33 rows selected.
> >
> > what are you doing up at this time?
> >
> > Upon further thinking I'm assuming that because a materialized view is
> > effectively `a snapshot and standard edition is supposed to deal with
the
> > multiple small shops scenario perhaps the snapshot functionality only is
> > available.
> >
> >
> > --
> > Niall Litchfield
> > Oracle DBA
> > Audit Commission UK
> > *****************************************
> > Please include version and platform
> > and SQL where applicable
> > It makes life easier and increases the
> > likelihood of a good answer
> >
> > ******************************************
> > "Nuno Souto" <nsouto_at_optushome.com.au.nospam> wrote in message
> > news:3c10adfb.1159024_at_news...
> > > Niall Litchfield doodled thusly:
> > >
> > > >However I get the following output from sqlplus suggesting that in
fact
> > > >materialised views are available?
> > > >
> > > >SQL> create materialized view nl_people
> > > > 2 as select * from v_people@<remotedb>
> > > > 3 ;
> > > >
> > > >Materialized view created.
> > > >
> > > >SQL> select * from v$version;
> > > >
> > > >BANNER
> > > >-------------------------------------------
> > > >Oracle8i Release 8.1.7.2.1 - Production
> > > >PL/SQL Release 8.1.7.2.0 - Production
> > > >CORE 8.1.7.2.1 Production
> > > >TNS for 32-bit Windows: Version 8.1.7.2.0 -
> > > >NLSRTL Version 3.4.1.0.0 - Production
> > > >
> > >
> > >
> > > What does v$option contain?
> > >
> > >
> > > Cheers
> > > Nuno Souto
> > > nsouto_at_optushome.com.au.nospam
> >
> >
>
>
Received on Mon Dec 10 2001 - 03:38:35 CST
