3 Replies Latest reply on Oct 9, 2013 7:20 AM by Steven Hawkins

    Teiid sql generation issue

    Michael Ash Newbie

      We 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_1

      That 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