Define Distributed partitioned views.
For performance gains, one might need to distribute database work over multiple servers. This is achieved by horizontally partitioning large tables over multiple servers. I.e. splitting a table with many rows into many tables with few rows. For data that is spread across multiple servers, different types of statements are needed for retrieving data. I.e. distributed partitioned views. These statements use standard SQL statements along with UNION to retrieve data from multiple servers. One needs to define a distributed partitioned view on each of these servers, and each view having the same name. This allows the queries to run on all these servers instead of one.
Define Distributed partitioned views. Partitioned view:
Partitioned view joins the horizontally portioned data. This data may belong to one ore more servers. It makes the data appear as one table. A partioned view can either be local or distributed. Distributed may reside on a different server. The original table is replaced with several smaller member tables. Each member table is on a separate member server.Syntax:
The view is then created by UNIONing all the tables and an updateable partitioned View resultsServer 1 :
CREATE TABLE Customer1
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 1 AND 32999),
... -- Additional column definitions)
Similar tables created for Server 2 and 3
Partitioned view for server 1
CREATE VIEW Customers AS
SELECT * FROM CompanyDatabase.TableOwner.Customer1
SELECT * FROM Server2.CompanyDatabase.TableOwner.Customer2
SELECT * FROM Server3.CompanyDatabase.TableOwner.Customer3