SQL meaning decision maker for materialized view selection

Abstract

A system (100) and method (200) for managing queries (134) include: receiving a new query (210) including a first plurality of joins; accessing a filtered view of a database from memory, the filtered view being filtered by a previously received query according to a filter represented by a second plurality of joins (220), at least one of the first plurality of joins or the second plurality of joins including at least one NULL value; determining that the filter of the new query implies a filter of the previously received query (230); and executing the new query (240) using the filtered view of the previously received query based on the determination that the filter of the new query implies a filter of the previously received query.

Description

[0001] Cross-referencing of related patent applications

[0002] This application is a continuation of U.S. Patent Application No. 17 / 392,728, filed August 3, 2021, which claims the benefit of U.S. Provisional Patent Application No. 63 / 073,746, filed September 2, 2020, the disclosures of which are hereby incorporated herein by reference. Background Technology

[0003] This technology generally relates to a method and system for accelerating SQL query processing using materialized views.

[0004] Typically, materialized views of a dataset can speed up query processing compared to standard views of the dataset. Materialized views may already be stored in a table and may have been indexed before new queries are received. If a materialized view can be used to process a new query, processing time can be significantly reduced.

[0005] Determining whether a materialized view can be used to process a new query requires determining whether the filtered data of a given stored materialized view contains all the information needed to correctly and completely respond to the query. This can be achieved by determining whether the new query can be rewritten as the previous query that generated the materialized view, or in other words, whether the new query satisfies or implies the previous query. This determination can be made using SAT (Satisfaction) or UNSAT (Dissatisfaction) checking algorithms. However, traditional SAT and UNSAT checking algorithms do not support SQL queries. This is because SQL supports NULL values. The uncertainty of NULL values ​​adds a layer of uncertainty, making it impossible to determine whether one query implies another using known SAT and UNSAT checking algorithms. Furthermore, SAT and UNSAT checking algorithms have high computational complexity, making their application unattractive in the context of database query rewriting and optimization. Summary of the Invention

[0006] One aspect of this disclosure relates to a method comprising: receiving a new query including a first plurality of joins by one or more processors; accessing a filtered view of a database from memory by the one or more processors, wherein the filtered view is filtered by a previously received query according to filters represented by a second plurality of joins, wherein at least one of the first plurality of joins or the second plurality of joins includes at least one NULL value; determining that the filters of the new query imply filters of the previously received query; and executing the new query using the filtered view of the previously received query based on the determination that the filters of the new query imply filters of the previously received query. The advantage of this method is that it enables the rapid identification of opportunities to reuse previously computed filtered views in the evaluation of new queries, thereby revealing optimization opportunities that lead to faster execution of the new query.

[0007] In some examples, the filters that determine the new query imply the filters of the previously received query may include: rewriting the new query and the previously received query to share a common set of variables; rewriting the new query in disjunctive normal form; rewriting the previously received query in conjunctive normal form; and determining that each item of the rewritten new query implies each item of the rewritten previously received query, wherein the determination of the new query implying the previously received query is based on each item of the rewritten new query implying each item of the rewritten previously received query.

[0008] In some examples, the previously received query or the new query may include a grouping operation, and the filter for determining the new query implies that the filter for the previously received query may include: removing items in the previously received query or the new query that refer to columns that are not keys of the grouping operation before rewriting the new query in disjunctive normal form and the previously received query in conjunctive normal form.

[0009] In some examples, the filters used to determine the new query imply that the filters used to determine the previously received query may include: removing structurally equivalent items in the previously received query and the new query before rewriting the new query in disjunctive normal form and the previously received query in conjunctive normal form.

[0010] In some examples, the method may further include: after removing structurally equivalent items in the previously received query and the new query, determining the filter of the new query that implies the filter of the previously received query, in part based on the fact that no remaining items refer to columns that are not grouping keys.

[0011] In some examples, determining whether a filter for a new query implies a filter for a previously received query may include: rewriting the new query and the previously received query into predicates that establish the new query as true and the previously received query as false or NULL; rewriting the predicates in disjunctive normal form; and determining that each term of the rewritten predicate is incompatible with itself. The determination of whether a filter for a new query implies a filter for a previously received query may be based on the incompatibility of each term of the rewritten predicate with itself.

[0012] In some examples, the method may further include: simplifying at least a portion of the predicate that represents the previously received query as NULL before rewriting the predicate in disjunctive normal form.

[0013] In some examples, the method may further include: receiving a second new query including a third plurality of joins by one or more processors; accessing a filtered view of a previously received query from memory by the one or more processors, wherein at least one of the second plurality of joins or the third plurality of joins includes at least one NULL value; determining by the one or more processors that the filter of the second new query does not imply the filter of the previously received query; and executing the second new query on the database in response to determining that the filter of the second new query does not imply the filter of the previously received query.

[0014] In some examples, determining the filter for the second new query does not imply that the filter for the previously received query may include: rewriting the second new query and the previously received query into predicates that establish the second new query as true and the previously received query as false or NULL; rewriting the predicates in disjunctive normal form; and determining that at least one item of the rewritten predicate is not incompatible with itself, wherein determining the filter for the second new query does not imply that the filter for the previously received query is incompatible with itself based on the at least one item of the rewritten predicate.

