RE: Snapshot Logs Explanation Needed

OK, checked TFM and there are two type of fast refresh snapshots: primary key (new in 8)
and rowid.

For a rowid snapshot, when the table (EMP) is updated, a row is added to the snapshot log (MLOG$_EMP) containing mainly the type of action, timestamping info, and the rowid (MLOG$_EMP.M_ROW$$) of the affected record (rowid x deleted at 2pm).

The snapshot object (type UNDEFINED in dba_objects) that lies underneath the table seen by the end users is akin to a table (it has an index) and has an extra column (also called M_ROW$$) that contains the rowid from the master table (rowid y is a copy of master rowid x). During a refresh, the changes from the snapshot log are applied to records in the shapshot with the appropriate *master* rowid (DELETE FROM snap.CAREGIVERS WHERE M_ROW$$ = x).

So if you drop the table and recreate it without a full refresh, the M_ROW$$ values in the snapshot object are now invalid because they point to the emp.rowid before the reorg.

For a primary key snapshot, there is no M_ROW$$ column. Instead, the primary key columns are placed in the snapshot log and used to identify the rows in the snapshot. Because rowids are not involved, a primary key snapshot CAN be fast refreshed (according to TFM) after a drop an recreate. However, there are some procedures that need to be called to let the system know you are doing a reorg so that it doesn't flip out when the table goes away.

Clear as mud?

HELP, AND YES I AM AN IDIOT. :) Brian Norrell
Manager, MPI Development
QuadraMed
511 E John Carpenter Frwy, Su 500
Irving, TX 75062
(972) 831-6600

-----Original Message-----
Sent: Thursday, May 31, 2001 2:24 PM
To: Multiple recipients of list ORACLE-L

If you do a snapshot refresh then the snapshot log should be empty(correct?).
Then I would think you could reorg the master table as long as you prohibit users from updating the master until the reorg and the recreating of the log is
complete. Thus eliminating the need to do a complete refresh of the snapshot.

---------------------- Forwarded by Tracy Rahmlow on 05/31/2001 12:29 PM
---------------------------

      PM PST

Please respond to ORACLE-L_at_fatcity.com

Sent by: root_at_fatcity.com

To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> cc:

Been a while since I worked with snapshots, but if I remember correctly, each record in a fast refresh snapshot keeps a mapping back to the rowid on the master. If you drop and recreate the master table, the mapping is hosed and updates/deletes do not propagate to the snapshot.

Brian Norrell
Manager, MPI Development
QuadraMed
511 E John Carpenter Frwy, Su 500
Irving, TX 75062
(972) 831-6600

-----Original Message-----
Sent: Wednesday, May 30, 2001 5:48 PM
To: Multiple recipients of list ORACLE-L

I would like to reorg many tables which have snapshots associated with them. I
understand that if the master table is dropped the snapshot log is also dropped. When a log is dropped, oracle states that you need to do a complete
refresh of the affected snapshot. My question is why? If you do not allow users to access the database with the master table, then you should not have any transactions that would be lost. Why can't you create a new log and continue to do a fast refresh? I am trying to avoid having to recreate all the snapshots due to the size and number that we have. Any thoughts? Thanks

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tracy Rahmlow
  INET: Tracy.Rahmlow_at_aexp.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Norrell, Brian
  INET: BNorrell_at_QuadraMed.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tracy Rahmlow
  INET: Tracy.Rahmlow_at_aexp.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Norrell, Brian
  INET: BNorrell_at_QuadraMed.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).