Materialized View and timestamps /DMS-34049

jaspernygaard

SCO include state info of the instance of MV into its metadata in the form of the current refresh time (in relation to log files). That is very inconvenient

 

For MV present and identical on source and dest DB SCO will generate a deployment script as 

 

--
-- Script generated by Schema Compare for Oracle 4.0.11.536 on 26/09/2018 12:30:42
--
SET DEFINE OFF
DROP MATERIALIZED VIEW h6601.mv_fysrap_roff;
CREATE MATERIALIZED VIEW h6601.mv_fysrap_roff
ON PREBUILT TABLE WITH REDUCED PRECISION
REFRESH START WITH TO_DATE('2018-9-25 8:35:56', 'yyyy-mm-dd hh24:mi:ss') NEXT SYSDATE+30/(24*60*60) 
AS SELECT id,
 rapportnavn,
 format,
 logonidfast,
 bes_id,
 status,
 synlighed,
 logonid,
 ajourtid,
 behandler_id2,
 dyr_id,
 driftsenh_id
 FROM fysiskrapport
 WHERE frekvenstype IN (40, 41) AND synlighed <> 0;

 

There are several problems around this:

1. The MV exists and is identically in definition on source and destination database (SCO has another problem around that, but that is a separate issue) and there is no need to recreate it. It can be quite resource demanding to recalc a MV.
2. Dependent privileges (outside the deployment set of schemas) will get lost by this. (if there where genuine differences this would happen anyway)
3. The metadata for this specifies using prebuilt table and refresh as of a timestamp. This is extracted from a source database and is the current state of the MV on the source, but the timestamp and the prebuild table refers to data on the source system and data is not part of the deployment only metadata. Especially this data as that is the log data for MV log it is relevant for. The corresponding data on the destination system will be used but that reflects the state on the destination system of the MV especially the existing (prebuild) table and the log table will be out of sync as of another time than at the source DB.

Using dbms_metadata 

select dbms_metadata.get_ddl('MATERIALIZED_VIEW', 'MV_FYSRAP_BEH', 'H6601') from dual

gives

 

CREATE MATERIALIZED VIEW "H6601"."MV_FYSRAP_BEH" ("ID", "RAPPORTNAVN", "FORMAT", "LOGONIDFAST", "BES_ID", "STATUS", "SYNLIGHED", "LOGONID", "AJOURTID", "BEHANDLER_ID2", "DYR_ID", "DRIFTSENH_ID")
 ON PREBUILT TABLE WITH REDUCED PRECISION
 USING INDEX 
 REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT SYSDATE+30/(24*60*60)
 WITH PRIMARY KEY USING DEFAULT LOCAL ROLLBACK SEGMENT
 USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
 AS select id, 
 rapportnavn, 
 format, 
 logonidfast, 
 bes_id, 
 status, 
 synlighed, 
 logonid, 
 ajourtid, 
 behandler_id2, 
 dyr_id,
 driftsenh_id
from fysiskrapport
where behandler_id2 is not null and synlighed <> 0
 

Which avoids the specific timestamp which is only useable if extracted from the same instantation of a MV which is being recreated.

This also is static over time where SCO will potentially extract different metadata every time it is comparing at is embed the timestamp of the last refresh from the state of the current instance of the MV.

Is it possible to avoid the timestamp?

Eddie D

Hi, thank you for your forum post.

There is no feature within Schema Compare for Oracle (SCO) to Ignore the timestamp.  Therefore I have raised an enhancement request for SCO to be able to ignore timestamp.

I cannot guarantee that the enhancement request will be successful. Or if approval is given what future version of SCO it will appear in.  The reference for this feature request is OC-1012.

Many Thanks
Eddie

Eddie D

Hi,

Just had another thought does the option to Ignore Materialized View START WITH value.  Does enabling this option help you?

Many Thanks

Eddie

andersharder

I have tried doing the extract and comparison with 'ignore: materialized view START WITH value' It still embeds the start with clause in the comparison and in the created deployment script.
As the deployment script does't deploy data (nor should it) in the underlying table or the mview log table that is used to make the incremental refresh with the time for  the 'start with' is meaningless to transfer or compare. 
The deployment script will try to recreate the mview, but could fail or cause inconsistent data as the log table might not contain data since the 'start with' time (which comes from a different instantation of the mview on a different database) nor will the 'prebuilt table' contain data as of the 'start with' time.

andersharder

Regarding the 'ignore: materialized view START WITH value' option. The option does exclude the start with clause from comparison. 
I cannot foresee any situation where you would not want to ignore the start with clause when it is phrased like an absolut time.
The problem is around the start with timestamp. When there is a genuine difference in the MVIEW then the deployment wizard takes the timestamp clause with absolut time from the source (LHS) database and uses that on the destination database. That does not make sense as it refers to validity of data in a potential prebuilt table and a mview log table which are on the destination database. 

andersharder

Hi Redgate,
Would you care to comment on whether you think using a '...on prebuilt table ...start with timestamp' for a MV extracted at one point in time from one database make sense to deploy to or just compare to another database (or the same at a different point in time) 
For such a deployment to work the data in the 'prebuilt table' and data in the logtable should first be deployed (which redgate does not do).
Thanks, Anders

jaspernygaard

For reference, ticket created (125725), but resolved by using a deployment user with correct permissions.