Details
-
Enhancement
-
Resolution: Won't Do
-
Major
-
None
-
9.x
-
None
Description
The way to get execution plan is not consistence with others
For most of RDBMS(Oracle, Mysql), the execution plan get via SQL query
explain select ...
But in Teiid, we need use Teiid API, and need enable showplan in advance:
statement.execute("set showplan on"); ResultSet rs = statement.executeQuery("select ..."); TeiidStatement tstatement = statement.unwrap(TeiidStatement.class); PlanNode queryPlan = tstatement.getPlanDescription(); System.out.println(queryPlan);
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.
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
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
