Open In App

SQL Snapshots

Improve
Improve
Like Article
Like
Save
Share
Report

Snapshot is a recent copy of the table from the database or a subset of rows/columns of a table. The SQL statement that creates and subsequently maintains a snapshot normally reads data from the database residing server. A snapshot is created on the destination system with the create snapshot SQL command. The remote table is immediately defined and populated from the master table.

These are used to dynamically replicate data between distributed databases. Two types of snapshots are available.

  1. Simple snapshots
  2. Complex snapshots

Simple snapshot :
In simple snapshot, each row is based on a single row in a single remote table. This consists of either a single table or a simple SELECT of rows from a single table.

Example –

CREATE SNAPSHOT emp_snap 
as select * from emp;

Complex snapshot :
In complex snapshot, a row may be based on more than one row in a remote table via GROUP BY operation or result of Multi-Table Join. This consists of joined tables, views, or grouped and complex SELECT statement queries.

Example –

CREATE SNAPSHOT sampleSnps1 
AS SELECT student.rollno, student.name 
FROM student
UNION ALL
SELECT new_student.rollno, new_student.name 
FROM new_student;

Advantages :

  • Response time is improved when local read-only copy of table exists.
  • Once snapshot is built on remote database, if node containing data from which the snapshot is built is not available. Snapshot can be used without need to access the unavailable database.
  • Ease network loads.
  • Data subsetting.
  • Disconnected computing.
  • Mass deployment.

Disadvantages :

  • Snapshots are not reachable when primary database goes offline.
  • It does not support full text indexing.
  • Snapshot runs out of disk if data changes frequently faster.
  • As no.of snapshots increases, disk space becomes problematic.

Applications :

  • Protects data.
  • Maintains history of data.
  • Used in testing application software.
  • Used in data mining.
  • Recovers data when information is lost because of human error or corruption of data.

Last Updated : 23 Oct, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads