1.1.1

NotesSupport levelQuery type
See About the colocation requirement for
querying multiple, partitioned tables on page
712.
Queries that involve two or more partitioned
tables must satisfy the colocation criteria.
Supported with colocation criteriaOuter join queries that involve multiple,
partitioned tables
See Outer join query of multiple, partitioned
tables on page 713.
See Query of a single partitioned table on
page 712.
SupportedOuter join queries that involve a single
partitioned table
See Outer join query of multiple replicated
tables and a single partitioned table on page
713.
*Non-correlated queries of partitioned and
replicated tables are supported. However,
Supported*Non-correlated subquery with outer query of
a partitioned table
SQLFire may attempt to converts such
queries into equijoin queries. This
optimization is possible only if the query
satisfies the colocation criteria.
Non-correlated subquery with an outer query
of a partitioned table on page 713
Non-correlated subquery with an outer query
of a replicated table on page 714
SupportedNon-correlated subquery with outer query of
a replicated table
Correlated subqueries involving partitioned
tables must satisfy the colocation criteria.
Supported with colocation criteriaCorrelated subquery with outer and inner
queries of partitioned tables
Correlated subquery with outer and inner
queries of partitioned tables on page 714
Correlated subquery with inner query of
replicated table on page 714
SupportedCorrelated subquery with inner query of a
replicated table
Correlated subquery with outer query of a
replicated table inner query of a partitioned
table on page 715
Not SupportedCorrelated subquery with outer query of a
replicated table inner query having one or
more partitioned tables
The sections that follow use the following set of example tables to describe query support and limitations.
create table trade.securities (sec_id int not null, symbol varchar(10) not
null,
price decimal (30, 20), exchange varchar(10) not null, tid int,
constraint sec_pk primary key (sec_id) )replicate
create table trade.customers (cid int not null, cust_name varchar(100),
since date, addr varchar(100), tid int, primary key (cid))
partition by column (cust_name)
create table trade.portfolio (cid int not null, sid int not null, qty int
not null, availQty int not null, subTotal decimal(30,20),
tid int, constraint portf_pk primary key (cid, sid), constraint cust_fk
foreign key (cid) references trade.customers (cid) on delete restrict,
constraint sec_fk foreign key (sid) references trade.securities
(sec_id)) partition by column (cid) colocate with (trade.customers)
711
vFabric SQLFire Limitations