[DWS-7] Write server side EXPLAIN PLAN web services

[dynamobi-build]

[reporter="ngoodman", created="Thu, 4 Mar 2010 21:28:43 -0800", resolved="Tue, 8 Jun 2010 14:46:57 -0700"]

Write server side web services to return the "EXPLAIN PLAN" results SOAP/XML needed by FlexSQLAdmin. General format is:

StmtText,StmtId,NodeId,Parent,PhysicalOp,LogicalOp,Argument,DefinedValues,EstimateRows,EstimateIO,EstimateCPU,AvgRowSize,TotalSubtreeCost,OutputList,Warnings,Type,Parallel,EstimateExecutions,level


true
0
1


SELECT [fname], [lname], [coursename],
[semester] FROM [vwCompSci]
11011
0.0563328229
SELECTfalse0


|--Sort(ORDER
BY:([tblCourses].[semester] ASC,
[tblCourses].[coursename] ASC,
[tblStudents].[lname] ASC,
[tblStudents].[fname] ASC))
121SortSort
ORDER BY:([tblCourses].[semester] ASC,
[tblCourses].[coursename] ASC,
[tblStudents].[lname] ASC,
[tblStudents].[fname] ASC)
10.01126126110.00010006969
0.0563328229

[tblCourses].[coursename],
[tblCourses].[semester],
[tblStudents].[fname],
[tblStudents].[lname]
PLAN_ROWfalse11


|--Nested Loops(Inner Join, OUTER
REFERENCES:([tblEnrollment].[studentid]))
142Nested LoopsInner Join
OUTER
REFERENCES:([tblEnrollment].[studentid])
101.254E-05290
0.0449713953

[tblCourses].[coursename],
[tblCourses].[semester],
[tblStudents].[fname],
[tblStudents].[lname]
PLAN_ROWfalse12
.....

[dynamobi-build]

[author="ngoodman", created="Thu, 4 Mar 2010 21:36:27 -0800"]

Some notes:

• Remove unnecessary fields (EstimateCPU, AvgRowSize,etc)
• Use "explain plan including all attributes with implementation" for explain plan
• End of Rows are of format ( : rowcount = 1, cumulative cost = 3.0). Put rowcount into estimateRows, and cumulative cost into TotalSubTreeCost
• Put name of RexNode as PhysicalOp/LogicalOp (IterCalcRel, IteratorToFennelConverter, etc)
• Put everything between RexNode Name and end rowcount into StmtText

You will have to build, in Java, the tree including inferring parent nodes based on levels. The indentation (two spaces per level) is key to this.

[dynamobi-build]

[author="ngoodman", created="Thu, 4 Mar 2010 21:50:29 -0800"]

Be aware of this bug (make sure you are using client drivers more recent than 13390):
http://jira.eigenbase.org/browse/FRG-393

[dynamobi-build]

[author="rzhang", created="Fri, 19 Mar 2010 01:21:44 -0700"]

Good reference for explain plan.

1. Overview: http://pub.eigenbase.org/wiki/FarragoExplainPlanExplained
2. XML Format: http://pub.eigenbase.org/wiki/FarragoExplainPlanXml

[dynamobi-build]

[author="rzhang", created="Tue, 23 Mar 2010 04:27:18 -0700"]

Status.
Coding and testing was done.
I need a little time to refine the code, after that, I will check in code.
The demo, please refer to DADMINUI-9

[dynamobi-build]

[author="ngoodman", created="Tue, 8 Jun 2010 14:46:57 -0700"]

Some followon issues needing to remove some cumulative rows, and costing pieces. Need to add icons as well, but will create those as separate issues.