Method and process of query optimization to a given environment via specific abstraction layer domain knowledge
View Patent ↗Methods and apparatus for optimizing an abstract query are disclosed. Embodiments of the present invention allow an abstract database to optimize an abstract query composed from a set of logical fields. Each logical field includes a field name and an access method used to map the logical field to an underlying physical representation of the data. After a user composes an abstract query and submits it for execution, a runtime component and optimization component may be configured to resolve the abstract query to a physical query of the underlying physical data source using an access method associated with each logical field. In addition, as the runtime component builds a query consistent with the schema of the underlying database, an optimizing component may modify the query using any suitable optimization techniques. Different optimization techniques include optimizations based on logical field metadata, optimizations based on the underlying physical data repositories referenced by an access method and optimizations based on the relationships between logical fields or underlying data structures.
1. A method for accessing data in an environment having multiple data repositories, comprising:
providing a query specification defining a data abstraction model abstracting at least one relational database, wherein the query specification defines a plurality of logical fields used to compose an abstract query independently from a schema of the at least one relational database, wherein the definition for each logical field specifies at least an access method for accessing data in the at least one relational database, the access methods being selected from at least two different access method types, wherein at least a first logical field provides query optimization data for optimizing at least one of an abstract query composed using the logical fields of the data abstraction model and a corresponding resolved query generated from the abstract query, and wherein the query optimization data includes information related to a relationship between the first logical field and a second logical field included in the data abstraction model;
receiving, from a requesting entity, an abstract query composed from one or more of the logical fields, wherein the received abstract query includes the first logical field and the second logical field;
generating, from the received abstract query, a resolved query consistent with the schema of the at least one relational database to the access method defined for each logical field in the received abstract query; and
optimizing the resolved query using the query optimization data provided for the first logical field and the second logical field;
executing the optimized query to retrieve a set of data from the at least one relational database consistent with the abstract query; and
presenting the set of data to the requesting entity.
2. The method of claim 1 , wherein the resolved query is an SQL query, and wherein the query optimization data is used to optimize the SQL query.
3. The method of claim 2 , wherein the query optimization data includes row optimization data specifying a number of rows expected to be returned from executing the resolved query.
4. The method of claim 2 , wherein the query optimization data is used to select between materializing a derived table in the SQL query generated from the abstract query and optimizing the SQL query to remove the derived table from the SQL query.
5. The method of claim 1 , wherein the query optimization data includes information related to an underlying system used to store the data referenced by the first logical field, the information including at least one of product, version, release and service pack information of the at least one relational database.
6. The method of claim 1 , wherein the optimization data in the first logical field references an external hierarchical data structure and the query optimization data includes information related to the hierarchical data structure.
7. The method of claim 1 , wherein generating, from the received abstract query, the resolved query consistent with the schema of the at least one relational database of the data comprises generating, for each logical field in the abstract query, a query contribution according to the access method defined for each logical field, and optimizing the query contribution using the query optimization data provided for the first logical field.
8. The method of claim 7 , wherein the resolved query is an SQL query and wherein the query optimization data is used to optimize the SQL query.
9. A computer-readable storage medium containing a program which, when executed by a processor, performs operations for providing access to data in an environment having multiple data repositories, comprising,
providing a query specification defining a data abstraction model abstracting at least one relational database, wherein the query specification defines a plurality of logical fields used to compose an abstract query independently from a schema of the at least one relational database, wherein the definition for each logical field specifies at least an access method for accessing data in the at least one relational database, the access methods being selected from at least two different access method types, wherein at least a first logical field provides query optimization data for optimizing at least one of an abstract query composed using the logical fields defined in the query specification and a corresponding resolved query generated from the abstract query, and wherein the query optimization data includes information related to a relationship between the first logical field and a second logical field included in the data abstraction model;
receiving, from a requesting entity, an abstract query composed from one or more of the logical fields, wherein the received abstract query includes the first logical field and the second logical field;
generating, from the received abstract query, a resolved query consistent with the schema of the at least one relational database to the access method defined each logical field in the received abstract query; and
optimizing the resolved query using the query optimization data provided for the first logical field and the second logical field;
executing the optimized query to retrieve a set of data from the at least one relational database consistent with the abstract query; and
presenting the set of data to the requesting entity.
10. The computer-readable storage medium of claim 9 , wherein the resolved query is an SQL query, and wherein the query optimization data is used to optimize the SQL query.
11. The computer-readable storage medium of claim 10 , wherein the query optimization data includes row optimization data specifying a number of rows expected to be returned from executing the resolved query.
12. The computer-readable storage medium of claim 10 , wherein the query optimization data is used to select between materializing a derived table in the SQL query generated from the abstract query and optimizing the SQL query to remove the derived table from the SQL query.
13. The computer-readable storage medium of claim 9 , wherein the optimization data includes information related to an underlying system used to store the data referenced by the first logical field, the information including at least one of product, version, release and service pack information of the at least one relational database.
14. The computer-readable storage medium of claim 9 , wherein the optimization data in the first logical field references an external hierarchical data structure and the optimization data includes information related to the hierarchical. data structure.
15. The computer-readable storage medium of claim 9 , wherein generating, from the received abstract query, the resolved query consistent with the schema of the at least one relational database of the data comprises generating, for each logical field in the abstract query, a query contribution according to the access method defined for each logical field, and optimizing the query contribution using the query optimization data provided for the first logical field.