Implementing Replica Tables

We've learned how you can choose between virtualization and replication for your data. But what if you decide to begin with virtualization but later you might want to switch to replication because of poor performance?

At first, creating a report on top of a virtual table might seem a good option because this approach supports a quick implementation and easy maintenance.

But in some situations, replicating the remote data to the local SAP HANA system might offer better query performance than accessing the data in a remote table. So, you might consider abandoning the virtualization approach and reimplementing a replication approach.

But the problem with switching the data provisioning approach is that you'd need toadjust all applications to point to the new target table where the replicated data is loaded. This would generate transport and testing tasks that you might not want to be burdened with.

The solution for this scenario is to implement replica tables. This is a feature of data replication.

A replica table sits alongside a virtual table and has the same structure. The replica table captures and stores the data in real-time.

The key feature of implementing a replica table is a toggle that allows you to easily switch between the virtual table and the replica table. This means that you can switch to the replica table if performance begins to degrade and switch back when performance improves.

When to Toggle?

You can easily identify problematic remote queries and cases where it might be beneficial to toggle using two monitoring views:

M_EXPENSIVE_STATEMENTS
M_REMOTE_STATEMENTS

When the execution time of a SQL statement that's run on a virtual table exceeds a certain threshold, it’s included in the M_EXPENSIVE_STATEMENTS monitoring view.

The common fields in the M_EXPENSIVE_STATEMENTS monitoring view and the M_REMOTE_STATEMENTS monitoring view can be used to find the remote queries of slow SQL statements and so the virtual tables that would benefit from a toggle to a replica.

Replica Table Definition

To toggle between replicated data and the virtual table, the virtual table must have a corresponding replica table added to it.

Here are some characteristics of replica tables:

Each virtual table can only have one replica table.
A replica table is updated in real time when the source table is updated.
Real-time replication is only supported for remote sources that support real-time change data capture (CDC).
Otherwise, you could load data into the table from time to time. Your replica would then be a snapshot replica.
Replica tables are automatically generated and stored in an internal schema, managed by an internal user. They're hidden behind the scenes.

Note

There’s a feature called User-owned replica tables that would allow changes to the replica table structure (for example, to add partitioning to improve query performance of the replicate table).

This type of replica table is created in a user schema, so it can be modified.

Toggle to Replica and Toggle Back to Virtual Table

To add a replica to a virtual table, execute the following statement:
Code Snippet
Copy code
Switch to dark mode
```
12
ALTER VIRTUAL TABLE <virtual_table_name> 
ADD SHARED [SNAPSHOT] REPLICA;
```
To directly access the virtual table again, you must either:
- For a specific query, use a HINT to override the reading mode so that you use the virtual table and not the replica.
  Code Snippet
  Copy code
  Switch to dark mode
```
12
SELECT * FROM <virtual_table_name> 
WITH HINT(NO_VIRTUAL_TABLE_REPLICA);
```
  or use a SET statement before you execute the query.
  Code Snippet
  Copy code
  Switch to dark mode
```
12
SET 'VIRTUAL_TABLE_REPLICA' = 'FALSE'; 
SELECT * FROM <virtual_table_name>;
```
- Temporarily disable the replica using an ALTER statement.
  Code Snippet
  Copy code
  Switch to dark mode
```
1
ALTER VIRTUAL TABLE <virtual_table_name> DISABLE REPLICA;
```
  It can then be enabled again later. (Replace DISABLE with ENABLE).
- Delete the replica using a DROP command.
  Code Snippet
  Copy code
  Switch to dark mode
```
12
ALTER VIRTUAL TABLE <virtual_table_name> 
DROP REPLICA;
```

You’ve reached the end of this unit and have learned about the different approaches to replicating data.