1.1

create table trade.sellorders (oid int not null constraint orders_pk primary
key, cid int, sid int, qty int, ask decimal (30, 20),
status varchar(10) default 'open', tid int, constraint portf_fk foreign
key (cid, sid) references trade.portfolio (cid, sid)) replicate
create table trade.networth (cid int not null, cash decimal (30, 20),
securities decimal (30, 20), loanlimit int, availloan decimal (30, 20),
tid int, constraint netw_pk primary key (cid), constraint cust_newt_fk
foreign key (cid) references trade.customers (cid) on delete restrict )
partition by column (cid) colocate with(trade.customers)
create table trade.buyorders(oid int not null constraint buyorders_pk primary
key, cid int, sid int, qty int, bid decimal (30, 20), status varchar(10),
tid int, constraint bo_sec_fk foreign key (sid) references
trade.securities (sec_id) on delete restrict) partition by column (cid)
In the above system, securities and sellorders are replicated tables, while customers, portfolio,networth and
buyorders are partitioned tables. The portfolio and networth tables are colocated with customers, partitioned on
cid column. The buyorders table is not colocated with any of the partitioned tables.
All tables are assigned to the default server group.
Query of a single partitioned table
Simple queries that involve only one partitioned table (and possibly multiple replicated tables) work without
any limitations. For example each of the following queries is supported:
select * from trade.portfolio, trade.securities, trade.sellorders
select * from trade.securities s, trade.portfolio f where sec_id = f.sid
and f.tid = ?
select * from trade.securities s, trade.portfolio f, trade.sellorders so
where s.sec_id = f.sid and so.sid = s.sec_id and f.tid = ?
About the colocation requirement for querying multiple, partitioned tables
Queries that involve two or more partitioned tables (with or without additional, replicated tables) are supported
only if they satisfy both of these colocation criteria:
1.
The query's WHERE clause has equijoin conditions on all of the partitioning columns for all of the partitioned
tables in the query.
2. All partitioned tables in the query are colocated.
These criteria allow SQLFire to distribute a join query to all of the data stores that host the data. The query is
executed concurrently on each local data store without having to move table data from one member to another
to perform the join. Joins are performed on the local data set of each data store, and the main query member
aggregates the results from each data store to obtain the result set.
When determining colocation, note that if a table B is colocated with table A, and Table C is colocated with
Table A, then tables B and C are also colocated. Similarly, if a table C is colocated with table B, and B is colocated
with table A, then tables C and A are also colocated. For example, consider the following query:
select * from trade.customers c, trade.securities s, trade.portfolio f where
c.cid = f.cid and sec_id = f.sid and f.tid = ?
The query above has an equijoin condition c.cid = f.cid, which associates the portfolio and
customers table on the partitioning column, cid. The two tables are colocated on cid, so SQLFire can
execute this query. The fact that the table securities is replicated does not impose any requirement for the
equijoin criteria.
vFabric SQLFire User's Guide692
vFabric SQLFire Reference