1.1.1

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 = ?
SQLFire converts this query into an equijoin because both the inner and outer query tables are replicated:
select * from trade.securities s where s.sec_id IN ( select so.sid from
trade.sellorders so ) and s.tid = ?
Correlated subquery with outer and inner queries of partitioned tables
SQLFire can execute a correlated subquery having inner and outer queries of partitioned tables only if the query
satisfies the colocation criteria.
For example, SQLFire can execute this query because of the presence of an equijoin condition on cid columns
of the customers and portfolio tables:
select * from trade.customers c where exists (select * from trade.portfolio
f where c.cid = f.cid and qty > 297) and tid =?
Correlated subquery with inner query of replicated table
SQLFire can execute a correlated subquery when inner query is against a replicated table and the outer query is
against a partitioned or replicated table.
For example, the following query contains two correlated subqueries. SQLFire can execute the first subquery
because both the customers and portfolio tables satisfy the colocation criteria, and it can execute the
second subquery because the inner query is against a replicated table, sellorders.
select * from trade.customers c where EXISTS (select * from trade.portfolio
f where c.cid = f.cid and tid =?)
and NOT EXISTS (select * from trade.sellorders s where c.cid = s.cid and
status IN ('open','filled'))
Table Subquery having table expression in a From Clause and involving Partitioned Table
Query of a partitioned table with a table query in the FROM clause
Any query that includes a partitioned table cannot include a table query in the FROM clause. For example, SQLFire
cannot execute this query because the FROM clause includes a table subquery and the query references a partitioned
table:
select * from (select cid, sid, qty, tid from trade.portfolio f) where tid
= 7
vFabric SQLFire User's Guide714
vFabric SQLFire Reference