Recommending materialized views for queries with multiple instances of same table

• US 20070083489A1
• Filed: 03/02/2006
• Published: 04/12/2007
• Est. Priority Date: 10/07/2005
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Lawande, Shilpa
• Granted Patent: US 7,840,553 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/24539 using cached or materialised query results
• CPC Class Codes: G06F 16/2456 Join operations

Abstract

Systems, methods, computer-readable media and other embodiments are provided that relate to processing database queries. In one embodiment, a query analysis logic can be configured to analyze a query to identify multiple instances of the same table. Instance numbering for the multiple table instances can then be performed using the query structure such that equivalent queries will likely get the same instance number assignments to improve materialized view recommendations for queries.

Claims

1. A computer system, comprising:

a query analysis logic configured to analyze a database query and identify multiple instances of a table where an instance of a table includes the table being part of a join operation; and

an instance numbering logic configured to assign an instance number to each of the multiple instances of the table in the query based on a structure of the join operation such that equivalent join operations from different queries that do not match syntactically will be assigned the same instance numbers such that a common materialized view can be recommended for both of the different queries.

3. The system of claim 1, where the signature generating logic is configured to combine the parameters of the join operation using an XOR function to generate the signature.

4. The system of claim 1 where the signature generating logic and the hash logic are configured to generate the same hash value for equivalent queries where the equivalent queries do not have matching text.

2. A computer program product comprising:

a query analysis logic configured to analyze a database query and identify multiple instances of a table where an instance of a table includes the table being part of a join operation;

a signature generating logic configured to generate a signature for each join operation based on parameters of the join operation and to associate the signature to the instance of the table;

a hash logic configured to generate a hash value from each signature; and

an instance numbering logic configured to order the hash values and assign instance numbers to each instance of the table based on the order of the hash values, where the instance numbers are used to recommend a materialized view for the database query.

5. A computer implemented method, comprising:

receiving a database query;

parsing the database query to identify multiple instances of a table and associated join operations, where a join operation includes a table instance and parameters that define the join operation;

for each instance of the table associated with a join operation, generating a signature value for the join operation using values associated to selected parameters from the join operation where the signature value reflects a join graph produced by the join operation;

assigning an instance number to each of the multiple instances of the table based on the signature value associated to each of the instances; and

recommending a materialized view for the database query using the instance numbers assigned to the database query.

6. The method of claim 5, where the signature value is generated by combining the values associated to the selected parameters using an XOR function.

7. The method of claim 5, where the parameters that define the join operation include at least one of:

an object number assigned to the table instance, object numbers assigned to other tables that are joined to the table instance, column identifiers of one or more columns listed in the join operation, and column numbers of the one or more columns.

8. The method of claim 5 where generating the signature value includes generating a hash value from the signature value using a hash function where the hash value becomes the signature value.

9. The method of claim 5 further including

receiving a plurality of database queries that are part of a workload;

and

for each database query having multiple instances of the table, assigning the same instance numbers to equivalent joint operations that include the instance of the table such that the same materialized view can be recommended to two or more of the database queries that do not match syntactically but have equivalent join operations.

10. A computer-readable medium storing processor executable instructions operable to perform a method, the method comprising:

analyzing a query to determine if the query contains multiple instances of a table;

assigning instance numbers to the multiple instances of the table based on signatures of join operations that contain an instance of the table, where a signature is based on selected parameters from the join operation and where the same signature is generated for equivalent join operations to cause a common materialized view to be recommended.

11. A system, comprising:

means for analyzing a query to determine if the query contains multiple instances of a table;

means for assigning instance numbers to the multiple instances of the table based on signatures of join operations that contain an instance of the table, where a signature is based on selected parameters from the join operation and where the same signature is generated for equivalent join operations to cause a common materialized view to be recommended.