1.1

TablesEntity group
Order Details
Product
Product
Suppliers
Category
Step 3: Define the partitioning key for each group
In this example, the partitioning keys are:
Partitioning keyEntity group
CustomerIDCustomer
ProductIDProduct
This example uses customerID as the partitioning key for the Customer group. The customer row and all associated
orders will be collocated into a single partition. To explicitly colocate Orders with its parent customer row, use
the colocate with clause in the create table statement:
create table orders (<column definitions, constraints>)
partition by (customerID)
colocate with (customers);
Create the OrderDetails table in a similar fashion. In this way, SQLFire supports any queries that join any of
Customer, Orders, and OrderDetails. This join query would be distributed to all partitions and executed in parallel,
with the results streamed back to the client:
select * from customer c , orders o where c.customerID = o.customerID;
A query such as this would be pruned to the single partition that stores "customer100" and executed only on that
SQLFire member:
select * from customer c, orders o where c.customerID = o.customerID
and c.customerID = 'customer100';
The optimization provided when queries are highly selective comes from engaging the query processor and
indexing on a single member rather than on all partitions. With all customer data managed in memory, query
response times are very fast. Consider how the above query would execute if the primary key was not used to
partition the table. In this case, the query would be routed to each partition member where an index lookup would
be performed, even though only a single member might have any data associated with the query.
Finally, consider a case where an application needs to access customer order data for several customers:
select * from customer c, orders o
where c.customerID = o.customerID and c.customerID IN ('cust1', 'cust2',
'cust3');
Here, SQLFire prunes the query execution to only those partitions that host 'cust1', 'cust2', and 'cust3'. The union
of the results is then returned to the caller.
Step 4: Identify replicated tables
If we assume that the number of categories and suppliers rarely changes, those tables can be replicated in the
SQLFire cluster (replicated to all of the SQLFire members that host the entity group). If we assume that the
Products table does change often and can be relatively large in size, then partitioning is a better strategy for that
table.
53
Designing vFabric SQLFire Databases