[0015] In some examples, determining that the filter for the second new query does not imply that the filter for the previously received query may include: rewriting the second new query and the previously received query to share a common set of variables; rewriting the second new query in disjunctive normal form; rewriting the previously received query in conjunctive normal form; and determining that at least one item of the rewritten second new query does not imply at least one item of the rewritten previously received query, wherein the determination that the new query does not imply the previously received query is based on the fact that at least one item of the rewritten second new query does not imply at least one item of the rewritten previously received query.

[0016] Another aspect of this disclosure relates to a system including a memory for storing instructions and one or more processors coupled to the memory. The one or more processors may be configured to: execute the stored instructions to receive a new query including a first plurality of joins; access a filtered view of a database filtered by a previously received query according to a filter, wherein the filtered view is represented by a second plurality of joins, and wherein at least one of the first plurality of joins or the second plurality of joins includes at least one NULL value; determine whether the filter of the new query implies a filter of the previously received query; and execute the new query using the filtered view of the database based on the determination that the filter of the new query implies a filter of the previously received query.

[0017] In some examples, the one or more processors may be configured to: execute stored instructions to rewrite a new query and a previously received query to share a common set of variables; rewrite the new query in disjunctive normal form, rewrite the previously received query in conjunctive normal form, and determine that each item of the rewritten new query implies each item of the rewritten previously received query, wherein the determination that the new query implies the previously received query is based on each item of the rewritten new query implying each item of the rewritten previously received query.

[0018] In some examples, the previously received query or the new query includes grouping operations. Filters for determining the new query that imply filters for the previously received query may include: removing items from the previously received query or the new query that refer to columns that are not keys for grouping operations before rewriting the new query in disjunctive normal form and the previously received query in conjunctive normal form.

[0019] In some examples, the one or more processors may be configured to execute stored instructions to remove structurally equivalent items in the previously received query and the new query before rewriting the new query in disjunctive normal form and the previously received query in conjunctive normal form.

[0020] In some examples, the one or more processors may be configured to execute stored instructions to: rewrite the new query and a previously received query into predicates that establish the new query as true and the previously received query as false or NULL, to rewrite the predicates in disjunctive normal form, and to determine that each item of the rewritten predicate is incompatible with itself. The filter for the new query implies that the determination of the filter for the previously received query may be based on the incompatibility of each item of the rewritten predicate with itself.

[0021] In some examples, the one or more processors may be configured to execute stored instructions to simplify at least a portion of a predicate that represents a previously received query as NULL before rewriting the predicate in disjunctive paradigm.

[0022] In some examples, the one or more processors may be configured to: execute stored instructions to receive a second new query including a third plurality of joins by the one or more processors; access a filtered view of a previously received query from memory by the one or more processors, wherein at least one of the second plurality of joins or the third plurality of joins includes at least one NULL value; determine by the one or more processors that the filter of the second new query does not imply the filter of the previously received query; and execute a new query on the database in response to the determination that the filter of the new query does not imply the filter of the previously received query.

[0023] In some examples, the one or more processors may be configured to: execute stored instructions to rewrite a second new query and a previously received query into predicates that establish the second new query as true and the previously received query as false or NULL, to rewrite the predicates in disjunctive normal form, and to determine that at least one term of the rewritten predicate is not incompatible with itself. The filtering of the second new query does not imply that the determination of the filter for the previously received query can be based on the fact that at least one term of the rewritten predicate is not incompatible with itself.

[0024] In some examples, the one or more processors may be configured to: execute stored instructions to rewrite a second new query and a previously received query to share a common set of variables; rewrite the second new query in disjunctive normal form; rewrite the previously received query in conjunctive normal form; and determine that at least one item of the rewritten second new query does not imply at least one item of the rewritten previously received query. The determination that the new query does not imply the previously received query may be based on the premise that at least one item of the rewritten second new query does not imply at least one item of the rewritten previously received query.

[0025] Another aspect of this disclosure relates to a data center comprising: a plurality of servers for storing a database and at least one materialized view of the database, wherein the materialized view is filtered according to a previously received query, and any of the example systems described herein.

[0026] In some examples, multiple servers can be configured to: provide access to a materialized view of the database for execution of the new query in response to the determination of a filter for a previously received query that implies a filter for the new query; and provide access to the database for execution of the new query in response to the determination of a filter for a previously received query that does not imply a filter for the new query. Attached Figure Description

[0027] Figure 1 This is a block diagram illustrating an example system according to aspects of this disclosure.

[0028] Figure 2This is a flowchart illustrating an example routine according to an aspect of this disclosure.

[0029] Figure 3 It is a diagram. Figure 2 The flowchart of the example subroutine of the routine.

[0030] Figure 4 It is a diagram. Figure 2 The flowchart of the alternative example subroutine for the routine. Detailed Implementation

[0031] This disclosure provides a system and method capable of determining whether a new query implies a previous query, even if at least one of the new or previous queries includes at least one NULL value. To make this determination, the system rewrites each of the new and previous queries into smaller pieces that are easier to compare with each other. This makes the processing items for each query containing the NULL operator easier to handle, thus enabling an overall determination.

