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.
Simple snapshot :
- Simple snapshots
- Complex snapshots
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.
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.
CREATE SNAPSHOT sampleSnps1
AS SELECT student.rollno, student.name
SELECT new_student.rollno, new_student.name
- 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.
- 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.
- 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.
Share your thoughts in the comments
Please Login to comment...