1.0

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.
The following example shows an example query that meets the equijoin requirements:
select * from trade.customers c, trade.networth n, trade.portfolio p where
n.cid = c.cid and n.cid = p.cid and n.tid = ? and c.cid >?
The query above involves three partitioned tables. Because the networth table is colocated with customers,
and portfolio is colocated with customers, the three tables are mutually colocated. The partitioning
column of each table forms an equi-join condition with the other. The presence of two equi-join conditions
n.cid = c.cid and n.cid = p.cid satisfies the colocation criteria, so SQLFire can execute this query.
Keep in mind that both colocation criteria must be met. Even if the partitioned tables in a query are colocated,
SQLFire does not support queries where the join condition is applied to non-partitioning columns. Queries of
this type satisfy only one but not both of the criteria. For example, even though both the customers and
portfolio tables are colocated, SQLFire does not support this query because tid is not a partitioning column:
select * from trade.customers c, trade.portfolio f where c.tid = f.tid
Similarly, join queries on non-colocated partitioned tables are not supported. This query has an equijoin condition
on the partitioning column of both tables, but SQLFire cannot be execute it because the tables are not colocated:
select * trade.buyorders b, trade.customers c where b.cid = c.cid
vFabric SQLFire User's Guide658
vFabric SQLFire Reference