5 Replies Latest reply on Aug 27, 2015 12:44 PM by shawkins

    Teiid Embedded - org.teiid.subqueryUnnestDefault

    vsathishkumaran

      Hello

       

      How to set system property "org.teiid.subqueryUnnestDefault" in Teiid Embedded Java Program? Can someone please help me?

       

       

      Thanks

       

      Sathish

        • 2. Re: Teiid Embedded - org.teiid.subqueryUnnestDefault
          vsathishkumaran

          I have set the property as below for Teiid Embedded Program; however I could not see the plan changes. Am I doing anything wrong?

           

          public static void main(String[] args) throws Exception {

             String query;

            EmbeddedConfiguration ec = new EmbeddedConfiguration();

            System.setProperty("org.teiid.subqueryUnnestDefault", "true");

            ec.setUseDisk(true);

            EmbeddedServer teiidServer = new EmbeddedServer();

          .....

           

           

          The plan still generates queries with EXISTS clause

           

          Query: select b.SYMBOL from  PRODUCT b where  exists (select 1 from PRICES d where d.SYMBOL=b.SYMBOL)

           

          <?xml version="1.0" encoding="UTF-8"?>
          <node name="ProjectNode">
             <property name="Relational Node ID">
                <value>3</value>
             </property>
             <property name="Output Columns">
                <value>SYMBOL (string)</value>
             </property>
             <property name="Cost Estimates">
                <value>Estimated Node Cardinality: -1.0</value>
             </property>
             <property name="Child 0">
                <node name="SelectNode">
                   <property name="Relational Node ID">
                      <value>4</value>
                   </property>
                   <property name="Output Columns">
                      <value>SYMBOL (string)</value>
                   </property>
                   <property name="Cost Estimates">
                      <value>Estimated Node Cardinality: 1.0</value>
                   </property>
                   <property name="Child 0">
                      <node name="AccessNode">
                         <property name="Relational Node ID">
                            <value>5</value>
                         </property>
                         <property name="Output Columns">
                            <value>SYMBOL (string)</value>
                         </property>
                         <property name="Cost Estimates">
                            <value>Estimated Node Cardinality: -1.0</value>
                         </property>
                         <property name="Query">
                            <value>SELECT g_0.SYMBOL FROM PortfolioSchema.PRODUCT AS g_0</value>
                         </property>
                         <property name="Model Name">
                            <value>PortfolioSchema</value>
                         </property>
                      </node>
                   </property>
                   <property name="Criteria Subplan 0">
                      <node name="LimitNode">
                         <property name="Relational Node ID">
                            <value>0</value>
                         </property>
                         <property name="Output Columns">
                            <value>expr1 (integer)</value>
                         </property>
                         <property name="Cost Estimates">
                            <value>Estimated Node Cardinality: 1.0</value>
                         </property>
                         <property name="Child 0">
                            <node name="AccessNode">
                               <property name="Relational Node ID">
                                  <value>1</value>
                               </property>
                               <property name="Output Columns">
                                  <value>expr1 (integer)</value>
                               </property>
                               <property name="Cost Estimates">
                                  <value>Estimated Node Cardinality: 1.0</value>
                               </property>
                               <property name="Query">
                                  <value>SELECT 1 FROM Teradata_OneDev.PRICES AS g_0 WHERE
            g_0.SYMBOL = b.SYMBOL</value>
                               </property>
                               <property name="Model Name">
                                  <value>Teradata_OneDev</value>
                               </property>
                            </node>
                         </property>
                         <property name="Row Offset">
                            <value>null</value>
                         </property>
                         <property name="Row Limit">
                            <value>1</value>
                         </property>
                      </node>
                   </property>
                   <property name="Criteria">
                      <value>EXISTS (SELECT 1 FROM Teradata_OneDev.PRICES AS d WHERE
            d.SYMBOL = b.SYMBOL LIMIT 1)</value>
                   </property>
                </node>
             </property>
             <property name="Select Columns">
                <value>b.SYMBOL</value>
             </property>
             <property name="Data Bytes Sent">
                <value>7</value>
             </property>
             <property name="Planning Time">
                <value>94</value>
             </property>
          </node>
          
          • 3. Re: Teiid Embedded - org.teiid.subqueryUnnestDefault
            shawkins

            The optimization is being overly cautious here.  It isn't accounting for the select of the exists effectively being unique.  Can you log an issue for this?  

            1 of 1 people found this helpful
            • 4. Re: Teiid Embedded - org.teiid.subqueryUnnestDefault
              vsathishkumaran

              Ok. Will log an issue for it

              • 5. Re: Teiid Embedded - org.teiid.subqueryUnnestDefault
                shawkins

                Logged and worked as [TEIID-3659] subquery unnesting missed - JBoss Issue Tracker which will be in 8.12 Beta2.