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

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-3842?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins closed TEIID-3842.
---------------------------------


> 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
>
> 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}



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)