[0032] Figure 1 This is a block diagram illustrating an example system 100 including a computing environment for processing queries. System 100 may include one or more computing devices 101 connected to clients 162, 164 via network connection 150. The computing devices may include one or more processors 110, servers, fragments, units, etc. It should be understood that system 100 may include any number of computing devices 101 spanning a distributed network with any number of data centers, the number of computing devices in one data center may differ from the number of computing devices in another data center, and the number of computing devices in a given data center may change over time, for example, when hardware is removed, replaced, upgraded, or expanded. The computing devices 101 may be communicatively coupled to each other via network 150. Thus, for example, clients may perform operations in the "cloud." These operations may include uploading, accessing, or processing data. Data access and processing may include querying a database to return a portion of the data included in the database.

[0033] The processor 110 of the one or more computing devices 101 may include well-known processors or other lesser-known types of processors. Alternatively, the processor 130 may be a dedicated controller such as an ASIC.

[0034] The memory 120, or one of the one or more computing devices 101, may include a type of non-transitory computer-readable medium capable of storing information accessible to the processor 110, such as a hard disk drive, solid-state drive, tape drive, optical storage, memory card, ROM, RAM, DVD, CD-ROM, writable memory, and read-only memory. The computing device 101 may implement any of a number of architectures and technologies, including but not limited to direct-attached storage (DAS), network-attached storage (NAS), storage area network (SAN), Fibre Channel (FC), Fibre Channel over Ethernet (FCoE), hybrid architecture networks, etc. In addition to storage devices, a data center may also include several other devices, such as cabling, routers, etc.

[0035] Memory 120 is capable of storing information accessible by the one or more processors 110, including data 130 received at or generated by the processor 110, and instructions 140 executable by the one or more processors 110. Data 130 can be retrieved, stored, or modified by the processor 110 according to the instructions 140. For example, although the system and method are not limited to a particular data structure, data 130 can be stored in computer registers, data storage areas, or as a structure with multiple different fields and records or documents or caches. Data 130 can also be formatted in computer-readable formats such as, but not limited to, binary values, ASCII, or Unicode. Furthermore, data 130 can include information sufficient to identify relevant information, such as numbers, descriptive text, proprietary code, pointers, references to data stored in other memory locations (including other network locations), or information used by functions to compute relevant data. Instructions 160 can be a set of instructions directly executed by the processor 110, such as machine code, or an indirectly executed set of instructions, such as a script. In this regard, the terms "instruction," "step," and "program" are used interchangeably herein. Instruction 140 can be stored in object code format for direct processing by processor 110, or in other types of computer languages, including scripts or sets of independent source code modules that are interpreted on demand or pre-compiled.

[0036] Data 130 may include a database 132 storing data from one or more datasets. Clients 162 and 164 may access data in database 132 via queries. Accessing data may involve requesting storage of at least a portion of the data, requesting processing on at least a portion of the data, or a combination thereof. Requests may be provided by clients 162 and 164 in the form of queries, wherein the query specifies one or more parameters of the data the client wishes to access, one or more operations to be performed on the data, or both. Data 130 may further include previously received queries 134 that may be stored temporarily or permanently. Data may further include materialized views 136 of data in the database. Materialized views may be constructed in response to user queries. For example, the database may include several columns of data, the query may request the sum of each column, and the materialized view may include the processed sums of the columns. Further, for example, the query may request all rows of data whose values ​​in a given column exceed a specified threshold, and the materialized view may include a subset of the data containing only rows that satisfy the query parameters. In this way, for a given database 132 stored in memory 120, each query 134 to the database stored in memory can correspond to a materialized view 136 stored in memory.

[0037] Instructions 140 may include various algorithms for instructing processor 110 to perform operations according to the routines described herein. For example, instructions 140 may include a query processing routine 142 for processing a query received from a client over a network and returning data from database 132 to the client in response to the query. Further, for example, instructions 140 may include a query rewrite check routine 144 for determining whether previously stored query results satisfy a newly received query. Using previously stored query results to process a newly received query can reduce the processing time and overhead of the computing system, thereby reducing the time and cost of query processing.

[0038] As discussed in more detail herein, the query rewrite checking routine 144 of this disclosure is capable of checking queries that are not limited to defined values, and in particular queries that include NULL values, such as SQL queries. Regarding Figures 2 to 4 The flowcharts describe these routines in more detail. It should be understood that the routines described herein are merely examples, and in other examples, certain steps may be added, subtracted, replaced, or reordered.

[0039] although Figure 1Each of processor 110 and memory 120 is illustrated as a single box in terms of function, but processor 110 and memory 120 may actually include multiple processors and memories that may or may not be stored in the same physical housing. For example, some of the data 130 and instructions 140 stored in memory may be stored on a removable CD-ROM, while others may be stored within a read-only computer chip. Some or all of the instructions and data may be stored in a location physically remote from processor 110, but still accessible to processor 110.

[0040] Figure 2 This is a flowchart illustrating example routine 200 for processing new queries. Routines can be made by, for example... Figure 1 One or more processors of the processor 110 shown are executed.

[0041] At box 210, a new query Q for the stored dataset is received. The new query Q may include a concatenation of terms Q1, ..., QN used to process data from the stored dataset. The term "concatenation" as used herein is not limited to the AND operator. Terms can be concatenated using any combination of the AND, OR, or other operators. A query in this format is also referred to as a query that includes multiple concatenated terms. Furthermore, terms may include one or more NULL values ​​to indicate undefined values. The new query Q can be used as a filter, thereby allowing the query to process the dataset and return a filtered view of the dataset.

