Improving efficiency in processing queries directed to static data sets

• US 7,725,468 B2
• Filed: 05/22/2005
• Issued: 05/25/2010
• Est. Priority Date: 04/08/2005
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Mishra, Amulya
• Primary Examiner(s): Jalil; Neveen Abel
• Assistant Examiner(s): Chbouki; Tarek
• Application Number:
  US10/908,666
• Publication Number:
  US 20060230020A1
• Time in Patent Office: 1,829 Days
• Field of Search: 707/3, 707/1, 707/102, 707/100, 707/101, 707/2, 707/202, 707/4, 707/5, 707/6
• US Class Current: 707/736
• CPC Class Codes: G06F 16/245 Query processing

Abstract

Data is maintained indicating which conditions match which data items (e.g., rows) of a data set (e.g., table(s) in a database). When a query is later received, the maintained data is quickly examined to determine the matching data items, thereby enhancing the throughput performance in processing queries directed to the data set.

Claims

1. A method of processing queries directed to a static data set, said static data set containing a plurality of data items which are unlikely to change, said method being performed in a server, said method comprising:

storing said static data set in a data warehouse in the form of a plurality of tables, wherein each table contains a corresponding set of columns and a corresponding set of rows, wherein each data item is stored in one row of the table;

determining which of a set of conditions match which of said plurality of data items in said static data set by retrieving and inspecting said plurality of data items of said static data set stored in the form of said plurality of tables in said data warehouse,

wherein each of said set of conditions comprises an identifier of a column, a comparison operation, and a corresponding comparison value,

wherein a match for a condition is determined to be present when a data item retrieved from said data warehouse has a first value for the same column as the condition, and applying said comparison operation on said first value and said comparison value produces a true result;

maintaining a match data indicating which conditions match which data items in said static data set according to said determining,

wherein said match data indicates a corresponding set of data items matching each of said set of conditions based on a plurality of row identifiers, with each row identifier uniquely identifying a corresponding row in one of said plurality of tables and is not formed based on said plurality of data items,

wherein said match data comprises a plurality of entries, with each entry corresponding to a single condition and indicating a set of rows matching the corresponding condition using said row identifiers;

receiving a query containing a first condition, wherein said query is received after said maintaining and wherein said first condition is contained in said set of conditions;

examining said match data to determine a first set of data items matching said first condition received in said query, without having to inspect again said static data set stored in said data warehouse, wherein said first set of data items are contained in said static data set,

wherein said examining comprises identifying the single entry in said plurality of entries corresponding to said first condition and wherein the set of rows identified in the single entry being provided as said first set of data items;

retrieving only said first set of data items from said data warehouse according to said examining; and

generating a response to said query, said response containing said first set of data items retrieved from said data warehouse.

2. The method of claim 1, further comprising:

receiving a plurality of queries directed to said static data set in said data warehouse over a duration prior to receiving said query, wherein each of said plurality of queries contains a corresponding conditions; and

identifying said set of conditions as commonly occurring conditions in said plurality of queries,

wherein each of said set of conditions occurs more number of times in said plurality of queries than conditions not included in said set of conditions, whereby commonly occurring conditions are included in said match data,

whereby said determining determines matching data items for each of said commonly occurring conditions.

11. The computer readable storage medium of claim 2, further comprising:

receiving a plurality of queries directed to said static data set in said data warehouse over a duration prior to receiving said query, wherein each of said plurality of queries contains a corresponding conditions; and

identifying said set of conditions as commonly occurring conditions in said plurality of queries,

wherein each of said set of conditions occurs more number of times in said plurality of queries than conditions not included in said set of conditions, whereby commonly occurring conditions are included in said match data,

whereby said determining determines matching data items for each of said commonly occurring conditions.

3. A computer readable storage medium carrying one or more sequences of instructions causing a server to process queries directed to a static data set already stored in the form of a plurality of tables in a data warehouse, wherein each table contains a corresponding set of columns and a corresponding set of rows, wherein each data item is stored in one row of the table, wherein execution of said one or more sequences of instructions by one or more processors contained in said server causes said server to perform the actions of:

receiving a plurality of queries directed to said static data set stored in said data warehouse, wherein each of said plurality of queries contains a set of conditions, wherein processing of said plurality of queries requires retrieval of at least some portions of said static data set matching said set of conditions from said data warehouse;

determining which of a plurality of conditions match which of said data items in said static data set by retrieving and inspecting said static data set stored in the form of said plurality of tables in said data warehouse,

wherein a match for a condition is determined to be present when a data item retrieved from said data warehouse has a first value for the same column as the condition, and applying said comparison operation on said first value and said comparison value produces a true result;

maintaining a match data indicating which of said plurality of conditions match which data items in said static data set according to said determining,

wherein said match data indicates a corresponding set of data items matching each of said set of conditions based on a plurality of row identifiers, with each row identifier uniquely identifying a corresponding row in one of said plurality of tables and is not formed based on said plurality of data items,

wherein said match data comprises a plurality of entries, with each entry corresponding to a single condition and indicating a set of rows matching the corresponding condition using said row identifiers;

receiving a query containing a first condition included in said plurality of conditions;

examining said match data to determine a first set of data items contained in said static data set and matching said first condition received in said query, without having to inspect again said static data set stored in said data warehouse, wherein said examining is performed in response to receiving said query,

wherein said examining comprises identifying the single entry in said plurality of entries corresponding to said first condition and wherein the set of rows identified in the single entry being provided as said first set of data items;

retrieving only said first set of data items from said data warehouse according to said examining; and

generating a response to said query, said response containing said first set of data items,

wherein said identifying, said determining and said maintaining are performed before said query is received such that said response can be generated quickly after receiving said query.

