Enhanced control to users to populate a cache in a database system

• US 8,868,595 B2
• Filed: 04/19/2012
• Issued: 10/21/2014
• Est. Priority Date: 08/13/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Kumbi, Niranjan, Mishra, Amulya
• Primary Examiner(s): Jami, Hares
• Application Number:
  US13/450,490
• Publication Number:
  US 20120203797A1
• Time in Patent Office: 915 Days
• Field of Search: 707/758, 707/769, 707/770
• US Class Current: 707/769
• CPC Class Codes: G06F 12/0862 with prefetch
• CPC Class Codes: G06F 12/0866 for peripheral storage systems, e.g. disk cache
• CPC Class Codes: G06F 16/24552 Database cache management
• CPC Class Codes: G06F 2212/465 Structured object, e.g. database record

Abstract

Providing enhanced control to users to populate a cache in a database system. A user provides an explicit indication indicating the specific information (available from the data stored in a storage of the database system) to be populated in the cache, and the cache is populated with the corresponding information. The information in the cache (whether due to explicit indications or otherwise) can then be used to generate results of queries, without having to access the same information from the storage of the database system.

Claims

1. A method of retrieving data from a database system containing a cache, said method being performed in a client system, said method comprising:

sending, from said client system to said database system on a network, a first request and then a second request, said first request including a first query to be processed in said database system, wherein generation of results for said first query requires examination of data from a first set of tables in said database system,

said first request further including an explicit indication in the form of an identifier of a second table in said database system to instruct said database system to load data from said second table into said cache contained in said database system,

wherein said second request includes a second query to be processed in said database system, wherein said second query includes said identifier to cause data of said second table to be examined in generation of results for said second query,

wherein said database system is enabled to load into said cache said data from said second table in response to said explicit indication included in said first request such that at least some of said data is available in said cache by the time said second request is processed by said database system,

wherein generation of results for said first query does not require examination of data from said second table, but data from said second table is retrieved into said cache in response to said explicit indication received along with said first query in said first request; and

receiving, in said client system on said network, a result of performance of said first query and said second query from said database system in response to said sending said first request and said second request respectively,

wherein said client system and said database system are implemented as separate systems coupled by said network.

2. The method of claim 1, wherein said explicit indication is in the form of a comment in said first request,

said database system being designed to ignore comments in processing of queries but being designed to load data indicated in comments in response to receiving comments in corresponding requests,

wherein said comment specifies identifier of said second table, wherein said database system loads said data from said second table into said cache in response to said comment specifying said second table.

3. The method of claim 1, wherein each of said first query and said second query are specified according to SQL (structured query language), said SQL specifying a SELECT clause, a FROM clause and a WHERE clause,

said select clause specifying columns of a second set of tables to be provided as results of processing of the corresponding query, said FROM clause specifying a third set of tables to be examined and said WHERE clause specifying condition to be satisfied based on a fourth set of tables,

wherein in said first query, said identifier is not specified in any of said second set of tables, said third set of tables and said fourth set of tables of said first query,

wherein in said second query, said identifier is specified in at least one of said second set of tables, said third set of tables and said fourth set of tables of said second query causing data of said second table to examined for generation of results for said second query.

4. The method of claim 2, wherein said first query specifies a first column of a first table in said first set of tables,

wherein said comment indicates a relationship required to be satisfied between the rows of said second table and the values in said first column of said first table for inclusion of the rows in said data loaded into said cache,

wherein said data from said second table loaded into said cache comprises specific rows of said second table satisfying said relationship.

5. A computing system comprising:

a network;

a database system comprising;

a storage storing user data in a plurality of tables, with each table containing a plurality of rows to store corresponding user data;

a cache; and

a processor to perform queries received on said user data; and

a client system operable to;

send on said network, a first request and then a second request to said database system, said first request including a first query to be processed in said database system, wherein generation of results for said first query requires examination of data from a first set of tables of said plurality of tables,

said first request further including an explicit indication in the form of an identifier of a second table in said plurality of tables to instruct said database system to load data from said second table into said cache;

after sending of said explicit indication, send on said network to said database system, a second request specifying a second query to be processed in said database system, wherein said second query includes said identifier to cause data of said second table to be examined in generation of results for said second query; and

receive on said network, a result of performance of said first query and said second query from said database system in response to said sending said first request and said second request respectively,