[0042] At box 220, a previously stored materialized view of the dataset is accessed. The materialized view can be a previously filtered version of the dataset created from a previously received query V. Similar to the new query Q, the previously received query V can include concatenations of terms V1, ..., VN, although the terms in the new query Q and the previous query V can differ because the queries themselves can be different. Also similar to the new query Q, the terms in the previous query V can be concatenated using any combination of AND, OR, or other operators and can include one or more NULL values.

[0043] At box 230, to determine whether the materialized view generated by the previous query V is sufficiently complete to handle the new query Q, the filters of the new query Q imply that the filters of the previous query V were determined. This can be achieved by comparing the two queries. In other words, the comparison can confirm that for any case where Q evaluates to true, then V must also evaluate to true, thus making V the appropriate query to replace Q.

[0044] Queries Q and V can each contain several terms, making comparison difficult. Therefore, to facilitate comparison, queries Q and V can first be broken down into smaller components, which can then be compared. For example, queries can be decomposed into simplified clauses, such as AND clauses (a group of terms combined by the AND operator) or OR clauses (a group of terms combined by the OR operator). Further, for example, clauses can be further decomposed into individual terms, which can be individual components of an AND clause separated by the AND operator, or individual components of an OR clause separated by the OR operator. Decomposing larger filters into these simpler clauses and smaller terms allows for faster and more efficient processing because comparing individual terms is much simpler than comparing larger filters.

[0045] If the filter for the new query Q determined at box 230 implies the filter for the previous query V, the operation can continue at box 240, where the new query is executed using a materialized view of the dataset already filtered by the previous query V. This avoids the need to process the entire dataset in response to the new query Q, which in turn can result in a significant reduction in query processing time and processing costs.

[0046] Conversely, if the filter for the new query Q determined at box 230 does not imply the filter for the previous query V, the operation can continue at box 250, where the new query is executed using an unfiltered view of the dataset. Since the materialized view of the previous query is determined not to return or process all data invoked by the new query, the system determines that the materialized view cannot be used as a shortcut. However, after processing the new query, the new query and the associated materialized view created by it can be saved for future reference and for processing future queries.

[0047] Figure 2 Examples typically describe a comparison between a new query and a previous query. However, it should be recognized that memory can store multiple previous queries, which can be associated with different materialized views, and any new query can be compared with any number or all of the previous queries to determine whether any of the materialized views satisfies the new query. In some instances, multiple stored views can satisfy a new query, thereby enabling the implementation of a best-view selection algorithm for, for example, selecting the minimum view.

[0048] Figure 3 This is a flowchart illustrating an example subroutine 300 used to determine whether the filter for a new query Q implies the filter for a previous query V.

[0049] At box 310, the new query is rewritten in Disjunctive Normal Form (DNF), also known as "OR in AND" or "OR clause". A DNF expression includes multiple terms joined by the "OR" operator, while an OR clause itself can include one or more terms joined using the "OR" operator. For example, "x = 6 OR (x > 3 AND y > 8) OR (NOT (x = 0) AND (y IS NULL))" is written in DNF form, and it includes three terms: the first is x = 6, the second is x > 3 AND y > 8, and the third is NOT (x = 0) AND (y IS NULL). It should be noted that the entire query is true if at least one of the terms is determined to be true. Additionally, it should be noted that queries written in DNF form can include NULL values, such as "y IS NULL".

[0050] At box 320, the previous query is rewritten in conjunctive normal form (CNF), also known as "AND or OR" or "AND clause". A CNF expression includes multiple terms joined by the "AND" operator, and the AND clause itself can include one or more terms combined using the "AND" operator. For example, "(x>5OR y>10)AND((NOT(x IS NULL))OR y>20)" is written in CNF form, and it includes two terms, the first being x>5OR y>10 and the second being NOT(x IS NULL)OR y>20. It should be noted that the entire query is true only if all terms are determined to be true. Additionally, it should be noted that queries written in CNF form can include NULL values, such as "y IS NOT NULL".

[0051] Once the new query and the previous query have been rewritten in DNF and CNF form respectively at box 330, each item of the rewritten new query is compared with the rewritten previously received query to determine whether each item of the new query implies each item of the previously received query. This can involve comparing the two queries item by item. For example, items from both the DNF and CNF expressions can be selected, and it can be determined whether the selected DNF item implies the selected CNF item. Items from both the CNF and DNF clauses can be selected iteratively until all items have been compared.

[0052] Comparing various terms to determine meaning can involve one or more logical rules. These rules are generally well-known in the field of mathematical logic. For example, constants and variables can be compared using any of the operators =, !=, <, <=, >, or >=. Similarly, combined constants and variables can be compared according to basic arithmetic rules, such as recognizing that x > A implies x >= A + 1, and x <= A implies x < A + 1. Additionally, a variable that is restricted to a range of values can be determined to not be NULL. The following examples illustrate several logical rules that may apply to some example terms:

[0053] x = 4 implies x >= 3.

[0054] x < 7 implies x <= 7.

