[ https://issues.jboss.org/browse/TEIID-3842?page=com.atlassian.jira.plugin... ] Steven Hawkins commented on TEIID-3842: --------------------------------------- Which is similar to SQL Server. You also don't need to use any JDBC / Java specific API by issuing a "SHOW PLAN" statement. This would not be the same as a cache hint as it would be a new keyword - which should only be done at a major version since it can be a breaking change. There are no standards here in terms of syntax or output, so there isn't really consistency that we necessarily should be targeting. Generally this feature for us has been more tooling rather than user oriented, so it's fine to try to update it in terms of what could be more helpful for users. Unfortunately a tree structure makes the most sense for anything beyond a simple query - as that conveys the exact processing layout and due to the optimization and rewrite process the user query symbols may not exist anymore so it could be hard to simply correlate back to the user query. > 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} > 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} -- This message was sent by Atlassian JIRA (v6.4.11#64026)