SQL Join Plan Representation
A system, method, and computer-readable medium are disclosed for improving the readability and understanding of a SQL query plan by presenting SQL query plan in a join plan representation. In various embodiments, the join plan representation includes one or more of a table join path portion, a statistics portion, an objects axis region and an operator region. The order of the table join path can be easily read in such a representation. Also, statistic figures like cost, IO, CPU and cardinality are all consistently and orderly presented, making it easy for users to understand and compare.
1 . A computer-implementable method for presenting a structured query language (SQL) query plan, comprising:
analyzing a SQL query plan to generate SQL join plan information;
parsing the SQL join plan information derived from analyzing the SQL query plan into a plurality of types of SQL join plan information; and,
generating a join plan representation for the analyzed query plan using the plurality of types of SQL join plan information.
2 . The method of claim 1 , wherein:
the plurality of types of SQL join plan information comprises at least one of table join path information, join plan statistics, objects axis information and operator information; and,
the generating a join plan representation comprises at least one of generating a table join path portion, a statistics portion, an objects axis region and an operator region.
3 . The method of claim 2 , wherein:
the table join path portion shows a table join path in a single direction; and,
arrows in the table join path portion represent how data are retrieved from SQL objects and are used in a next level of the join plan.
4 . The method of claim 2 , wherein:
the statistics portion allows user to select particular statistics associated with an operation in each step in the join plan.
5 . The method of claim 2 , wherein:
the objects axis region shows all referenced objects in an order of their join path.
6 . The method of claim 2 , wherein:
the operator region shows operations, data flow, and retrieval methods between objects of the join plan.
7 . A system comprising:
a processor;
a data bus coupled to the processor; and
a non-transitory, computer-readable storage medium embodying computer program code, the non-transitory, computer-readable storage medium being coupled to the data bus, the computer program code interacting with a plurality of computer operations and comprising instructions executable by the processor and configured for:
analyzing a SQL query plan to generate SQL join plan information;
parsing the SQL join plan information derived from analyzing the SQL query plan into a plurality of types of SQL join plan information; and,
generating a join plan representation for the analyzed query plan using the plurality of types of SQL join plan information.
8 . The system of claim 7 , wherein:
the plurality of types of SQL join plan information comprises at least one of table join path information, join plan statistics, objects axis information and operator information; and,
the generating a join plan representation comprises at least one of generating a table join path portion, a statistics portion, an objects axis region and an operator region.
9 . The system of claim 8 , wherein:
the table join path portion shows a table join path in a single direction; and,
arrows in the table join path portion represent how data are retrieved from SQL objects and are used in a next level of the join plan.
10 . The system of claim 8 , wherein:
the statistics portion allows user to select particular statistics associated with an operation in each step in the join plan.
11 . The system of claim 8 , wherein:
the objects axis region shows all referenced objects in an order of their join path.
12 . The system of claim 8 , wherein:
the operator region shows operations, data flow, and retrieval methods between objects of the join plan.
13 . A non-transitory, computer-readable storage medium embodying computer program code, the computer program code comprising computer executable instructions configured for:
analyzing a SQL query plan to generate SQL join plan information;
parsing the SQL join plan information derived from analyzing the SQL query plan into a plurality of types of SQL join plan information; and,
generating a join plan representation for the analyzed query plan using the plurality of types of SQL join plan information.
14 . The non-transitory, computer-readable storage medium of claim 13 , wherein:
the plurality of types of SQL join plan information comprises at least one of table join path information, join plan statistics, objects axis information and operator information; and,
the generating a join plan representation comprises at least one of generating a table join path portion, a statistics portion, an objects axis region and an operator region.
15 . The non-transitory, computer-readable storage medium of claim 14 , wherein:
the table join path portion shows a table join path in a single direction; and,
arrows in the table join path portion represent how data are retrieved from SQL objects and are used in a next level of the join plan.
16 . The non-transitory, computer-readable storage medium of claim 14 , wherein:
the statistics portion allows user to select particular statistics associated with an operation in each step in the join plan.
17 . The non-transitory, computer-readable storage medium of claim 14 , wherein:
the objects axis region shows all referenced objects in an order of their join path.
18 . The non-transitory, computer-readable storage medium of claim 14 , wherein:
the operator region shows operations, data flow, and retrieval methods between