Transformation reconstruction for optimized database query
Provided is a system and method for reconstructing and visualizing transformation steps that are performed to an optimized database query. In one example, the method may include receiving a database query including an initial set of execution steps, generating a plurality of alternative sets of execution steps for the database query based on transformations to the initial set of execution steps, selecting an alternative set of execution steps from among the plurality of alternative sets of execution steps based on a performance of the alternative set of execution steps, identifying transformations that are used to transform the initial set of execution steps into the selected alternative set of execution steps, and displaying information about the identified transformations via a user interface.
1. A computing system comprising:
a storage to store a database query comprising an initial query execution plan that includes an initial set of execution steps which are assigned an initial sequence of identifiers, respectively; and
a processor configured to
transform the initial query execution plan into a plurality of alternative sets of execution steps for the initial query execution plan, wherein the processor applies a sequence of transformations to the initial query execution plan to create the plurality of alternative sets of execution steps,
assign different sequences of identifiers to the execution steps each time a transformation is applied, respectively, and store the assigned different sequence of identifiers in a log, wherein the assigning comprises reusing an identifier for a step when the step remains unchanged as a result of the transformation being applied, and changing identifiers of steps that are changed as a result of the transformation being applied;
select an alternative set of execution steps from among the plurality of alternative sets of execution steps in the log based on a performance of the alternative set of execution steps,
reconstruct a list of identifiers of transformation operations that are applied to the initial set of execution steps to transform the initial set of execution steps into the selected alternative set of execution steps by backtracking through the log based on one or more reused identifiers assigned to the selected alternative set of execution steps, and
display at least a portion of the reconstructed list of identifiers via a user interface.
2. The computing system of claim 1 , wherein the database query comprises a structured query language (SQL) query comprising an ordered sequence of steps for accessing data from one or more database tables.
3. The computing system of claim 1 , wherein the processor is further configured to trace an order of execution steps that remain after each transformation in the sequence of transformations and store each traced order in the log.
4. The computing system of claim 1 , wherein the processor is configured to select an optimal alternative set of execution steps from the plurality of alternative sets of execution steps based on query costs of the respective plurality of alternative sets of execution steps.
5. The computing system of claim 1 , wherein the processor prevents a subset of transformations that are used to transform the initial set of execution steps into non-selected alternative sets of execution steps from being displayed.
6. The computing system of claim 1 , wherein the plurality of alternative sets of execution steps comprise a plurality of alternative execution plans that are generated by the processor based on transformations to a logical plan of an SQL query.
7. The computing system of claim 1 , wherein the processor is further configured to compile the database query based on the selected alternative set of execution steps and execute the compiled database query.
8. A method comprising:
receiving a database query comprising an initial query execution plan that includes an initial set of execution steps which are assigned an initial sequence of identifiers, respectively;
transforming the initial query execution plan into a plurality of alternative sets of execution steps for the initial query execution plan, wherein the transforming comprises applying a sequence of transformations to the initial query execution plan to create the plurality of alternative sets of execution steps;
assigning different sequences of identifiers to the execution steps each time a transformation is applied, respectively, and storing the assigned different sequence of identifiers in a log, wherein the assigning comprises reusing an identifier for a step when the step remains unchanged as a result of the transformation being applied, and changing identifiers of steps that are changed as a result of the transformation being applied;
selecting an alternative set of execution steps from among the plurality of alternative sets of execution steps in the log based on a performance of the alternative set of execution steps;
reconstructing a list of transformation operations that are applied to the initial set of execution steps to transform the initial set of execution steps into the selected alternative set of execution steps by backtracking through the log based on one or more reused identifiers assigned to the selected alternative set of execution steps; and
displaying at least a portion of the reconstructed list of identifiers via a user interface.
9. The method of claim 8 , wherein the database query comprises a structured query language (SQL) query comprising an ordered sequence of steps for accessing data from one or more database tables.
10. The method of claim 8 , further comprising tracing an order of execution steps that remain after each transformation in the sequence of transformations and storing the traced order in the log.
11. The method of claim 8 , wherein the selecting comprises selecting an optimal alternative set of execution steps from the plurality of alternative sets of execution steps based on query costs of the respective plurality of alternative sets of execution steps.
12. The method of claim 8 , wherein the identifying further comprises preventing a subset of transformations that are used to transform the initial set of execution steps into non-selected alternative sets of execution steps from being displayed.
13. The method of claim 8 , wherein the generating the plurality of alternative sets of execution steps comprises generating a plurality of alternative execution plans based on transformations to a logical plan of an SQL query.
14. The method of claim 8 , further comprising compiling the database query based on the selected alternative set of execution steps and executing the compiled database query.
15. A non-transitory computer-readable medium comprising instructions which when executed by a processor cause a computer to perform a method comprising:
receiving a database query comprising an initial query execution plan that includes an initial set of execution steps which are assigned an initial sequence of identifiers, respectively;
transforming the initial query execution plan into a plurality of alternative sets of execution steps for the initial query execution plan, wherein the transforming comprises applying a sequence of transformations to the initial query execution plan to create the plurality of alternative sets of execution steps;
assigning different sequences of identifiers to the execution steps each time a transformation is applied, respectively, and storing the assigned different sequence of identifiers in a log, wherein the assigning comprises reusing an identifier for a step when the step remains unchanged as a result of the transformation being applied, and changing identifiers of steps that are changed as a result of the transformation being applied;
selecting an alternative set of execution steps from among the plurality of alternative sets of execution steps in the log based on a performance of the alternative set of execution steps;
reconstructing a list of transformation operations that are applied to the initial set of execution steps to transform the initial set of execution steps into the selected alternative set of execution steps by backtracking through the log based on one or more reused identifiers assigned to the selected alternative set of execution steps; and
displaying at least a portion of the reconstructed list of identifiers via a user interface.
16. The non-transitory computer-readable medium of claim 15 , wherein the database query comprises a structured query language (SQL) query comprising an ordered sequence of steps for accessing data from one or more database tables.
17. The non-transitory computer-readable medium of claim 15 , wherein the method further comprises tracing an order of execution steps that remain after each transformation in the sequence of transformations and storing each traced order in the log.
18. The computing system of claim 1 , wherein the processor is further configured to prevent identifiers of a subset of transformation operations from being displayed via the user interface.