4. The computer readable storage medium of claim 3, wherein said match data indicates a corresponding set of data items matching each of said plurality of conditions based on a plurality of row identifiers, with each row identifier uniquely identifying a corresponding row in one of said plurality of tables and is not formed based on said plurality of data items.

5. A system for processing a query directed to a static data set stored in the form of a plurality of tables in on a storage server, wherein each table contains a corresponding set of columns and a corresponding set of rows, wherein each data item is stored in one row of the table, said static data set containing a plurality of data items which are unlikely to change, wherein each data item is identified by a corresponding one of a plurality of identifiers, said system comprising:

means for receiving a plurality of queries directed to said static data set stored in said storage server, wherein each of said plurality of queries contains a set of conditions, wherein processing of said plurality of queries requires retrieval of at least some portions of said static data set matching said set of conditions from said storage server;

means for determining which of a plurality of conditions match which of said data items in said static data set by examining retrieving and inspecting said static data set stored in the form of said plurality of tables in said data warehouse,

wherein a match for a condition is determined to be present when a data item retrieved from said data warehouse has a first value for the same column as the condition, and applying said comparison operation on said first value and said comparison value produces a true result;

means for maintaining a match data external to said static data set,

wherein said match data indicates a corresponding set of data items matching each of said set of conditions based on a plurality of row identifiers, with each row identifier uniquely identifying a corresponding row in one of said plurality of tables and is not formed based on said plurality of data items,

wherein said match data comprises a plurality of entries, with each entry corresponding to a single condition and indicating a set of rows matching the corresponding condition using said row identifiers;

means for receiving said query containing a first condition included in said plurality of conditions, wherein said query is received after said maintaining;

means for examining said match data to determine a first set of individual identifiers matching said first condition received in said query, without having to inspect again said static data set stored in said data warehouse, wherein said first set of individual identifiers are contained in said plurality of identifiers,

wherein said examining comprises identifying the single entry in said plurality of entries corresponding to said first condition and wherein the set of rows identified in the single entry being provided as said first set of data items;

means for retrieving only a first set of data items from said storage server, wherein said first set of data items are identified by said first set of individual identifiers; and

means for generating a response to said query, said response containing said first set of data items identified by said first set of individual identifiers.

6. The system of claim 5, wherein said match data indicates a corresponding set of data items matching each of said plurality of conditions based on a plurality of row identifiers, with each row identifier uniquely identifying a corresponding row in one of said plurality of tables and is not formed based on said plurality of data items.

7. The system of claim 6, wherein said plurality of queries are received over a duration.

8. The system of claim 6, wherein said storage server comprises a data warehouse and said system comprises a warehouse server designed to access said data warehouse.

12. The system of claim 5, further comprising:

means for receiving a plurality of queries directed to said static data set in said data warehouse over a duration prior to receiving said query, wherein each of said plurality of queries contains a corresponding conditions; and

means for identifying said set of conditions as commonly occurring conditions in said plurality of queries,

wherein each of said set of conditions occurs more number of times in said plurality of queries than conditions not included in said set of conditions, whereby commonly occurring conditions are included in said match data,

whereby said means for determining determines matching data items for each of said commonly occurring conditions.

9. A computing system comprising:

a database storage to store a data set containing a plurality of data items;

a database client to send a plurality of transaction requests to be performed on said data set;

a database server to receive each of said plurality of transaction requests and to accordingly alter said data set stored in said data storage;

a data warehouse to store said data set in the form of a static data set in a plurality of tables after said data set is determined to not require further alterations, wherein each table contains a corresponding set of columns and a corresponding set of rows, wherein each data item is stored in one row of the table;

a warehouse client to send a plurality of queries to be performed on said static data set; and

a warehouse server being operable to;

receive a subset of said plurality of queries containing a corresponding set of conditions, wherein processing of said subset of said plurality of queries requires retrieval of at least some portions of said static data set matching said set of conditions from said data warehouse;

determine which of a plurality of conditions match which of said plurality of data items in said static data set by examining said static data set in said data warehouse, wherein a match for a condition is determined to be present when a data item retrieved from said data warehouse has a first value for the same column as the condition, and applying said comparison operation on said first value and said comparison value produces a true result;

maintain a match data indicating which of said plurality of conditions match which of said plurality of data items in said static data set,

wherein said match data indicates a corresponding set of data items matching each of said set of conditions based on a plurality of row identifiers, with each row identifier uniquely identifying a corresponding row in one of said plurality of tables and is not formed based on said plurality of data items,

wherein said match data comprises a plurality of entries, with each entry corresponding to a single condition and indicating a set of rows matching the corresponding condition using said row identifiers;

receive a query contained in said plurality of queries, wherein said query contains a first condition included in said plurality of conditions;

examine said match data to determine, without having to inspect again said static data stored in said data warehouse, a first set of data items contained in said static data set and matching said first condition,

wherein said examine comprises identifying the single entry in said plurality of entries corresponding to said first condition and wherein the set of rows identified in the single entry being provided as said first set of data items;

retrieve only said first set of data items in said static data set from said data warehouse; and

generate a response to said query, said response containing said first set of data items.

10. The computing system of claim 9, wherein said warehouse server is implemented external to said data warehouse as a separate unit.

13. The computing system of claim 9, wherein said warehouse server is further operable to:

receive a plurality of queries directed to said static data set in said data warehouse over a duration prior to receiving said query, wherein each of said plurality of queries contains a corresponding conditions; and

identify said set of conditions as commonly occurring conditions in said plurality of queries,

wherein each of said set of conditions occurs more number of times in said plurality of queries than conditions not included in said set of conditions, whereby commonly occurring conditions are included in said match data,

whereby said determining determines matching data items for each of said commonly occurring conditions.