Query expression result caching using dynamic join index
An apparatus, method and computer program product for query optimization in a Relational Database Management System (RDBMS), wherein an optimizer accesses a query expression repository (QER) storing planning and execution information for QEs from previous queries, wherein the QEs comprise table relations, intermediate results and/or final results of operations in the previous queries. Additionally, dynamic join indexes representing QE results are created for high-value QEs selected from the QER and maintained within a DJI repository. During query plan creation for a current or subsequent query, the optimizer searches the QER and DJI repository for DJIs created for high-value QEs corresponding to QEs contained in the current or subsequent query. DJIs corresponding to the matching QEs are used in the query planning phase to rewrite the current or subsequent user query so that stored QE results are used to answer QEs contained in the current or subsequent query.
1. A computer-implemented apparatus, comprising:
a relational database management system (RDBMS) executing in a computer system, wherein the RDBMS manages a relational database comprised of one or more tables storing data;
an optimizer for interpreting one or more queries comprised of one or more query expressions to generate one or more query execution plans for execution by the RDBMS; and
a Dynamic Join Index (DJI) repository for storing dynamic join indexes (DJIs) corresponding to query expressions having high cache values selected from previous queries; the cache value of a query expression being determined by evaluating benefit and cost of storing the query expression within the DJI repository;
wherein the optimizer when generating query execution plans for a current query searches the DJI repository for DJIs corresponding to query expressions contained in the current query; and uses DJIs read from said search to generate the query execution plans for the current query.
2. The apparatus of claim 1 , further comprising a query expression repository for storing planning and execution information for previous queries, said planning and execution information including operation specific information for said previous queries, the operation specific information including query expression computing cost, query expression result size, and query expression frequency of use; and
wherein the cache value of a query expression selected from previous queries is determined by said optimizer from the operation specific information stored within said query expression repository.
3. The apparatus of claim 1 , wherein said dynamic join indexes (DJIs) corresponding to query expressions selected from previous queries comprise materialized views containing query expression results corresponding to the query expressions selected from previous queries.
4. The apparatus of claim 1 , further comprising a DJI cache for storing and managing metadata and usage history for the DJIs stored within said DJI repository.
5. A computer-implemented method, comprising:
executing a relational database management system (RDBMS) in a computer system, wherein the RDBMS manages a relational database comprised of one or more tables storing data;
interpreting one or more queries in an optimizer to generate one or more query execution plans for execution by the RDBMS, and
maintaining a Dynamic Join Index (DJI) repository for storing dynamic join indexes (DJIs) corresponding to query expressions having high cache values selected from previous queries; the cache value of a query expression being determined by evaluating benefit and cost of storing the query expression within the DJI repository;
wherein the optimizer when generating query execution plans for a current query searches the DJI repository for DJIs corresponding to query expressions contained in the current query; and uses DJIs read from said search to generate the query execution plans for the current query.
6. The method of claim 5 , further comprising the step of:
storing planning and execution information for previous queries within a query expression repository, said planning and execution information including operation specific information for said previous queries, the operation specific information including query expression computing cost, query expression result size, and query expression frequency of use; and
wherein the cache value of a query expression selected from previous queries is determined by said optimizer from the operation specific information stored within said query expression repository.
7. The method of claim 5 , wherein said dynamic join indexes (DJIs) corresponding to query expressions selected from previous queries comprise materialized views containing query expression results corresponding to the query expressions selected from previous queries.
8. The apparatus of claim 5 , further comprising the step of:
maintaining a DJI cache for storing and managing metadata and usage history for the DJIs stored within said DJI repository.
9. A computer program product, the computer program product comprising a tangible, non-transitory, computer readable storage medium having program instructions embodied therewith, the program instructions executable by a computer system to cause the computer system to perform a method, comprising:
executing a relational database management system (RDBMS) in a computer system, wherein the RDBMS manages a relational database comprised of one or more tables storing data;
interpreting one or more queries in an optimizer to generate one or more query execution plans for execution by the RDBMS, and
maintaining a Dynamic Join Index (DJI) repository for storing dynamic join indexes (DJIs) corresponding to query expressions having high cache values selected from previous queries; the cache value of a query expression being determined by evaluating benefit and cost of storing the query expression within the DJI repository;
wherein the optimizer when generating query execution plans for a current query searches the DJI repository for DJIs corresponding to query expressions contained in the current query; and uses DJIs read from said search to generate the query execution plans for the current query.
10. The computer program product of claim 9 , wherein the program instructions executable by a computer system further comprises the step of:
storing planning and execution information for previous queries within a query expression repository, said planning and execution information including operation specific information for said previous queries, the operation specific information including query expression computing cost, query expression result size, and query expression frequency of use; and
wherein the cache value of a query expression selected from previous queries is determined by said optimizer from the operation specific information stored within said query expression repository.
11. The computer program product of claim 9 , wherein said dynamic join indexes (DJIs) corresponding to query expressions selected from previous queries comprise materialized views containing query expression results corresponding to the query expressions selected from previous queries.
12. The computer program product of claim 9 , wherein the program instructions executable by a computer system further comprises the step of:
maintaining a DJI cache for storing and managing metadata and usage history for the DJIs stored within said DJI repository.
13. A computer-implemented apparatus, comprising:
a relational database management system (RDBMS) executing in a computer system, wherein the RDBMS manages a relational database comprised of one or more tables storing data;
an optimizer for interpreting one or more queries comprised of one or more query expressions to generate one or more query execution plans for execution by the RDBMS;
a Query Expression Repository (QER) for storing planning and execution information for previous queries, said planning and execution information including operation specific information for said previous queries, the operation specific information including query expression computing cost, query expression result size, and query expression frequency of use; and
a Dynamic Join Index (DJI) repository for storing dynamic join indexes (DJIs) corresponding to query expressions having high cache value selected from query expressions stored in said QER; the cache value of a query expression selected from previous queries being determined by said optimizer from the operation specific information stored within said QER;
wherein the optimizer when generating query execution plans for a current query searches the DJI repository for DJIs corresponding to query expressions contained in the current query; and uses DJIs read from said search to generate the query execution plans for the current query.
14. A computer-implemented method, comprising:
executing a relational database management system (RDBMS) in a computer system, wherein the RDBMS manages a relational database comprised of one or more tables storing data;
interpreting one or more queries in an optimizer to generate one or more query execution plans for execution by the RDBMS,
maintaining a Query Expression Repository (QER) for storing planning and execution information for previous queries, said planning and execution information including operation specific information for said previous queries, the operation specific information including query expression computing cost, query expression result size, and query expression frequency of use; and
maintaining a Dynamic Join Index (DJI) repository for storing dynamic join indexes (DJIs) corresponding to query expressions having high cache value selected from query expressions stored in said QER; the cache value of a query expression selected from previous queries being determined by said optimizer from the operation specific information stored within said QER;
wherein the optimizer when generating query execution plans for a current query searches the DJI repository for DJIs corresponding to query expressions contained in the current query; and uses DJIs read from said search to generate the query execution plans for the current query.