Using SharePlex Efficiently – Only Replicate the Data You Need

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.

Wildcard replication

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

Horizontal (row) Partitioning

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:

COLUMN

DATATYPE

Contents

PARTITION_SCHEME

VARCHAR2(30)

The name of the partition scheme

DESCRIPTION

VARCHAR2(61)

Description (documentation only)

TARGET_TABLE_OWNER

VARCHAR2(30)

The owner of the target table

TARGET_TABLE_NAME

VARCHAR2(30)

The name of the target table

ROUTE

VARCHAR2(1024)

For partitioning by a column value, Standard routing information; for hash partitioning , use a “#” in place of the queue name

PRIORITY

NUMBER

NULL, Reserved for SharePlex

ORDER

NUMBER

NULL, Reserved for SharePlex

OPTIONS

VARCHAR2(32)

For partitioning by a column value, NULL; for hash partitioning, “HASH#”, where # is the number of partitions you want to create

COL_CONDITIONS

VARCHAR2(1024)

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.

Vertical (Column) Partitioning

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.

Anonymous