Question on VDB Query Plans

[walla2sl]

I have two tables, call them a and b. Both reside in the same database, but imported as separate source xmi files. I've built Vds separately on each.

I want to create another Vdb, which is a full outer join of the two Vdbs. I create a view containing the two Vdbs as follows:

SELECT coalesce(vdb_a.key, vdb_b.key) as key, vdb_a.column_1, vdb_b.column_1

FROM vdb_a FULL OUTER JOIN /*+ optional */ vbd_b ON vdb_a.key = vdb_b.key

As a test, I query the combined Vdb as follows:

SELECT key, sum(column_1), sum(column_2)

FROM vdb

WHERE key = 123

GROUP BY key

My desired effect is for this to either:

a) push down the entire Sql to the source as a full outer join statement, including the filter predicate key = 123

b) push down two queries, each containing the filter predicate key = 123

Instead, I'm seeing two queries without the filter predicate in the source and Teiid ends pulling all the data from those tables and then applying the filter locally. This is not optimal for very large data sets. I'd like to be able to tell Teiid to push the Sql and predicate back to the source. Is this possible?

[rareddy]

Scott,

The third VDB treats other two VDBs as two separate sources. However, you can import the first two VDBs into third VDB and use same translator name and JNDI name for source, then it will treat as your expectation in (a)

See VDB Reuse - Teiid 8.11 (draft) - Project Documentation Editor

Ramesh..

[walla2sl]

Hi Ramesh,

I've attempted to update the connection profile for the first two VDBs but I'm unable to do so because the models are 'read-only'. I'm using Designer 9.0.1 with server 8.8 in Eclipse 4.4.2.

Anyhow, to work around, I created a VDB with the two views and imported each individually as its own VDB source model. So the connection profile info is the same on both source models now. However, I'm still not seeing the filter get pushed down to the database. Do you think the coalesce function is causing issue and Teiid doesn't know how to map that back to each VDB as a filter?

coalesce(vdb_a.key, vdb_b.key) as key

Thanks,

Scott

[rareddy]

Scott,

The behavior I explained is for server. Designer, does help you create "import" VDB part during the (third) VDB creation, but when you do preview it will NOT use the same semantics as server. BTW, when I say import, it means Teiid Designer User Guide

As per the specific issue of coalesce, you would need to see query plan for the reason.

Ramesh..