Use of Oracle Database snapshots

The ORACLE tutorial is: Use of Oracle Database snapshots. An oracle Database snapshot is a table that contains query results for one or more tables or views on a local or remote database. Because snapshots are a subset of a master table, using snapshots can speed up data query. While synchronizing two tables in different databases, using snapshots to refresh, the data update performance will also be greatly improved.

The following describes how to use a snapshot to accelerate the query speed in the 170 call reminder system of Xiangfan Telecom Bureau:

170 the call reminder system is a system that calls a user to display the call reminder. The user's arrears amount is stored in the yh_1_cx table of the rs6000 minicomputer sffw user (the yh_1_cx table is a arrears record table dynamically changed with the user's payment situation ), the data of the reminder system is stored in the dmtcx user of another xf170 server as required. For some data in the yh_qfcx table below the sffw User table for dmtcx users, I created the yh_qfcx snapshot S_yh_qfcx under the dmtcx user to speed up the query.

　　The procedure is as follows:

1. Create a snapshot log for the table yh_qfcx under the sffw user;

You can perform a quick refresh in a snapshot only when you create a snapshot log for the table yh_qfcx.

Create snapshot log on yh_qfcx;

2. Create a database chain link_sf for the sffw user under the dmtcx user;

After the database chain of the sffw user is established, data can be obtained from the table yh_qfcx under the sffw user.

Create database link link_sf

Connect to sffw identified by xxxxxxx using 'rs6000 ';

3. Create a snapshot s_yh_qfcx under the dmtcx user;

Create snapshot s_yh_policcx

Select yhh, qf6 + qf5 + qf4 + qf3 + qf2 + qf1 + qf rjje

From yh_qfcx @ link_sf

Where tjbz = 'K' and bz6 + bz5 + bz4 + bz3 + bz2 + bz1 + bz> 0;

4. Modify the interval of snapshot refresh as needed;

To synchronize the snapshot s_yh_qfcx with the master table yh_qfcx of the sffw user, you need to refresh the snapshot continuously. Oracle will automatically refresh the snapshot only when the interval for refreshing the snapshot is set.

There are two ways to refresh a snapshot: Quick refresh and full refresh. A snapshot log exists in the master table that requires a snapshot for quick refresh. When a snapshot is completely refreshed, oracle executes a snapshot query and places the result to the snapshot. Quick refresh is faster than full refresh, because Quick refresh only needs to transmit the modified data in the master table because there is less data sent from the master database to the snapshot through the network, and completely refresh all the results of the snapshot query to be sent.

Alter snapshot s_yh_qfcx refresh fast

Start with sysdate + 1/1440 next sysdate + 1/144;

{This SQL statement indicates that oracle is automatically set to 1 minute.

(1/24*60) after the first quick refresh, after every 10 minutes

(10/24*60) Refresh quickly .}

Alter snapshot s_yh_qfcx refresh complete

Start with sysdate + 1/2880 next sysdate + 1;

{The meaning of this SQL statement is: Set oracle to automatically pay at 30

(30/24*60*60) after the first full refresh,

It will be refreshed every other day .}

　　Note:

1. Because snapshot refresh is automatically completed by the server, ensure that the snapshot refresh process is started for the oracle database. Check whether the snapshot refresh process has been started in the oracle database. You can view the value of snapshot_refresh_processes in view V _ $ SYSTEM_PARAMETER as the Database sys. If it is not 1, the Snapshot refresh process is not started.

2. Start the snapshot refresh process by modifying the initorcl. ora file of the oracle database initialization file, changing the value of the snapshot_refresh_processes parameter from 0 to 1, and then restarting oracle data.

3. It should be noted that the oracle database created a trigger tlog $ _ yh_qfcx and snapshot log table mlog $ _ yh_qfcx for us when creating snapshot logs; when a snapshot is created, the oracle database creates a table, two views, and one index for us. They are:

One table: snap $ _ s_yh_rjcx;

Two views: mview $ _ s_yh_qfcx and s_yh_qfcx;

One index: I _snap $ _ s_yh_rjcx (

Based on the m_row $ field in the table snap $ _ s_yh_qfcx.