May the Shard be with You!

Oh how I remember when the first Star Wars movie came out, all the action, adventure and of course the lightsaber. Who didn’t want a lightsaber? You could slice your way through anything with it. Well I never did get one, but what I did get was SharePlex and that’s just as good.

Using SharePlex’s ability to slice data at the row level or the column level is just as exciting to me. This ability is called partitioning and this blog will show horizontal partitioning in action.

With horizontal partitioning you can divide the rows of a table into separate processing streams. Replicate a subset of rows to the target, while retaining the rest of the rows in the source. Replicate different subsets of rows to different targets. Divide the replication of a source table into parallel Post processes for faster posting to the target table.

Horizontal partitioning is just like using a lightsaber to slice your data up and then replicate it out to different targets. Sharding at a basic level! How cool is that!

Our example today will have an Oracle source table called mike.shard with the columns and data types you see below


Our target tables are all in three different PostgreSQL databases and have the same table name and structure but different owner as is shown below


To setup horizontal partitioning you need to do a few basic steps first.

1. Define your partition schemes

A partition scheme is the element that is used in the configuration file to direct SharePlex to use horizontal partitioning. Using a column condition, the partition scheme defines a subset of rows. In our example we will have three partition schemes (shard1, shard2, shard3), each one identifies a subset of rows using a condition on the LNAME column.. This can be seen in the COL_CONDITIONS column of the shareplex_partition table below  


2. Specify partition schemes in the configuration file

To direct SharePlex to use a specific partition scheme for a source table, you specify the partition scheme as the routing map in the configuration file. SharePlex obeys the specifications of the partition scheme to process the row subsets. You can see in our configuration file below how this is setup


Once those steps are completed, everything else works the same, just activate your config file and off you go. You now have your own SharePlex light sabre and are slicing your data like a true Jedi Knight or in this case SharePlex Knight.

This video shows all this in action. Put on the Star Wars theme music, sit back and enjoy.

For more information, please visit SharePlex...and of course “May the Shard be with You”

About the Author
With over 15 years as an Oracle DBA working for multi-national Financial institutions, I know the importance of replication and reliability. As the requirements for more and more data grow and with more...