Teiid sql generation issue
ashurov_mf Oct 8, 2013 12:14 AMWe use vdb.xml to specify schema. There are 3 models:
<model name="Stat"> <property name="importer.schemaPattern" value="public"/> <property name="importer.useFullSchemaName" value="false"/> <property name="importer.importKeys" value="true"/> <property name="importer.importIndexes" value="true"/> <property name="importer.importApproximateIndexes" value="true"/> <source name="Stat" translator-name="postgresql-conf" connection-jndi-name="java:/main-ds"/> </model> <model name="Dims" visible="true" type="PHYSICAL"> <source name="Dims" translator-name="postgresql-conf" connection-jndi-name="java:/dims-ds"/> <metadata type="DDL"><![CDATA[ CREATE FOREIGN TABLE Creative (creative_id integer PRIMARY KEY, order_id integer not null, user_id integer not null) OPTIONS (CARDINALITY 200000, nameinsource 'statistics.creative_m'); CREATE FOREIGN TABLE Zone (zone_id integer PRIMARY KEY, user_id integer not null) OPTIONS (CARDINALITY 100000, nameinsource 'statistics.zone_m'); ]]> </metadata> </model> <model name="Custom" visible="true" type="VIRTUAL"> <metadata type = "DDL"><![CDATA[ CREATE VIEW Billing AS SELECT b.zone_id, b.creative_id, b.requests, b.actions,b."date", c.user_id AS creative_user_id, c.order_id, z.user_id AS zone_user_id FROM ((Stat.billing_by_day AS b INNER JOIN /*+ optional */ Dims.creative AS c ON b.creative_id = c.creative_id) INNER JOIN /*+ optional */ Dims.zone AS z ON b.zone_id = z.zone_id); </metadata> </model>
So java:/main-ds and java:/dims-ds connectors to diffrent postgres databases.We prepare sql like:
SELECT "date", SUM("requests") as "requests", SUM("actions") as "actions" FROM Custom."Billing" WHERE "date" >= '2013-08-01' AND "date" < '2013-09-01' AND ("zone_user_id" IN (9781) OR "creative_user_id" IN (9781)) GROUP BY "date"
Teiid generates two sql: one to main db to billing_by_day table, and one:
SELECT g_1.zone_id AS c_0, g_0.creative_id AS c_1 FROM Dims.Creative AS g_0, Dims.Zone AS g_1 WHERE (g_1. user_id = 9781) OR (g_0.user_id = 9781) ORDER BY c_0, c_1That cross-join select executing on two big tables with many matching by the user, so it can't be done quickly on db (over 5 mins).
Issue: why teiid generate cross-join select instead of two independent selects? Are there ways to configured teiid to split that sql on two?
Thanks