[teiid-issues] [JBoss JIRA] (TEIID-3842) Teiid's query's explain not consistence with other Database

Teiid's query's explain not consistence with other Database ------------------------------------------------------------ Key: TEIID-3842 URL: https://issues.jboss.org/browse/TEIID-3842 Project: Teiid Issue Type: Enhancement Components: Query Engine Affects Versions: 9.x Reporter: Kylin Soong Assignee: Kylin Soong Fix For: 9.x h2. The way to get execution plan is not consistence with others For most of RDBMS(Oracle, Mysql), the execution plan get via SQL query {code} explain select ... {code} But in Teiid, we need use Teiid API, and need enable showplan in advance: {code} statement.execute("set showplan on"); ResultSet rs = statement.executeQuery("select ..."); TeiidStatement tstatement = statement.unwrap(TeiidStatement.class); PlanNode queryPlan = tstatement.getPlanDescription(); System.out.println(queryPlan); {code} Cache Hint can be added as prefix, so I think we can enhance that show plan also as prefix, in order for consistence with others. h2. The plan structure is too complex [1] is the query plan of dynamicvdb-datafederation which is 2 datasources' federation, it long, complex, for the users/customers, it's not easy for them to understand. So can we enhance that simplify the output, like add a format option that can output as matrix/table. [1] dynamicvdb-datafederation's 'select * from Stock' query plan {code} ProjectNode + Relational Node ID:0 + Output Columns: 0: product_id (integer) 1: symbol (string) 2: price (bigdecimal) 3: company_name (string) + Statistics: 0: Node Output Rows: 9 1: Node Next Batch Process Time: 0 2: Node Cumulative Next Batch Process Time: 12 3: Node Cumulative Process Time: 21 4: Node Next Batch Calls: 3 5: Node Blocks: 2 + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: JoinNode + Relational Node ID:1 + Output Columns: 0: ID (integer) 1: symbol (string) 2: price (bigdecimal) 3: COMPANY_NAME (string) + Statistics: 0: Node Output Rows: 9 1: Node Next Batch Process Time: 4 2: Node Cumulative Next Batch Process Time: 12 3: Node Cumulative Process Time: 21 4: Node Next Batch Calls: 3 5: Node Blocks: 2 + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: JoinNode + Relational Node ID:2 + Output Columns: 0: symbol (string) 1: price (bigdecimal) + Statistics: 0: Node Output Rows: 10 1: Node Next Batch Process Time: 2 2: Node Cumulative Next Batch Process Time: 7 3: Node Cumulative Process Time: 7 4: Node Next Batch Calls: 2 5: Node Blocks: 1 + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: ProjectNode + Relational Node ID:3 + Output Columns:file (clob) + Statistics: 0: Node Output Rows: 1 1: Node Next Batch Process Time: 0 2: Node Cumulative Next Batch Process Time: 2 3: Node Cumulative Process Time: 2 4: Node Next Batch Calls: 1 5: Node Blocks: 0 + Cost Estimates:Estimated Node Cardinality: -1.0 + Child 0: AccessNode + Relational Node ID:4 + Output Columns: 0: file (clob) 1: filePath (string) + Statistics: 0: Node Output Rows: 1 1: Node Next Batch Process Time: 2 2: Node Cumulative Next Batch Process Time: 2 3: Node Cumulative Process Time: 2 4: Node Next Batch Calls: 1 5: Node Blocks: 0 + Cost Estimates:Estimated Node Cardinality: -1.0 + Query:EXEC MarketData.getTextFiles('*.txt') + Model Name:MarketData + Select Columns:MarketData.getTextFiles.file + Child 1: TextTableNode + Relational Node ID:5 + Output Columns: 0: symbol (string) 1: price (bigdecimal) + Statistics: 0: Node Output Rows: 10 1: Node Next Batch Process Time: 3 2: Node Cumulative Next Batch Process Time: 3 3: Node Cumulative Process Time: 3 4: Node Next Batch Calls: 2 5: Node Blocks: 1 + Cost Estimates:Estimated Node Cardinality: -1.0 + Table Function:TEXTTABLE(f.file COLUMNS symbol string, price bigdecimal HEADER) AS SP + Join Strategy:NESTED TABLE JOIN + Join Type:CROSS JOIN + Join Criteria + Child 1: AccessNode + Relational Node ID:6 + Output Columns: 0: SYMBOL (string) 1: ID (integer) 2: COMPANY_NAME (string) + Statistics: 0: Node Output Rows: 25 1: Node Next Batch Process Time: 1 2: Node Cumulative Next Batch Process Time: 1 3: Node Cumulative Process Time: 17 4: Node Next Batch Calls: 5 5: Node Blocks: 4 + Cost Estimates:Estimated Node Cardinality: -1.0 + Query:SELECT g_0.SYMBOL AS c_0, g_0.ID AS c_1, g_0.COMPANY_NAME AS c_2 FROM Accounts.PRODUCT AS g_0 ORDER BY c_0 + Model Name:Accounts + Join Strategy:ENHANCED SORT JOIN RAN AS SORT MERGE (SORT/ALREADY_SORTED) + Join Type:INNER JOIN + Join Criteria:SP.symbol=A.SYMBOL + Select Columns: 0: A.ID AS product_id 1: SP.symbol 2: SP.price 3: A.COMPANY_NAME AS company_name + Data Bytes Sent:0 + Planning Time:128 {code}