[0055] x < 7 implies x <= 6 (for discrete types).

[0056] x < 8 implies x IS NOT NULL.

[0057] x < 7 AND x > 1 implies x < 8 AND x > 0.

[0058] For illustrative purposes, use the previous example of comparing the DNF term x = 6 OR (x > 3 AND y > 8) OR (NOT(x = 0) AND (y IS NULL)) with the CNF term "(x > 5 OR y > 10) AND ((NOT(x IS NULL)) OR y > 20)".

[0059] First, the first DNF term x = 6 and the first CNF term (x > 5 OR y > 10) can be selected. The first DNF term includes only the expression x = 6. The first CNF term is satisfied by the expression x > 5 or y > 10. Since 6 is a value greater than 5, it can be determined that x = 6 implies x > 5, and thus the DNF term implies the CNF term.

[0060] Next, the first DNF term x = 6 and the second CNF term ((NOT(x IS NULL)) OR y > 20) can be selected. Since x = 6, its value is not NULL, and the first DNF term is also determined to imply the second CNF term.

[0061] Next, the second DNF term x > 3 AND y > 8 and the first CNF term (x > 5 OR (y IS NOT NULL)) can be selected. Since y is not a NULL value in the second DNF term (it must be greater than 8), the second DNF term is also determined to imply the first CNF term.

[0062] Next, we can choose the second DNF term x>3 AND y>8 and the second CNF term ((NOT(x IS NULL)) OR y>20). Since x is not NULL in the second DNF term (it must be greater than 3), the second DNF term is also determined to imply the second CNF term.

[0063] Next, we can choose the third DNF term NOT(x=0)AND(y IS NULL) and the first CNF term (x>5OR(y ISSNOT NULL)). For this comparison, the DNF term does not imply the CNF term because there are cases where the DNF term returns false but the CNF term returns true (x, y), such as any case where y is not NULL (x, y). Therefore, in this example, the comparison between the new query and the previous query determines that the new query does not imply the previous query, and the operation can continue at box 250.

[0064] In the alternative example, the new query only includes "x = 6 OR (x > 3 AND y > 8)" and does not state "OR (NOT (x = 0) AND (y IS NULL))", so the comparison at box 330 may be different. In this case, since each DNF item in the new query will have been determined to imply each CNF item in the previous query, the comparison between the new and previous queries can determine that the filter in the new query does indeed imply the filter in the previous query, and the operation can continue at box 240.

[0065] Figure 4 This is a flowchart illustrating an example subroutine 400 used to determine whether the filter for a new query Q implies an alternative filter for a previous query V.

[0066] At box 410, the new query and the previous query are rewritten as predicates that establish the new query as true and the previous query as false or NULL. The predicates can indicate whether there exists any case where the new query evaluates to true and the previous query evaluates to false, which in turn indicates whether the filters of the new query cannot imply the filters of the previous query. In this respect, the predicate can be expressed as "Q AND((NOT V)OR(V IS NULL)".

[0067] At box 420, the predicate is rewritten in DNF, whereby multiple terms are separated by the OR operator. Rewriting the predicate in DNF allows each term in the DNF clause to be evaluated independently.

[0068] At box 430, it is determined whether each item in a DNF clause is incompatible with itself, meaning that the item contains incompatibility or contradiction. Incompatibility and contradiction typically occur between two expressions contained in an item. Each item in a DNF predicate is separated by the OR operator, but may contain multiple expressions connected with the AND operator. Thus, if two expressions are connected with the AND operator but are incompatible with each other (such as "x<1" and "x>2", or "y is NULL" and "y=5"), then the item can be determined to be incompatible with itself or contradictory. In other words, there is no value for x that would make the AND expression evaluate to true, or more generally, a variable assignment. The terms "incompatible with itself" and "contradictory" are used interchangeably in this document. Because a DNF predicate is merely a rewrite of the original predicate, each DNF item containing incompatibility or contradiction means that the original predicate as a whole contains that incompatibility and contradiction. Therefore, it would be inconsistent or contradictory for a predicate to state that Q evaluates to true while V evaluates to false or NULL. Furthermore, if each DNF term is incompatible with itself, this would indicate that for every Q that evaluates to true, V must also evaluate to true. Therefore, Q implies V. Conversely, if no DNF term is found to be incompatible with itself, it cannot be proven that for every Q that evaluates to true, V must also necessarily evaluate to true. Therefore, Q does not imply V, and Q cannot be performed based on a materialized dataset generated by V.

[0069] For example, if the new query is "x>1OR(y IS NULL))" and the previous query is "x>0OR(y IS NOT NULL)", these queries can be rewritten as predicates.

[0070] 1)(x>1OR(y IS NULL))AND(NOT(x>0OR(y IS NOT NULL))OR(x>0OR(y IS NOTNULL)IS NULL)),

[0071] To complete the evaluation at box 430, various operations can be used to simplify the predicate. At least some simplification can be performed before rewriting the predicate in DNF at box 420. For example, the first operation used to simplify the predicate might involve simplifying the "V IS NULL" part of the predicate. The following example illustrates several logical rules that may only apply to simplifying a particular example expression containing NULL:

[0072] NULL is NULL → TRUE

[0073] 7is NULL→FALSE

[0074] x is NULL → (cannot be simplified)

