1.1.1

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
Finally, SQLFire does not support queries involving multiple partitioned tables that do not have a join condition.
For example, this query is not supported:
Select * from trade.customers c, trade.portfolio f where f.tid = ?
Outer join query of multiple, partitioned tables
In order to execute an outer join against more than one partitioned table, you must satisfy the colocation criteria,
which requires a join condition on the partitioning columns. For example, consider the query:
select * from trade.customers c LEFT OUTER JOIN trade.portfolio f LEFT OUTER
JOIN trade.sellorders so on f.cid = so.cid on c.cid= f.cid where f.tid =
?
This query involves two partitioned tables and one replicated table. Because the cid column is the partitioning
column of the customers and portfolio tables, SQLFire can execute the query.
Outer join query of multiple replicated tables and a single partitioned table
Outer join queries that involve only one partitioned table (and possibly multiple replicated tables) are supported
without meeting any additional criteria. For example, SQLFire can execute this query because it references only
one partitioned table:
select * from trade.portfolio f LEFT OUTER JOIN trade.sellorders so on f.cid
= so.cid on c.cid= f.cid where f.tid = ?
Non-correlated subquery with an outer query of a partitioned table
SQLFire supports non-correlated subqueries where the outer query table is partitioned and the inner query table
is either replicated or partitioned. However, SQLFire only optimizes the independent queries into an equi-join
query if the colocation criteria are satisfied. If the colocation criteria is not satisfied, SQLFire evaluates the
subquery independently (using map and reduce functions).
For example, SQLFire converts this query into an equijoin query because customer and portfolio are
colocated on cid:
select * from trade.customers c where c.cid IN (select f.cid from
trade.portfolio f where qty > 297) and tid =?
713
vFabric SQLFire Limitations