1.0

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 =?
SQLFire executes this query without converting it into an equijoin, because the tables are not colocated on tid:
select * from trade.customers c where c.tid IN (select f.tid from
trade.portfolio f where qty > 297)
This query is also executed by converting it into an equijoin, because the inner table is replicated and does not
require colocation criteria:
select * from trade.portfolio f where f.sid IN ( select sec_id from
trade.securities ).
Non-correlated subquery with an outer query of a replicated table
SQLFire supports non-correlated subqueries where the outer query table is replicated and the inner query table
is either replicated or partitioned. If the inner query table is replicated, SQLFire optimizes the independent queries
into an equi-join query if possible. If the inner query table is partitioned, then SQLFire executes the query without
attempting an equijoin conversion.
For example, SQLFire executes this query without converting it to an equijoin, because the inner query table is
partitioned:
select * from trade.securities s where s.sec_id IN ( select f.sid from
trade.portfolio f ) and s.tid = ?
659
vFabric SQLFire Limitations