[0075] (x <y)is NULL→(X is NULL)OR(Y is NULL)

[0076] (NOT x) is NULL → P is NULL

[0077] (x is NULL) is NULL → FALSE

[0078] (x1 AND x2 AND…AND xN)is NULL→((x1 OR(x1 is NULL))AND(x2 OR(x2 isNULL))AND…AND(xN OR(xN is NULL))AND((x1 is NULL)OR(x2 is NULL)OR…OR(xN isNULL))

[0079] (x1 OR x2 OR…OR xN)is NULL→((NOT x1)OR(x1 is NULL))AND((NOT x2)OR(x2 is NULL))AND…AND((NOT xN)OR(xN is NULL))AND((x1 is NULL)OR(x2 is NULL)OR…OR(xN is NULL))

[0080] Taking the example above, "(x>0OR(y IS NOT NULL))IS NULL", it is clear that x>0 is incompatible with x IS NULL, and further, y IS NOT NULL is incompatible with y IS NULL. Therefore, this statement can be simplified to "FALSE". Thus, the predicate can be simplified to:

[0081] 2)(x>1OR(y IS NULL))AND(NOT(x>0OR(y IS NOT NULL))OR(FALSE))

[0082] Another operation used to simplify the predicate can involve simplifying the "NOT B" part of the predicate, which in this case is "NOT(x>0OR(y IS NOT NULL))". This is the same as "x≤0AND(y IS NULL)", making the entire predicate rewritten as:

[0083] 3)((x>1)OR(y IS NULL))AND(((x≤0)AND(y IS NULL))OR(FALSE))

[0084] For clarity, the predicates are rewritten below before the DNF rewrite, where "a" represents x>1, "b" represents (y IS NULL), and "c" represents x≤0:

[0085] 4)(a OR b)AND((c AND b)OR(FALSE))

[0086] As can be seen from the examples above, simplifying the predicate makes it easier to rewrite the predicate in DNF, as performed in box 420. In this particular example, the DNF of the example predicate is a set of four items separated by the OR operator.

[0087] 5)[a AND b AND c]OR[a AND FALSE]OR[b AND c]OR[b AND FALSE]

[0088] Finally, as performed at box 430, predicate incompatibilities can be evaluated. Starting with the first item in the sequence and ending with the last, incompatibilities can be evaluated interactively. The evaluation operation can continue at least until a compatible item is found. If this occurs, the operation can continue at box 250 because the materialized view of the previous query V will have been determined to be unsuitable for the new query Q. However, if each item is determined to be incompatible with itself, the operation can continue at box 240, where the new query Q is evaluated using the materialized view of query V.

[0089] Taking the example predicate above as an example, the second and fourth terms of the predicate rewritten in DNF are clearly incompatible because they have been simplified to "FALSE". The first term includes an expression "a" that requires x>1 and another expression "c" that requires x≤0. These requirements contradict each other, so it can be determined that the first term is also incompatible.

[0090] However, each term of a DNF predicate must be incompatible with itself in order to determine whether the filter of the new query implies the filter of the previous query. In this example, the third term includes an expression "b" requiring y IS NULL and another expression x>0, but these terms are compatible with each other. Therefore, it can be seen that for some set of variables x, y, at least this clause of the predicate is satisfied, meaning the new query will return "TRUE," while the previous query will return "FALSE" or "NULL." This indicates that the filter of the new query does not imply the filter of the previous query, and therefore the materialized view derived from the previous query cannot be used to process the new query. Instead, it can be determined that the new query is executed using a standard view of the dataset.

[0091] Routine 200 may include instructions for selecting between subroutines 300 and 400. Selection can be based on Q and V, thus allowing one subroutine to be preferred for simpler or shorter queries, and another for more complex or longer queries. For example, if it is determined that rewriting a new query or predicate in DNF is longer for one subroutine than the other, the subroutine that results in a shorter DNF rewrite can be selected. In other instances, a subroutine can be selected as the default unless an error occurs, such as the presence of an expression or set of expressions that the subroutine does not support.

[0092] To simplify the evaluation process, additional steps can be added to routine 200 and subroutines 300 and 400. An example additional step could include rewriting the queries to share a common set of variables before rewriting the new query and the previous query in DNF and CNF respectively, or rewriting the new query and the previous query as predicates. For example, if variable w can be rewritten as a different variable x, this step could involve rewriting w as x. For example, if the new query contains constraints on a variable w that is not constrained in the previous query, constraints on w in the new query might be excluded from the evaluation of the new query because it can be assumed that the previous query contains all data in the dataset related to w. This step can reduce the total number of variables to be evaluated in the query.

[0093] Another example of an additional step could be to remove structurally equivalent new and previous queries before rewriting them in DNF and CNF respectively, or rewriting them as predicates. Logically equivalent but differently written terms, such as the first term "x>5 AND y>6" and the second term "NOT(x≤5 OR y≤6)," are not removed in this step. This approach reduces the total number of terms in the evaluation.

[0094] Further example steps could include, after removing structurally equivalent items, determining whether any of the remaining items refers to a column that is not a key for the grouping operation. If any of the remaining items refers to a column that is not a grouping key, it can be preemptively determined not to use a filtered view. Conversely, if none of the remaining items refer to a column that is not a grouping key, the operation can proceed by rewriting the new and previous queries in DNF and CNF respectively, or by rewriting the new and previous queries as predicates.