wherein said processor in said database system is enabled to load into said cache said data from said second table in response to said explicit indication included in said first request such that at least some of said data is available in said cache by the time said second request is processed by said database system,

wherein generation of results for said first query does not require examination of data from said second table, but data from said second table is retrieved into said cache in response to said explicit indication received along with said first query in said first request,

wherein said client system and said database system are implemented as separate systems coupled by said network.

6. The computing system of claim 5, wherein said explicit indication is in the form of a comment in said first request,

said processor being designed to ignore comments in processing of said queries but being designed to load data indicated in comments in response to receiving comments in corresponding requests,

wherein said comment specifies identifier of said second table, wherein said processor loads said data from said second table into said cache in response to said comment specifying said second table.

7. The computing system of claim 5, wherein each of said first query and said second query are specified according to SQL (structured query language), said SQL specifying a SELECT clause, a PROM clause and a WHERE clause,

said select clause specifying columns of a second set of tables to be provided as results of processing of the corresponding query, said PROM clause specifying a third set of tables to be examined and said WHERE clause specifying condition to be satisfied based on a fourth set of tables,

wherein in said first query, said identifier is not specified in any of said second set of tables, said third set of tables and said fourth set of tables of said first query,

wherein in said second query, said identifier is specified in at least one of said second set of tables, said third set of tables and said fourth set of tables of said second query causing data of said second table to examined for generation of results for said second query.

8. The computing system of claim 6, wherein said first query specifies a first column of a first table in said first set of tables,

wherein said comment indicates a relationship required to be satisfied between the rows of said second table and the values in said first column of said first table for inclusion of the rows in said data loaded into said cache,

wherein said data from said second table loaded into said cache comprises specific rows of said second table satisfying said relationship.

9. A non-transitory computer readable storage medium storing one or more sequences of instructions for causing a client system to retrieve data from a database system containing a cache, wherein execution of said one or more sequences of instructions by one or more processors contained in said client system causes said client system to perform the actions of:

sending, from said client system to said database system on a network, a first request and then a second request, said first request including a first query to be processed in said database system, wherein generation of results for said first query requires examination of data from a first set of tables in said database system,

said first request further including an explicit indication in the form of an identifier of a second table in said database system to instruct said database system to load data from said second table into said cache contained in said database system,

wherein said second request includes a second query to be processed in said database system, wherein said second query includes said identifier to cause data of said second table to be examined in generation of results for said second query,

wherein said database system is enabled to load into said cache said data from said second table in response to said explicit indication included in said first request such that at least some of said data is available in said cache by the time said second request is processed by said database system,

wherein generation of results for said first query does not require examination of data from said second table, but data from said second table is retrieved into said cache in response to said explicit indication received along with said first query in said first request; and

receiving, in said client system on said network, a result of performance of said first query and said second query from said database system in response to said sending said first request and said second request respectively,

wherein said client system and said database system are implemented as separate systems coupled by said network.

10. The non-transitory computer readable storage medium of claim 9, wherein said explicit indication is in the form of a comment in said first request,

said database system being designed to ignore comments in processing of queries but being designed to load data indicated in comments in response to receiving comments in corresponding requests,

wherein said comment specifies identifier of said second table, wherein said database system loads said data from said second table into said cache in response to said comment specifying said second table.

11. The non-transitory computer readable storage medium of claim 9, wherein each of said first query and said second query are specified according to SQL (structured query language), said SQL specifying a SELECT clause, a FROM clause and a WHERE clause,

said select clause specifying columns of a second set of tables to be provided as results of processing of the corresponding query, said FROM clause specifying a third set of tables to be examined and said WHERE clause specifying condition to be satisfied based on a fourth set of tables,

wherein in said first query, said identifier is not specified in any of said second set of tables, said third set of tables and said fourth set of tables of said first query,

wherein in said second query, said identifier is specified in at least one of said second set of tables, said third set of tables and said fourth set of tables of said second query causing data of said second table to examined for generation of results for said second query.

12. The non-transitory computer readable storage medium of claim 10, wherein said first query specifies a first column of a first table in said first set of tables,

wherein said comment indicates a relationship required to be satisfied between the rows of said second table and the values in said first column of said first table for inclusion of the rows in said data loaded into said cache,

wherein said data from said second table loaded into said cache comprises specific rows of said second table satisfying said relationship.