This post will guide you how to use the powerful object of Distributed Partition View (aka DPV). DPV can solve many issues dealing with multiple tables management.
After understanding the steps to creating the DPV we will introduce you with a simple, hassle free alternative, stay tuned…
Distributed Partition View
Distribute Partition View is a unique database object. It gives the ability to partition your tables by giving a smart view layer on several tables instead of splitting the table into partitions.
The smart view knows how to optimize access to the designated tables, unlike a regular view that sorts all the data into memory (with minor query rewrites). DPV allows you to define different indexes to each table and the optimizer knows how to perfectly use those indexes. It is also proven that DPV works far better than table partitioning on large data queries on a single partition.
Here we have, simple example of partition view:
In this example two tables are created Test1 and Test2. A view, TestView is created, pointing to records from both tables. This configuration allows to insert records into the tables, via TestView.
This works well, but what if we need to use identity, time stamp etc. Let’s try adding an identity column to the tables:
insert into TestView (name,partKey) values ('Test1',1);
insert into TestView (name,partKey) values ('Test2',2);
select * from TestView
update TestView set name = 'test1 new name' where id = 1 and partKey = 1
select * from TestView
Actually, using trigger makes all the atomic operations (insert, update and delete) feasible. But if we need to work on multiples servers, authentication problems arise. Dealing with authentication problems is a complex task.
How can PartitionDB help?
PartitionDB SDK provides you with a smart partitioning mechanism, that will take care of everything for you. In order to run the example below you need to download PdbLogic and attach it to your database, and there you go.
For Example, to achieve partition view abilities, you just need to configure your wishes in a simple way as below (PdbLogic should be downloaded and attached before running this example):
PdbsetDistributedObject adds tables to a view. In our example, it adds Test1 table and Test2 table to TestView. More tables can be added. Once it is done, you can add new records to the view, or update existing records. You don’t need to create triggers, or worry about identities.
For your reference, here is a list of optional configuration parameters:
|TargetDatabaseName||Name of the target database that contain the table|
|TargetLinkedServerName||Name of the linked server of the table (database)|
|TargetSchemaName||Schema name of the table|
|SourceObjectName||Name of the table that will be part of the partition view|
|DistributedDatabaseName||Database name that contains the view|
|DistributedLinkedServerName||Linked server name that contains the view (database)|
|DistributedSchemaName||Schema name of the view|
|DistributedColumnName||The column which will be the partition column|
|DistributedStringValue||The string value of the partition (in case of range it will be the minimun value)|
|DistributedNumberValue||The value (tinyint, smallint, int, bigint) of the partition (in case of range it will be the minimum value)|
|DistributedDecimalValue||The value (decimal, flaut, real, numeric) of the partition (in case of range it will be the minimum value)|
|DistributedDateValue||The value (date, smalldatetime, datetime) of the partition (in case of range it will be the minimum value)|
|DistributedRangeStringValue||The maximum string value of the partition (in case of range it will be the minimum value)|
|DistributedRangeNumberValue||The maximum value (tinyint, smallint, int, bigint) of the partition (in case of range it will be the minimum value)|
|DistributedRangeDecimalValue||The maximum value (decimal, flaut, real, numeric) of the partition (in case of range it will be the minimum value)|
|DistributedRangeDateValue||The maximum value (date, smalldatetime, datetime) of the partition (in case of it will be the minimum value)|
|IsOutputScript||Decide if print the command or run it|