[0095] The example methods above provide a way to evaluate SQL queries and compare them with previous queries and filtered views generated from those queries. In this way, it can be determined whether a new query can be processed using previously stored and indexed views of the query database. This, in turn, can lead to improved computing performance and functionality, including but not limited to faster processing times and reduced overhead in SQL query processing.

[0096] Although the techniques described herein have been referenced to specific embodiments, it should be understood that these embodiments are merely illustrative of the principles and applications of the technology. Therefore, it should be understood that many modifications can be made to the illustrative embodiments and other arrangements can be designed without departing from the spirit and scope of the technology as defined by the appended claims.

[0097] Most of the foregoing alternative examples are not mutually exclusive, but can be implemented in various combinations to achieve unique advantages. Because these and other variations and combinations of the features discussed above can be utilized without departing from the subject matter defined by the claims, the foregoing description of the embodiments should be interpreted rather than limited by the subject matter defined by the claims. For example, the preceding operations need not be performed in the exact order described above. Instead, the various steps can be processed in different orders, such as reversed or simultaneously. Steps can also be omitted unless otherwise stated. Furthermore, the examples provided herein, as well as clauses expressed in phrases such as "e.g.," "comprising," etc., should not be construed as limiting the subject matter of the claims to specific examples; rather, the examples are intended to illustrate only one of many possible embodiments. Moreover, the same reference numerals in different figures can identify the same or similar elements.

Claims

1. A method for managing queries, comprising: A new query, including a first plurality of join items, is received by one or more processors; A filtered view of a database accessed from memory by one or more processors, wherein the filtered view is filtered by a previously received query according to a filter represented by a second plurality of joins, wherein at least one of the first plurality of joins or the second plurality of joins includes at least one NULL value; The new query and the previously received query are rewritten by the one or more processors to share a common set of variables; The new query is rewritten in disjunctive normal form by the one or more processors; The previously received query is rewritten by the one or more processors in conjunction paradigm; The filter for the new query, which is implied by the one or more processors, is determined based on each item of the rewritten new query, which is implied by each item of the previously received query. as well as Based on the determination of the filters for the new query that imply the filters for the previously received query, the new query is executed by the one or more processors using the filtered view of the previously received query.

2. The method according to claim 1, wherein, The previously received query or the new query includes a grouping operation, and wherein the filter determining the new query implies that the filter for the previously received query includes: removing items in the previously received query or the new query that refer to columns that are not keys of the grouping operation before rewriting the new query in disjunctive normal form and the previously received query in conjunctive normal form.

3. The method according to claim 1, wherein, The filter for determining the new query implies that the filter for the previously received query includes: removing structurally equivalent items in the previously received query and the new query before rewriting the new query in disjunctive normal form and the previously received query in conjunctive normal form.

4. The method of claim 3, further comprising: After removing structurally equivalent items from the previously received query and the new query, the filter for the new query is determined to imply the filter for the previously received query, partly based on the fact that no remaining item refers to a column that is not a grouping key.

5. The method of claim 1, further comprising: The one or more processors receive a second new query that includes a third plurality of join items; The filtered view of the previously received query is accessed from memory by the one or more processors, wherein at least one of the second plurality of join items or the third plurality of join items includes at least one NULL value; The filter for the second new query, determined by the one or more processors, does not imply the filter for the previously received query; and In response to the determination that the filter for the second new query does not imply the filter for the previously received query, the second new query is executed on the database.

6. The method according to claim 5, wherein, The filters that determine the second new query do not imply the previously received query include: The second new query and the previously received query are rewritten as predicates, which establish the second new query as true and the previously received query as false or NULL; Rewrite the predicate in disjunctive paradigm; and It is determined that at least one term of the rewritten predicate is not incompatible with itself, wherein the filter for the second new query does not imply that the determination of the filter for the previously received query is based on the fact that at least one term of the rewritten predicate is not incompatible with itself.

7. A method for managing queries, comprising: A new query, including a first plurality of join items, is received by one or more processors; A filtered view of a database accessed from memory by one or more processors, wherein the filtered view is filtered by a previously received query according to a filter represented by a second plurality of joins, wherein at least one of the first plurality of joins or the second plurality of joins includes at least one NULL value; The one or more processors rewrite the new query and the previously received query into a predicate, which establishes the new query as true and the previously received query as false or NULL; The predicate is rewritten in disjunctive paradigm by the one or more processors; The one or more processors determine that each term of the rewritten predicate is incompatible with itself; The filter for the previously received query, determined by the one or more processors, is based on the fact that each item of the rewritten predicate is incompatible with itself; as well as Based on the determination of the filters for the new query that imply the previously received query, the one or more processors execute the new query using the filtered view of the previously received query.

8. The method according to claim 7, wherein, The method further includes: simplifying at least a portion of the predicate that represents the previously received query as NULL before rewriting the predicate in disjunctive normal form.

9. The method of claim 7, further comprising: The one or more processors receive a second new query that includes a third plurality of join items; The filtered view of the previously received query is accessed from memory by the one or more processors, wherein at least one of the second plurality of join items or the third plurality of join items includes at least one NULL value; The filter for the second new query, determined by the one or more processors, does not imply the filter for the previously received query; and In response to the determination that the filter for the second new query does not imply the filter for the previously received query, the second new query is executed on the database.

