Methods and systems for optimizing queries in a database system
First Claim
1. A method at a host organization for optimizing database queries in a database system of the host organization, the database system having one or more processors coupled with memory and including both a relational data store and a non-relational data store therein, the method comprising:
- operating the database system within the host organization;
receiving a request at the host organization, the request specifying data for retrieval from the database system;
retrieving, based on the request received at the host organization, one or more locations of the data for retrieval;
generating, at the host organization, a database query based on the request, wherein the database query specifies a plurality of data elements for retrieval, the plurality of data elements including one or more data elements residing within the non-relational data store and one or more other data elements residing within the relational data store;
optimizing the database query to include at least a sub-query or a pre-query;
executing the optimized database query against the database system to retrieve the data;
wherein optimizing the database query comprises;
(i) identifying via a first sub-query to a table within relational data store, the first portion of the data for retrieval, (ii) identifying via a second sub-query to the non-relational data store, the second portion of the data for retrieval, (iii) identifying a data delta between the first sub-query that identifies the first portion of the data retrieved within the relational data store and the second sub-query that identifies the second portion of the data retrieved within the non-relational data store, and (iv) executing a third sub-query replicating missing data from the relational data store to the non-relational data store according to the data delta;
wherein the database query comprises a join operation performed by initiating non-relational database queries on the non-relational data store where one or more foreign key parents are stored in the relational data store; and
wherein the join operation comprises a plurality of sub-queries which are generated based on query optimizations available via an optimizer agent of the host organization, the query optimizations selected from the group comprising;
a specified ordering for the plurality of sub-queries;
a target data store for execution of a corresponding sub-query;
one or more pre-query assessments based on the data for retrieval;
a replication order from the relational data store to the non-relational data store; and
an in-memory join operation specifying at least one or more of the plurality of data elements for retrieval from each of the relational data store and the non-relational data store and placed into memory accessible to the optimizer agent and a corresponding sub-query to retrieve the at least one or more of the plurality of data elements from the memory accessible to the optimizer agent in fulfillment of the request.
1 Assignment
0 Petitions
Accused Products
Abstract
Methods and systems for optimizing queries in a multi-tenant store are described. In one embodiment, such a method includes retrieving data from a multi-tenant database system having a relational data store and a non-relational data store, receiving a request specifying data to be retrieved, retrieving one or more locations of the data to be retrieved, generating a database query based on the request, in which the database query specifies a plurality of data elements to be retrieved, the plurality of data elements including one or more data elements residing within the non-relational data store and one or more other data elements residing within the relational data store, generating an optimized database query having an optimized query syntax that is distinct from a query syntax of the database query, and executing the optimized database query against the multi-tenant database system to retrieve the data.
184 Citations
16 Claims
-
1. A method at a host organization for optimizing database queries in a database system of the host organization, the database system having one or more processors coupled with memory and including both a relational data store and a non-relational data store therein, the method comprising:
-
operating the database system within the host organization; receiving a request at the host organization, the request specifying data for retrieval from the database system; retrieving, based on the request received at the host organization, one or more locations of the data for retrieval; generating, at the host organization, a database query based on the request, wherein the database query specifies a plurality of data elements for retrieval, the plurality of data elements including one or more data elements residing within the non-relational data store and one or more other data elements residing within the relational data store; optimizing the database query to include at least a sub-query or a pre-query; executing the optimized database query against the database system to retrieve the data; wherein optimizing the database query comprises;
(i) identifying via a first sub-query to a table within relational data store, the first portion of the data for retrieval, (ii) identifying via a second sub-query to the non-relational data store, the second portion of the data for retrieval, (iii) identifying a data delta between the first sub-query that identifies the first portion of the data retrieved within the relational data store and the second sub-query that identifies the second portion of the data retrieved within the non-relational data store, and (iv) executing a third sub-query replicating missing data from the relational data store to the non-relational data store according to the data delta;wherein the database query comprises a join operation performed by initiating non-relational database queries on the non-relational data store where one or more foreign key parents are stored in the relational data store; and wherein the join operation comprises a plurality of sub-queries which are generated based on query optimizations available via an optimizer agent of the host organization, the query optimizations selected from the group comprising; a specified ordering for the plurality of sub-queries; a target data store for execution of a corresponding sub-query; one or more pre-query assessments based on the data for retrieval; a replication order from the relational data store to the non-relational data store; and an in-memory join operation specifying at least one or more of the plurality of data elements for retrieval from each of the relational data store and the non-relational data store and placed into memory accessible to the optimizer agent and a corresponding sub-query to retrieve the at least one or more of the plurality of data elements from the memory accessible to the optimizer agent in fulfillment of the request. - View Dependent Claims (2, 3, 4, 5, 6, 7, 8, 9, 10, 11)
-
-
12. A non-transitory computer readable storage medium having instructions stored thereon that, when executed by processor in a host system, the instructions cause the host to perform operations comprising:
-
retrieving, based on the request via the host system, one or more locations of the data for retrieval; generating, at the host system, a database query based on the request, wherein the database query specifies a plurality of data elements for retrieval, the plurality of data elements including one or more data elements residing within a non-relational data store and one or more other data elements residing within a relational data store; optimizing the database query to include at least a sub-query or a pre-query; executing the optimized database query against the database system to retrieve the data; wherein optimizing the database query comprises;
(i) identifying via a first sub-query to a table within relational data store, the first portion of the data for retrieval, (ii) identifying via a second sub-query to the non-relational data store, the second portion of the data for retrieval, (iii) identifying a data delta between the first sub-query that identifies the first portion of the data retrieved within the relational data store and the second sub-query that identifies the second portion of the data retrieved within the non-relational data store, and (iv) executing a third sub-query replicating missing data from the relational data store to the non-relational data store according to the data delta;wherein the database query comprises a join operation performed by initiating non-relational database queries on the non-relational data store where one or more foreign key parents are stored in the relational data store; and wherein the join operation comprises a plurality of sub-queries which are generated based on query optimizations available via an optimizer agent of the host organization, the query optimizations selected from the group comprising; a specified ordering for the plurality of sub-queries; a target data store for execution of a corresponding sub-query; one or more pre-query assessments based on the data for retrieval; a replication order from the relational data store to the non-relational data store; and an in-memory join operation specifying at least one or more of the plurality of data elements for retrieval from each of the relational data store and the non-relational data store and placed into memory accessible to the optimizer agent and a corresponding sub-query to retrieve the at least one or more of the plurality of data elements from the memory accessible to the optimizer agent in fulfillment of the request.
-
-
13. A system comprising:
-
a processor and a memory; a communications interface to a database system having a relational data store and a non-relational data store implemented therein; a request processor to receive a request at the host system for a database system, the request specifying data for retrieval from the database system; a customer schema processor to retrieve, based on the request, one or more locations of the data for retrieval; a query layer agent to generate a database query based on the request, wherein the database query specifies a plurality of data elements for retrieval, the plurality of data elements including one or more data elements residing within a non-relational data store and one or more other data elements residing within a relational data store; a query optimizer to optimize the database query to include at least a sub-query or a pre-query; wherein the query layer agent to execute the optimized database query against the database system to retrieve the data; wherein the query optimizer to further;
(i) identify via a first sub-query to a table within relational data store, the first portion of the data for retrieval, (ii) identify via a second sub-query to the non-relational data store, the second portion of the data for retrieval, (iii) identify a data delta between the first sub-query that identifies the first portion of the data retrieved within the relational data store and the second sub-query that identifies the second portion of the data retrieved within the non-relational data store, and (iv) execute a third sub-query to replicate missing data from the relational data store to the non-relational data store according to the data delta;wherein the database query comprises a join operation performed by initiating non-relational database queries on the non-relational data store where one or more foreign key parents are stored in the relational data store; and wherein the join operation comprises a plurality of sub-queries which are generated based on query optimizations available via an optimizer agent of the host organization, the query optimizations selected from the group comprising; a specified ordering for the plurality of sub-queries; a target data store for execution of a corresponding sub-query; one or more pre-query assessments based on the data for retrieval; a replication order from the relational data store to the non-relational data store; and an in-memory join operation specifying at least one or more of the plurality of data elements for retrieval from each of the relational data store and the non-relational data store and placed into memory accessible to the optimizer agent and a corresponding sub-query to retrieve the at least one or more of the plurality of data elements from the memory accessible to the optimizer agent in fulfillment of the request. - View Dependent Claims (14, 15, 16)
-
Specification