RANGE PREDICATE CANONIZATION FOR TRANSLATING A QUERY

• US 20120191697A1
• Filed: 01/25/2011
• Published: 07/26/2012
• Est. Priority Date: 01/25/2011
• Status: Active Grant

• Current Assignee: Hewlett Packard Enterprise Development LP (Hewlett-Packard Enterprise Company)
• Original Assignee: Hewlett-Packard Development Company, L.P. (HP Inc.)
• Inventors: SHERMAN, YUVAL, Fritchman, Barry Lynn
• Granted Patent: US 8,799,271 B2
• Time in Patent Office: Days
• US Class Current: 707/717
• CPC Class Codes: G06F 16/2452 Query translation

Abstract

A system and methods for implementing a materialized view for a query are provided. The query system includes a database that includes a base table. The base table can include a set of data. The query system also includes a materialized view associated with a portion of the set of data, and a MVQR component to translate each of a range predicate of the materialized view and a range predicate of a query into a canonical range representation (CRR) format in materialized view metadata and query metadata, respectively, to enable the query to search the materialized view.

Claims

1. A query system comprising:

a database comprising a base table, the base table comprising a set of data;

a materialized view associated with a portion of the set of data; and

a materialized view query rewrite (MVQR) component to translate each of a range predicate of the materialized view and a range predicate of a query into a canonical range representation (CRR) format to generate a materialized view metadata and a query metadata, respectively, to enable the query to search the materialized view.

2. The system of claim 1, wherein the MVQR component is further to compare the materialized view metadata and the query metadata to determine whether the query matches the materialized view to enable the query to search the materialized view.

3. The system of claim 2, wherein the MVQR component is further to enable search of the materialized view by the query in response to determining that the query is subsumed by the materialized view based on the materialized view metadata and the query metadata.

4. The system of claim 1, further comprising a query optimizer to choose from among a plurality of possible search plans for searching the base table for data associated with the range predicate of the query, the materialized view being included in the plurality of possible search plans by the query optimizer.

5. The system of claim 1, wherein the MVQR component is further to translate at least one of a data type and a check constraint associated with at least one of query and the materialized view into CRR format.

6. The system of claim 1, wherein the MVQR component is further to translate a plurality of range predicates associated with at least one of the query and the materialized view that are connected by a Boolean operator to a single range predicate in CRR format.

7. The system of claim 6, wherein the plurality of range predicates associated with at least one of the query and the materialized view comprises a NULL value.

8. The system of claim 6, wherein the plurality of range predicates associated with at least one of the query and the materialized view comprises a first range and a second range comprising one of overlapping and adjacent values with respect to each other, the MVQR component being further to merge the first range and the second range into a third range corresponding to the single range predicate, the third range comprising both the first range and the second range.

9. A non-transitory computer readable medium having computer readable instructions comprising:

a catalogue manager to create a materialized view associated with a portion of a set of data represented by a base table;

a compiler to receive a query programmed to search the base table; and

an MVQR component to translate a range predicate of the materialized view into a canonical range representation (CRR) format in materialized view metadata, to translate a range predicate of the query into a CRR format in query metadata, to compare the materialized view metadata and the query metadata, and to enable a search of the materialized view by the query if the query metadata is subsumed by the materialized view metadata.

10. The computer readable medium of claim 9, wherein the MVQR component is further to translate a plurality of range predicates associated with at least one of the query and the materialized view that are connected by a Boolean operator to a single range predicate in CRR format.

11. The computer readable medium of claim 9, wherein the MVQR component is further to translate at least one of a data type and a check constraint associated with at least one of query and the materialized view into CRR format.

12. The computer readable medium of claim 9, wherein the computer readable instructions further comprise a query optimizer to select a search plan from among a plurality of possible search plans for searching the base table for data associated with the range predicate of the query, the materialized view being included in the plurality of possible search plans by the query optimizer.

13. A method comprising:

generating a materialized view associated with a portion of a set of data represented by a base table;

translating a range predicate of the materialized view into a canonical range representation (CRR) format in materialized view metadata;

receiving a query programmed to search the base table;

translating a plurality of range predicates of the query that are connected by a Boolean operator into a single range-oriented predicate in a CRR format in query metadata;

comparing the materialized view metadata and the query metadata;

enabling a search of the materialized view by the query if the query metadata is subsumed by the materialized view metadata; and

searching the materialized view via the query in response to enabling the search.

14. The method of claim 13, wherein translating the range predicate associated with the materialized view comprises translating at least one of a data type and a check constraint associated with at least one of query and the materialized view into CRR format.

15. The method of claim 13, wherein translating the plurality of range predicates of the query comprises translating a first range and a second range comprising one of overlapping and adjacent values with respect to each other into a third range in CRR format, the third range comprising both the first range and the second range.