Configuration-parametric query optimization
Described herein are techniques for Configuration-Parametric Query Optimization (C-PQO) that can improve performance of database tuning tools. When first optimizing a query, a compact representation of the optimization space is generated. The representation can then be used to efficiently produce other execution plans for the query under arbitrary hypothetical configurations.
1. One or more computer readable storage media storing information to enable a computing device to perform a process to facilitate physical design tuning of a database comprised of tables and indices thereof, wherein the database is managed by a database management system (DBMS), the DBMS configured to optimize execution plans for queries, the database having a set of indices, wherein if the database is reconfigured by adding, removing, or reconfiguring an index for a table of the database, an execution plan optimized by the DBMS for an arbitrary query before the reconfiguration of the database will differ from an execution plan for the arbitrary query optimized by the DBMS after the database is reconfigured, the process comprising:
receiving at the DBMS, from a client, a query and a first candidate configuration, the first candidate configuration comprising a first hypothetical index change for the database, the first hypothetical index change comprising information representing a first addition, removal, or modification of an index of the database;
while performing optimization of the query, by the DBMS, according to the first candidate configuration, producing a first execution plan, the producing including identifying parts of the first execution plan as being independent of the first hypothetical index change and storing dependency information indicating the identified parts, wherein the DBMS stores the first execution plan of the query and the dependency information and does not execute the query; and
receiving at the DBMS, from the client, a second candidate configuration different than the first candidate configuration and describing a second hypothetical index change comprising information representing a second addition, removal, or modification of an index of the database, and using the stored first execution plan and the dependency information to perform optimization of the query according to the second candidate configuration by, according to the dependency information, using the parts of the stored first execution plan identified by the dependency information to build a second execution plan that includes the indentified parts of the first execution plan and includes second parts that are dependent on the second hypothetical index change.
2. One or more computer readable storage media according to claim 1 , wherein the DBMS comprises a Cascades-based optimizer that performs memoization.
3. One or more computer readable storage media according to claim 1 , wherein the client determines whether the query has been previously issued and when it has not the client requests that information about the first MEMO data structure execution plan be returned to the client.
4. One or more computer readable storage media according to claim 3 , wherein the client uses the returned first execution plan to optimize a workload.
5. One or more computer readable storage media according to claim 1 , wherein the first execution plan comprises a MEMO data structure that is capable of representing other execution plans that are possible for the query for varying candidate configurations.
6. One or more computer readable stgorage media according to claim 1 , the process further comprising producing a second execution plan for the second candidate configuration that uses at least a portion of the first execution plan generated by the optimizing the query according to the first candidate configuration.