One of the more attractive features of the SharePlex data replication tool is its ability to be very selective about the data it replicates. If you’re looking at workload offloading, making data more available to your users, building data marts or data warehouses, SharePlex has a number of features to allow you to select only the data you need, and route it to the right places. Selectively replicating data can cut down on the resources required to maintain and transport that data, and allow more efficient use of those resources.
This blog will discuss some of the ways you can use SharePlex to select or partition the data you replicate.
At the highest level, SharePlex operates on schemas or objects owned by specific Oracle users. If we use the EXPAND directive, we can replicate an entire schema with one line in a configuration file, as on this example:
EXPAND sourceschema.% targetschema.% routing_map
This tells SharePlex to replicate every change to every table in the sourceschema. You can then use the “NOT” keyword to exclude certain tables. For example, this entry would replicate all tables except those with “test” in the name.
EXPAND sourceschema.% (NOT %test%) targetschema.% routing_map
Wildcards are great for replicating an entire schema, and of course, we can replicate individual tables in their entirety. SharePlex also gives us the capability to replicate only selected rows, or to divide the rows across multiple queues, which we call horizontal partitioning.
To partition replication horizontally, we specify either a standard WHERE clause, or a hash value. We specify the selection criteria in what’s called a partition scheme. Partition schemes are stored in the <shareplex shchema owner>.SHAREPLEX_PARTTION table. Here’s a definition of that table:
The name of the partition scheme
Description (documentation only)
The owner of the target table
The name of the target table
For partitioning by a column value, Standard routing information; for hash partitioning , use a “#” in place of the queue name
NULL, Reserved for SharePlex
For partitioning by a column value, NULL; for hash partitioning, “HASH#”, where # is the number of partitions you want to create
For partitioning by a column value, a standard condition, such as (region_id = ‘WEST’); for a hash partitioning , the word “ROWID”
There are a few caveats to defining partition schemes; for example, if the column used in condition changes, an out-of-sync condition can occur. See the SharePlex Administrators Guide for complete information.
Once we have the partition scheme defined, we reference it in the configuration file as follows:
sourceowner.table targetowner.table !partition_scheme ! routing_map
Horizontal partitioning is a great way to either route separate rows to different targets, or to only replicate certain rows.
Sometimes, rather than replicating an entire row, you might want to replicate only selected columns out of the row. You can also use this to prevent replication of secure data, such as account or identification numbers. Unlike horizontal partitioning, which requires a table in the source database, vertical partitioning is configured using the configuration file. We have two options, we can either include a list of columns, or exclude a list of columns. Here’s an inclusion example:
sourceschema.table (col1, col2, col5) targetschema.table routing_map
In this example, col1, col2 and col5 are the names of the columns we want replicated, and we assume that the target table contains columns of the same name. Here’s how we would specify this if the column names in the source and target were different:
sourceschema.table (col1, col2, col5) targetschema.table (col4, col6, col7) routing_map
In both of these cases, only the columns specified would be replicated.
Here’s an example of exclusion:
sourceschema.table !(col1, col2, col5) targetschema.table routing_map
Here, the ! tells SharePlex to replicate all columns EXCEPT those listed.
Again, there are a few caveats that are covered in the SharePlex Administration Guide.
The SharePlex Administration Guide, can be found with all of the other SharePlex documentation. Ready to test it out for yourself, download a free 30 day trial of SharePlex.