-
2. Re: Teiid Embedded - org.teiid.subqueryUnnestDefault
vsathishkumaran Aug 25, 2015 1:59 PM (in response to rareddy)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 Aug 26, 2015 11:09 AM (in response to vsathishkumaran)1 of 1 people found this helpfulThe 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?
-
4. Re: Teiid Embedded - org.teiid.subqueryUnnestDefault
vsathishkumaran Aug 26, 2015 4:58 PM (in response to shawkins)Ok. Will log an issue for it
-
5. Re: Teiid Embedded - org.teiid.subqueryUnnestDefault
shawkins Aug 27, 2015 12:44 PM (in response to vsathishkumaran)Logged and worked as [TEIID-3659] subquery unnesting missed - JBoss Issue Tracker which will be in 8.12 Beta2.