The use of Oracle database snapshots

The ORACLE tutorial you are looking at is the use of Oracle database snapshots. A snapshot of the oracle database is a table that contains the results of a query against one or more tables or views on a local or remote database. Because the snapshot is a subset of the query of a main table, using the snapshot can speed up the query of the data. When keeping two tables in different databases in sync, the performance of data updates can also be greatly improved by using snapshot refreshes.

The following is an example of how to use snapshot to speed up the query in the call call system of xiangfan telecom bureau 170.

The calling fee collection system is a system that plays the calling fee reminder sound to the user's phone. The amount of user's overdue fees is stored in the yh_qfcx table under sffw of rs6000 minicphone (yh_qfcx table is a record table of overdue fees that changes dynamically with the user's payment situation), while the data of the collection system is stored in another xf170 server dmtcx user as required, so as to use part of the data in yh_qfcx table under sffw user under dmtcx user. I set up S_yh_qfcx snapshot of yh_qfcx under dmtcx users to speed up the query.

The specific steps are as follows:

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

Only when the snapshot log of the table yh_qfcx is established can a quick refresh be performed in the snapshot.

Create snapshot log on yh_qfcx;

2. Establish a database link to sffw users under dmtcx users;

Data can only be retrieved from the table yh_qfcx under the sffw user after a database link to the sffw user has been established.

Create database link link_sf

Connect to sffw identified by xxxxxxx using 'rs6000';

3. Create a snapshot under dmtcx user s_yh_qfcx;

Create snapshot s_yh_qfcx as

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

From yh_qfcx @ link_sf

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

4. Modify the snapshot refresh interval as required;

Snapshots under the dmtcx user s_yh_qfcx need to be constantly refreshed in order to keep up with the main table yh_qfcx under the sffw user. oracle will automatically refresh the snapshot only if the refresh interval is set.

There are two ways to refresh a snapshot: fast refresh and full refresh. The snapshot log exists before the main table that needs snapshot is quickly refreshed. When fully refreshed, oracle performs a snapshot query to put the results into the snapshot. A quick refresh is faster than a full refresh, because a quick refresh sends less data from the primary database over the network to the snapshot, so only the modified data from the main table needs to be transmitted, while a full refresh delivers all the results of the snapshot query.

Alter snapshot s_yh_qfcx refresh fast

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

{this SQL statement means: set oracle automatically in 1 minute

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

(10/24*60) fast refresh once. }

Alter snapshot s_yh_qfcx refresh complete

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

{this SQL statement means: set oracle automatically at 30

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

It will be completely refreshed once every other day. }

Description:

1. Since the snapshot refresh is done automatically by the server, make sure that the oracle database starts the snapshot refresh process. To see if the oracle database has started the snapshot refresh process, you can view the V_$SYSTEM_PARAMETER parameter snapshot_refresh_processes as database sys to see if the value of snapshot_refresh_processes is 1. If it is not 1, the snapshot refresh process is not started.

2. The method to start the snapshot refresh process is: modify the initialization file initorcl.ora of oracle database, change the value of snapshot_refresh_processes parameter from 0 to 1, and then restart the oracle data.

3. It should be noted that when we set up the snapshot log, oracle database set up a trigger tlog$_yh_qfcx and the snapshot log table mlog$_yh_qfcx for us based on yh_qfcx. When the snapshot was established, oracle database set up 1 table, 2 views and 1 index for us, which are:

Table 1: snap $_s_yh_qfcx;

Two views :mview$_s_yh_qfcx and s_yh_qfcx;

1 index :I_snap$_s_yh_qfcx(

Based on the snap$_s_yh_qfcx row $$field in the table snap$_s_yh_qfcx.