10. The method according to claim 9, wherein, The filters that determine the second new query do not imply the previously received query include: Rewrite the second new query and the previously received query to share a common set of variables; Rewrite the second new query using disjunctive normal form; Rewrite the previously received query in conjunction paradigm; and Determining that at least one item of the rewritten second new query does not imply at least one item of the rewritten previously received query, wherein the determination that the new query does not imply the previously received query is based on the fact that at least one item of the rewritten second new query does not imply at least one item of the rewritten previously received query.

11. A system for managing queries, comprising: Memory used to store instructions; as well as One or more processors, said one or more processors being coupled to said memory and configured to execute stored instructions to: Receive new queries that include multiple join items; Access the memory to a filtered view of the database filtered by a previously received query according to a filter, wherein the filtered view is represented by a second plurality of join items, and wherein at least one of the first plurality of join items or the second plurality of join items includes at least one NULL value; Rewrite the new query and the previously received query to share a common set of variables; Rewrite the new query using disjunctive normal form; Rewrite the previously received query in conjunction normal form; The filter for the new query is determined based on each item in the rewritten new query implying each item in the previously received query. as well as The new query is executed using the filtered view of the database, based on the determination of the filters implied by the filters for the new query and the filters for the previously received query.

12. The system according to claim 11, wherein, The previously received query or the new query includes a grouping operation, and wherein the filter determining the new query implies that the filter for the previously received query includes: removing items in the previously received query or the new query that refer to columns that are not keys of the grouping operation before rewriting the new query in disjunctive normal form and the previously received query in conjunctive normal form.

13. The system according to claim 11, wherein, The one or more processors are configured to execute stored instructions to remove structurally equivalent items in the previously received query and the new query before rewriting the new query in disjunctive paradigm and the previously received query in conjunctive paradigm.

14. The system of claim 11, wherein the one or more processors are configured to execute the stored instructions to: A second new query, including a third or more join items, is received by one or more processors; The filtered view of the previously received query is accessed from memory by the one or more processors, wherein at least one of the second plurality of joins or the third plurality of joins includes at least one NULL value; The one or more processors determine that the second new query does not imply the previously received query; as well as In response to the determination that the new query does not imply the previously received query, the new query is executed on the database.

15. The system according to claim 14, wherein, The one or more processors are configured to execute the stored instructions to: Rewrite the second new query and the previously received query to share a common set of variables; Rewrite the second new query using disjunctive normal form; Rewrite the previously received query in conjunction normal form; as well as Determining that at least one item of the rewritten second new query does not imply at least one item of the rewritten previously received query, wherein the determination that the new query does not imply the previously received query is based on the fact that at least one item of the rewritten second new query does not imply at least one item of the rewritten previously received query.

16. The system of claim 11, further comprising: A data center comprising multiple servers, the multiple servers being used to store a database and the filtered views of the database.

17. The system according to claim 16, wherein, The plurality of servers are configured to: provide access to the filtered view of the database for executing the new query in response to the determination that the filter for the new query implies the filter for the previously received query; and provide access to the database for executing the new query in response to the determination that the filter for the new query does not imply the filter for the previously received query.

18. A system comprising: Memory used to store instructions; as well as One or more processors, said one or more processors being coupled to said memory and configured to execute stored instructions to: Receive new queries that include multiple join items; Access the memory to a filtered view of the database filtered by a previously received query according to a filter, wherein the filtered view is represented by a second plurality of join items, and wherein at least one of the first plurality of join items or the second plurality of join items includes at least one NULL value; The new query and the previously received query are rewritten as predicates, which establish the new query as true and the previously received query as false or NULL; Rewrite the predicate in disjunctive normal form; Determine that each term of the rewritten predicate is incompatible with itself; The filter for determining the new query based on the incompatibility of each term of the rewritten predicate with itself implies the filter for the previously received query; as well as The new query is executed using the filtered view of the database, based on the determination of the filter for the new query that implies the filter for the previously received query.

19. The system according to claim 18, wherein, The one or more processors are configured to execute stored instructions to simplify at least a portion of the predicate that represents the previously received query as NULL before rewriting the predicate in disjunctive paradigm.

20. The system according to claim 18, wherein, The one or more processors are configured to execute the stored instructions to: A second new query, including a third or more join items, is received by one or more processors; The filtered view of the previously received query is accessed from memory by the one or more processors, wherein at least one of the second plurality of join items or the third plurality of join items includes at least one NULL value; The one or more processors determine that the second new query does not imply the previously received query; and In response to the determination that the second new query does not imply the previously received query, the second new query is executed on the database.

21. The system according to claim 20, wherein, The one or more processors are configured to execute the stored instructions to: The second new query and the previously received query are rewritten as predicates, which establish the second new query as true and the previously received query as false or NULL; Rewrite the predicate in disjunctive normal form; as well as It is determined that at least one term of the rewritten predicate is not incompatible with itself, wherein the filter for the second new query does not imply that the determination of the filter for the previously received query is based on the fact that at least one term of the rewritten predicate is not incompatible with itself.

Images