9 Replies Latest reply on Aug 6, 2014 8:50 AM by Steven Hawkins

    Optimization is not applied to inline view if the translator does not support inline views?

    Sanjeev Gour Novice

      Hi-

       

      We have a query of the following format-

       

      SELECT  DISTINCT  DATAMART_NAME AS c_9, '_constant_' AS c_10 FROM (SELECT datamart_name FROM

        (

            SELECT 'DATACOM Datamart' AS DATAMART_NAME FROM SECURITY_DM_DATACOM_DEV_R140_CA31_CMGRD1.VIEWOBJACCESS

            UNION ALL

           SELECT 'DB2 D91A Datamart' AS DATAMART_NAME FROM SECURITY_DM_DB2_D91APTIB_CMGRD1.VIEWOBJACCESS

        ) AS t

      ) a

       

      and part of the generate query plan looks like this (full plan is attached)-

       

      OPTIMIZATION COMPLETE:

      PROCESSOR PLAN:

      SortNode(0) output=[datamart_name AS c_9, '_constant_' AS c_10] [DUP_REMOVE]

        ProjectNode(1) output=[datamart_name AS c_9, '_constant_' AS c_10] [datamart_name AS c_9, '_constant_' AS c_10]

          UnionAllNode(2) output=[datamart_name]

            AccessNode(3) output=['DATACOM Datamart' AS DATAMART_NAME] SELECT 'DATACOM Datamart' FROM SECURITY_DM_DATACOM_DEV_R140_CA31_CMGRD1.VIEWOBJACCESS AS g_0

            AccessNode(4) output=['DB2 D91A Datamart' AS DATAMART_NAME] SELECT 'DB2 D91A Datamart' FROM SECURITY_DM_DB2_D91APTIB_CMGRD1.VIEWOBJACCESS AS g_0

       

      If we take a close look at the AccessNodes 3 and 4, they query a constant and the main query issued a DISTINCT, in such a case, we expect that Teiid will apply a limit clause on each of those access nodes but that doesn't appear to the case. Is that a fair expectation or we are missing something here?

       

      If we take the UNION ALL out and the inline view contains only one statement, then it applies a limit 1 at the end.

       

      Note:- The related translator does not support inline views if that affects the behavior.

       

      Appreciate any help.

       

      Thanks.

      Sanjeev.