Querying large data under limited resources

Abstract

A method and system for bounded evaluation of SQL aggregate queries to query large data under limited resources. One embodiment includes receiving a SQL aggregate query posed against a database (110), checking whether the SQL aggregate query is bound-evaluable in a bag access mode (120), generating a bounded query plan when the SQL aggregate query is bound-evaluable in the bag access mode (130), and executing the SQL aggregate query against a bounded data set identified by the bounded query plan (140). The method extends a bound-evaluable DBMS and provides small businesses with the ability to query large data under limited resources.

Description

Technical Field

[0001] The embodiments relate to database storage and access, and more specifically, to methods and systems for querying big data with limited resources through bounded evaluation of SQL aggregation queries. Background Technology

[0002] Querying large datasets can be extremely expensive. As a metric, determining whether a tuple is present in the answer Q(D) of an SPC (selection, projection, Cartesian product) query Q in a dataset D is NP-hard1, and PSPACE-hard1 when Q is a query in relational algebra (denoted by RA). Joining two tables, each with 10 million tuples, can take days. One might assume that parallel computing could accomplish this. However, there are computational problems that parallel scalability cannot solve: the parallel runtime of algorithms for such problems may not decrease regardless of the number of machines used. Worse still, small businesses are often resource-constrained and may not be able to afford large-scale parallel computing. Summary of the Invention

[0003] A simplified summary is provided herein to aid in a basic or general understanding of the aspects of the exemplary, non-limiting embodiments that follow in a more detailed description and accompanying drawings.

[0004] One aspect disclosed herein relates to a computer-implemented method, including:

[0005] Receives SQL aggregate queries submitted to the database;

[0006] Check whether the SQL aggregation query is bounded and evaluable in package access mode;

[0007] When the SQL aggregation query is bounded and evaluable in package access mode, generate a bounded query plan; and

[0008] Execute the SQL aggregation query on the bounded dataset identified by the bounded query plan.

[0009] In some embodiments, it further includes:

[0010] If the SQL aggregation query is not bounded and evaluable, generate a partially bounded query plan;

[0011] Optimize and execute the partially bounded query plan to maximize the use of access constraints in the package access pattern.

[0012] In some embodiments, the method further includes, before checking whether the SQL aggregation query is bounded and evaluable in package access mode:

[0013] The packet access pattern is discovered from instances of the database schema;

[0014] Build an index of the packet access patterns on the database; and

[0015] Maintain the packet access pattern in response to updates to the database.

[0016] In some embodiments, checking that the SQL aggregation query is bounded and evaluable in package access mode includes:

[0017] Check whether the SQL aggregation query and the package access mode belong to special cases;

[0018] If the SQL aggregation query and the package access mode are special cases, then the SQL aggregation query is determined to be bounded and evaluable under the package access mode.

[0019] In some embodiments, checking that the SQL aggregation query is bounded and evaluable in package access mode further includes:

[0020] Check whether the SQL aggregation query is within the valid syntax for bounded evaluable queries;

[0021] If the SQL aggregation query is in a valid syntax for a bounded evaluable query, then the SQL aggregation query is determined to be bounded evaluable in the package access mode.

[0022] In some embodiments, when the SQL aggregation query is bounded and evaluable under the package access mode, generating a bounded query plan includes:

[0023] To retrieve a limited amount of data for each relation appearing in the SQL aggregation query; and

[0024] Perform SQL aggregation queries on the retrieved data.

[0025] In some embodiments, the SQL aggregation query includes nested aggregation subqueries.

[0026] In some embodiments, the packet access pattern includes a set of packet access constraints on the database schema.

[0027] On the other hand, it involves a computing system, including:

[0028] Memory containing instructions, and

[0029] One or more processors communicating with the memory, wherein the one or more processors execute the instructions to:

[0030] Receives SQL aggregate queries submitted to the database;

[0031] Check whether the SQL aggregation query is bounded and evaluable in package access mode;

[0032] When the SQL aggregation query is bounded and evaluable under the package access mode, a bounded query plan is generated; and

[0033] Execute the SQL aggregation query on the bounded dataset identified by the bounded query plan.

[0034] In some embodiments, the one or more processors further execute the instructions to:

[0035] When the SQL aggregation query is not bounded and evaluable, generate a partially bounded query plan;

[0036] Optimize and execute the partially bounded query plan to maximize the use of access constraints in the package access pattern.

[0037] In some embodiments, before checking whether the SQL aggregation query is bounded and evaluable in package access mode, the one or more processors further execute the instructions to:

[0038] Discover the packet access pattern from the database schema;

[0039] Build an index of the packet access patterns on the database; and

[0040] Maintain the packet access pattern in response to updates to the database.

[0041] In some embodiments, the one or more processors execute the instructions to check whether the SQL aggregation query is bounded and evaluable in package access mode, including:

[0042] Check whether the SQL aggregation query and the package access mode belong to special cases;

[0043] If the SQL aggregation query and the package access mode are special cases, then the SQL aggregation query is determined to be bounded and evaluable under the package access mode.

[0044] In some embodiments, the one or more processors execute the instructions to check whether the SQL aggregation query is bounded and evaluable in package access mode, including:

[0045] Check whether the SQL aggregation query is within the valid syntax for bounded evaluable queries;

[0046] If the SQL aggregation query is in a valid syntax for a bounded evaluable query, then the SQL aggregation query is determined to be bounded evaluable in the package access mode.

[0047] In some embodiments, when the SQL aggregation query is bounded and evaluable in the package access mode, the one or more processors execute the instructions to generate a bounded query plan, including:

[0048] Retrieve a bounded number of data for each relation appearing in the SQL aggregation query; and

[0049] Execute the SQL aggregation query operation on the obtained data.

[0050] In some embodiments, the SQL aggregation query includes nested aggregation subqueries.

[0051] In some embodiments, the packet access pattern includes a set of packet access constraints on the database schema.

[0052] On the other hand, it relates to a computer-readable storage medium comprising computer instructions that, when executed by the one or more processors, cause the one or more processors to:

[0053] Receives SQL aggregate queries submitted to the database;

[0054] Check whether the SQL aggregation query is bounded and evaluable in package access mode;

[0055] When the SQL aggregation query is bounded and evaluable under the package access mode, a bounded query plan is generated; and

[0056] Execute the SQL aggregation query on the bounded dataset identified by the bounded query plan.

[0057] In some embodiments, the medium further includes instructions that cause the one or more processors to perform the following operations:

[0058] When the SQL aggregation query is not bounded and evaluable, generate a partially bounded query plan;

[0059] Optimize and execute the partially bounded query plan to maximize the use of access constraints in the package access pattern.

[0060] In some embodiments, the medium further includes instructions that cause the one or more processors to perform the following operations:

[0061] Discover the packet access pattern from the database schema;

[0062] Build an index of the packet access patterns on the database; and

[0063] Maintain the packet access pattern in response to updates to the database.

[0064] In some embodiments, the medium further includes instructions that cause the one or more processors to perform the following operations:

[0065] Check whether the SQL aggregation query and the package access mode belong to special cases;

[0066] If the SQL aggregation query and the package access mode are special cases, then the SQL aggregation query is determined to be bounded and evaluable under the package access mode.

[0067] In some embodiments, the medium further includes instructions that cause the one or more processors to perform the following operations:

[0068] Check whether the SQL aggregation query is within the valid syntax for bounded evaluable queries;

[0069] If the SQL aggregation query is in a valid syntax for a bounded evaluable query, then the SQL aggregation query is determined to be bounded evaluable in the package access mode.

[0070] In some embodiments, the medium further includes instructions that cause the one or more processors to perform the following operations:

[0071] Retrieve a limited amount of data for each relation appearing in the SQL aggregation query; and

[0072] Execute the SQL aggregation query operation on the obtained data.

[0073] In some embodiments, the SQL aggregation query includes nested aggregation subqueries.

[0074] In some embodiments, the packet access pattern includes a set of packet access constraints on the database schema. Attached Figure Description

[0075] The above overview and the following detailed description of illustrative embodiments can be better understood when read in conjunction with the accompanying drawings. Example structures of the embodiments are shown in the drawings to illustrate the embodiments; however, the embodiments are not limited to the specific methods and tools disclosed. In the drawings:

[0076] Figure 1 A flowchart is shown as an exemplary method for querying big data with limited resources, according to one aspect;

[0077] Figure 2A flowchart is shown as another exemplary method for querying big data with limited resources, based on one aspect;

[0078] Figure 3 A flowchart illustrating an exemplary method for discovering packet access patterns according to one aspect is shown;

[0079] Figure 4 A flowchart is shown of an exemplary method for checking whether an SQL aggregation query is bounded and evaluable under package access mode, based on one aspect;

[0080] Figure 5 A flowchart is shown for another exemplary method for checking whether an SQL aggregation query is bounded and evaluable under package access mode, based on one aspect;

[0081] Figure 6 A flowchart is shown of an exemplary method for generating a bounded query plan based on one aspect;

[0082] Figure 7 A block diagram of an exemplary system for querying big data with limited resources is shown, based on one aspect.

[0083] Figure 8 It shows a method for RA based on one aspect. aggr Exemplary valid syntax for queries;

[0084] Figure 9 An exemplary framework, BEAS, is shown based on one aspect for querying large amounts of data with limited resources.

[0085] Figure 10 A simplified diagram of an example algorithm is shown. Detailed Implementation

[0086] Is querying large datasets unattainable for small companies, or is it a privilege reserved for large companies? Is it possible to scale a DBMS to immediately answer common queries on large datasets even with limited resources?

[0087] A recent study explored a method for addressing challenges based on bounded evaluation. To answer a query Q on a dataset D, the idea is to only look at the "bounded" portion of D sufficient to compute Q(D). Q Instead of examining the entire dataset D, we can use access pattern A, which is a combination of cardinality constraints and relevant indexes. Under A, if for all datasets D that satisfy A, we can identify the cardinality constraint through inference. And obtain D by using the index of A. Q , such that (a)Q(D) Q )=Q(D) and (b)D QIf Q is boundedly evaluable only by A and Q, then Q is boundedly evaluable. In other words, if Q is boundedly evaluable under A, then a query for Q can be answered precisely by bounded evaluation by accessing only D, whose size is constrained by the cardinality in A. Q .

[0088] The theory has been tested by industry and found to "improve performance by several orders of magnitude".

[0089] Example 1: Consider the query Q1 from Facebook Graph Search: Find all my friends who have checked in in the UK. The query is presented on dataset D1 and has two relations:

[0090] (a) `friend(uid, fid)` indicates that person `fid` is a friend of person `uid`, and (b) `checkin(uid, loc, cty, date)` indicates that person `uid` checks in at location `loc` in country `cty` on date `date`. This is denoted as RA query, and Q1 is as follows (u0 represents "me"):

[0091]

[0092] The dataset D1 here is very large, with trillions of friend links and check-ins. Directly calculating Q1(D1) is very costly.

[0093] Now consider the cardinality constraint set A1 in real life:

[0094] friend(pid→fid,5000);

[0095] checkin(uid→country,193).

[0096] Here are constraints Facebook policy states that each user is limited to 5,000 friends. This means each user can check in to a maximum of 193 countries. This can be based on... Build an index on D1 so that, given a person, it returns the IDs of all her friends by accessing a maximum of 5000 friend tuples; for Similarly. In summary, these constraints and their associated indexes are called access constraints.

[0097] Using A1, we can compute Q1(D1) by accessing up to 970,000 tuples from D1 instead of trillions of tuples. (1) We use Get T1 from the fid of up to 5000 friend tuples with uid=u0. (2) For each fidf in T1, we use T2 is obtained with a maximum of 193 country values. (3) We return a set of fids in T1 with country = UK in T2. ​​Regardless of how large D1 is, the program can obtain at most 5000 + 193 × 5000 tuples to compute Q1(D1). Therefore, Q1 is bounded and evaluable under A1.

[0098] As shown in Example 1, bounded evaluation is performed by accessing a set of data values ​​D with a bounded size. Q To answer query Q on a large dataset, it retrieves values ​​(i.e., partial tuples) using an index associated with a cardinality constraint on the associated attribute. One might argue that this could also be optimized using a traditional index-only plan. However, these are different problems, as their complexity limits indicate: deciding whether an SPC query can be answered with a "bounded" query plan is expspace-hard, while deciding whether it has an index-only plan in PTIME is different.

[0099] While bounded evaluation shows promise, much more work remains to be done, from theory to systems. This study only addresses Ragnarok Online (RA) queries within the context of set semantics. In the real world, queries are typically expressed using Ragnarok Online (RA). aggr This indicates that the RA is extended through aggregation under package semantics. RA aggr It can express all SQL (Structured Query Language) queries that do not include arithmetic expressions.

[0100] Example 2: Recall query Q1 and access pattern A1 from Example 1. Consider query Q2 to find the number of times each of my friends checked in at the UK. Let's denote it as RA. aggr Q2 is:

[0101] Q2 = gpBy(Q3, uid, count(cty)), where

[0102]

[0103] Here, `gpBy(Q3, uid, count(cty))` groups the results of Q3 by the attribute `uid` and calculates `count(cty)` for each group. Compared to Q1, A1 does not help us answer Q2. Using... We can obtain a different set of countries for each friend x. However, x may have multiple UK check-ins. Under package semantics, the access pattern no longer satisfies RA (Access Registry). aggr The demand.

[0104] To facilitate the description of the embodiments, this disclosure first defines a packet access pattern, and then specifies the RA under packet semantics. aggrThe query evaluates the boundedness of the query, whether it aggregates, and provides related theories and proofs.

[0105] RA aggr Package access mode

[0106] RA aggr It is an extension of RA, with group-by constructs and nested aggregate subqueries.

[0107] RA aggr Query. RA aggr The query is based on the expression defined by the RA operator (i.e., select σ). C Projected π Y Cartesian product × or concatenation Rename ρ A→B There are union (∪) and set difference (-), as well as a group-by aggregation operator.

[0108] gpBy(Q,X,agg1(V1),...,agg m (V m )),

[0109] Where (a)Q is an RA aggr The query is: (b) X is a group-by set of attributes; (c) agg i It is one of the aggregate functions max, min, count, sum, avg, and (d)V1,...,V m It is an attribute that makes This establishes the output relationship for Q. We will use agg i (V i This is called attribute V. i The aggregate field on the field. When clear from the context, we write the operator as gpBy(Q,X,agg(V)). Since Q itself may contain aggregate operators, RA aggr Aggregates in a query can be arbitrarily nested.

[0110] In SQL syntax, the operator can be written as:

[0111] selectX, agg1(V1), ..., agg m (V m from Q group by X

[0112] As a special case, when In this case, gpBy(Q,X,agg(V)) does not have a group-by structure. We simply write it as agg(Q).

[0113] Example 3: In Example 2, query Q2 is RA. aggr Search.

[0114] As another example, RA with nested aggregations aggr The query is Q4 on relations R(A,B,C) and S(E,F,W):

[0115] in

[0116]

[0117] Package Access Pattern. To support package semantics, this disclosure extends access patterns to package access patterns. On database schema R, package access pattern B is a set of package access constraints in the following normal form:

[0118] φ=R(|X→Y,N|),

[0119] Where R is the relation schema in R, X and Y are the attribute sets of R, and N is a positive integer.

[0120] To define the semantics of package access constraints, we use the following notation. (1) Let D denote the database of R, and let D' denote an instance of the relation schema R in R. (2) For an instance D of R, D' ... Y (X=a)={t[Y]|t∈D,t[X]=a}, that is, D Y (X=a) represents the set of Y values ​​corresponding to the X value a. (3) For any XY value ab in D, m(D,ab) represents the cardinality (multiset) of the package {|t∈D|t[XY]=ab|}, that is, the number of times ab appears in D as an XY attribute; we call m(D,ab) the multiplicity of ab in D.

[0121] We say that D satisfies ψ, denoted as D|=φ, if

[0122] (1) For any value a of X in D, |D Y (X=a)|≤N, meaning that there are at most N distinct associated Y values ​​in D; and

[0123] (2) There exists an index φ on D such that, given any X value a, by accessing at most N tuples, it retrieves (a) all relevant distinct Y values ​​b in D, and (b) for each such b, the multiplicity m(D,ab).

[0124] Intuitively, if for any value of X, there are at most N distinct corresponding values ​​of Y in D, then D|=φ. Furthermore, these values ​​of Y (partial tuples) and their multiplicity in D are indexed by ψ and can be efficiently retrieved using this index.

[0125] Example 4: Extending Example 1's A1, packet access pattern B1 consists of the following packet access constraints:

[0126] οφ1=friend(|pid→fid,5000|);

[0127] οφ2=checkin(|uid→country,193|).

[0128] Here, φ2 represents (a) for any uid u1, there are at most 193 distinct country values, and (b) there exists an index built on the friendship relationship, given any uid u1, to get all the different associated countries c and the multiplicity (u1,c) in the friendship relationship for each country c; similarly for φ2.

[0129] As another example, consider packet access pattern B2 of Q4 in Example 3, which consists of the following packet access constraints:

[0130] οφ3=R(|A→B,1|),

[0131] οφ4=R(|B→C,10|), and

[0132] οφ5=S(|EF→W,10|).

[0133] We will see that Q4 can be answered effectively with B2.

[0134] A database instance D of R conforms to the package access pattern B, denoted as D|=B, if for every φ∈B, D|=φ, where φ=R(|X→Y,N|), and D is an instance of R in D.

[0135] Intuitively, the package access constraint φ extends the access constraint by incorporating multiplicity. Given any value a of x in D, φ enforces a cardinality constraint |D Y (X = a)|≤N and returns the distinct corresponding Y values. For each corresponding Y value b, φ also returns the multiplicity of ab in D. In other words, when we only restrict the cardinality and retrieve distinct values, the access constraint under set semantics is a special case of the package access constraint.

[0136] When it is clear from the context, we will also simply refer to the package access pattern as the access pattern below.

[0137] RA aggr Bounded evaluation of queries

[0138] We will next work on RA. aggr The query defines a bounded evaluation.

[0139] Multiple relations. From an instance D of a relation schema R, we can retrieve a relation consisting of tuples (t[X,Y],m(t[X,Y])) using the index of the package access constraint R(|X→Y,N|), where t is a tuple in D. A multiple relation is a set that, in addition to the partial tuples t[X,Y] in D, also possesses multiplicity m(D,t[X,Y]), and is thus called a multiple relation.

[0140] RA aggr Operators can be easily extended to multiple relations. For example, the concatenation operator... For example, given two multi-relation relations I1 and I2, The result is denoted as I. s It is a multiple relation as follows: (a)I s The tuples in the expression have the normal form (t, M), where t is a set of elements using traditional join semantics (ignoring multiplicity). The resulting tuple, and (b) M = m(I1,t1) × m(I2,t2), where t is concatenated by t1∈I1 and t2∈I2, and m(I i ,t i ) indicates the presence of multiple relationships I i In the tuple t of (i∈{1,2}) i The multiplicity of RA. Similarly, other RAs aggr Operators are defined on multiple relations.

[0141] Bounded RA aggr Plan. Bounded RA under Packet Access Mode B aggr Plan ξ is to extend the traditional RA using the new operator fetch(T,φ). aggr The query plan is an algebraic tree where φ = R(|X→Y,N|) is a package access constraint in B, and T is an intermediate multiplicity relation on attribute R[X]. On an instance D of R satisfying φ, fetch(T,φ) retrieves the intermediate relation S = ∪ using the index φ on D. a∈T D XY (X = a), where each tuple t in S is annotated with multiplicity m(D,t) (also retrieved by φ).

[0142] Intuitively speaking, bounded RA aggr The plan is to start from a set of constants (possibly) Starting with ), retrieve data from D using the fetch operator and then transfer it to RA. aggr The operator is applied to the retrieved data, except that it accesses the data only by using the index of the package constraint in B, and allows group-by aggregation and operations on multiple relationships.

[0143] We use ξ(D) to represent the result of applying the plan ξ to D.

[0144] Example 5: Recall RA in Example 2 aggr Query the package access pattern B1 in Q2 and Example 4. Under B1, determine the bounded plan ξ for Q2. Q2 The algebraic expression is as follows:

[0145] T1(uid,fid)=fetch({u0},φ1),

[0146] T2(uid,cty)=fetch(π fid T1,φ2),

[0147] T3(uid,cty)=σ cty T2,

[0148] T4=gpBy(T3,uid,count(cty)).

[0149] As a more interesting example, recall query Q4 in Example 3 and package access pattern B2 in Example 4. According to the cardinality constraint in φ3, for any database instance D|= B2, for each value of A, for w in Q4, there exists at most one distinct value of B associated with w. Therefore, Q4 ≡ B2 Q6, that is, Q4, is equivalent to Q6 on each database D|=B2, where Q6 is

[0150] in

[0151] Q7(y)=sum(π y R(w,1,y)).

[0152] Under B2, Q6 (and thus Q4) has a bounded query plan ξ. Q4 :

[0153] T1(B,C)=fetch({1},φ4),

[0154] T2=sum(π C T1),

[0155] T3(E,F,W)=fetch(T2×{1},φ5),

[0156] T4=avg(π W T3).

[0157] Observed ξ Q4 φ3 is not explicitly used. However, ξ Q4 The correctness depends on the cardinality of φ3. Furthermore, ξ Q4By propagating the constants of Q4 through join and fetch, all values ​​and combinations needed to answer Q4 are obtained from D|=B2. In particular, in the presence of nested aggregations, the answer to the aggregate subquery can also be used by fetch, such as T3.

[0158] Bounded and evaluable queries. In access mode B, RA aggr Q is bounded and measurable if it has a plan ξ such that:

[0159] ξ is a bounded RA under B. aggr plan;

[0160] The constant {c} in ξ comes from the choice condition of Q;

[0161] Furthermore, for any database D|=B, ξ(D)=Q(D).

[0162] If Q has such a bounded query plan ξ, we denote it as Q≡ B ξ.

[0163] Here, for any multiplicity relation D1 and the traditional package (multiset) D2, if D2 can be obtained from D1 by containing m copies of each tuple (t,m)∈D1, then we write D1 = D2.

[0164] For example, query Q2 in Example 2 is bounded evaluable under B1 in Example 4 because it has the bounded program ξ given in Example 5. Q2 Similarly, Q4 in Example 3 is also bounded and evaluable under B2 in Example 4.

[0165] Observe the following regarding bounded RA aggr The contents of the plan.

[0166] Independent in scale Each fetch operation in ξ retrieves data, and its cost can be quantified by the package constraints used. Therefore, as long as D satisfies B, the cost of executing ξ is determined solely by the package access pattern B and the query plan ξ, not by the size of the dataset D. In other words, under package semantics, there is a bounded RA. aggr The plan preserves the size independence of the bounded evaluation of RA queries.

[0167] Delayed packet semantics execution Since fetch(T,ψ) returns a set, we plan to fetch and manipulate the set for ξ. This postpones the process of package semantics to the latest possible stage. This reduces the performance degradation caused by duplicate values, such as in joins, where duplicate values ​​rapidly expand.

[0168] Included in columnar storageA bounded plan ξ can also express a query evaluation on columnar storage or columnar storage indexes. In fact, in columnar storage (or columnar storage indexes), each column (or column index) on attribute A of relation schema R is essentially in normal form. This is a special case of the package access constraint. Therefore, columnar storage and columnar storage indexes are special cases of the package access pattern, and thus their evaluation plan can be represented by a bounded plan ξ under this package access pattern.

[0169] It is important to note that the efficiency of columnar storage primarily stems from implementation-level optimizations, such as columnar compression and vectorization. While these optimization strategies can also be used to implement indexes for package access patterns, they are not the focus of this disclosure. We investigate logical-level query evaluation under the general constraint R(|X→Y,N|) when X is not necessarily null. Therefore, this disclosure focuses on using row-oriented databases as the underlying platform for implementing package access patterns.

[0170] Complexity of bounded evaluation

[0171] In this section, we examine the complexity of bounded assessability and identify practically decidable cases.

[0172] Bounded measurability. The problem statement is as follows.

[0173] Input: Database schema R, package access schema B on R, and RA on R. aggr Query Q.

[0174] Question: Is Q bounded and measurable under B?

[0175] This bounded evaluability problem determines whether a query can be answered by accessing a limited amount of data, and it forms the basis for our first step in querying big data frameworks with limited resources.

[0176] No matter how important, the problem is difficult. To understand why it's interesting, let's consider the following example.

[0177] Example 6: Consider the package access pattern B3 defined on relations T(A,B) and U(E,F) and the SPC query Q8:

[0178] B3 consists of the following two access constraints:

[0179] φ6=T(|A→B,N|),

[0180] φ7=U(|E→F,2|);

[0181] Query Q8 = Q9 - Q 10 ,in

[0182] and

[0183]

[0184] At first glance, the Q9 and Q 10 It appears that none of them are bounded and evaluable, therefore Q8 is not either. In fact, we cannot retrieve the value of any of x, y, or z using the index in B3. However, considering φ7 and... Put together, any query plan from Q9 can infer that in all tuples retrieved from instance T, x must be equal to 1 or y. In other words, under B3, Q9 simplifies to SPCU. in and Therefore, due to It's easy to see B3 is bounded and measurable, and so is Q8.

[0185] As shown above, query equivalence is typically checked to determine if the query is bounded. The use of union (∪) allows us to convert SPC to SPCU (e.g., Q4 to...) under package access mode. This could further interact with set differences (-) (e.g., Q3 and...) ).

[0186] In practice, check RA or RA aggr The equivalence of queries is unattainable. Therefore, the problem of bounded measurability is irrelevant for RA. aggr It is a special case and is already undeterminable.

[0187] Theorem 1: Bounded evaluability problems are undecidable for RA queries.

[0188] Theorem 1 is verified under the access pattern. As mentioned above, the access pattern is a special case of the package access pattern. Therefore, under the package access pattern, the bounded assessability problem of RA remains undetermined. As a direct corollary, the bounded assessability problem is undetermined for any RA containing RA. aggr It is impossible to determine.

[0189] Decidable Cases. We will now identify bounded evaluability as a special case of decidability. The reasons are twofold: (1) the special case covers a large number of practically used RAs. aggr Queries, such as all SPC subqueries of the built-in benchmark queries in TPCH and TPCDS. (2) These cases reveal insights into why queries become bounded evaluable. In Section 4, we will discuss this further by providing insights into why queries become bounded evaluable. aggr Query design: Develop an efficient syntax for handling general RA (Research Automation). aggrSearch.

[0190] (I) PTIME Case. The first special case, using C. P This indicates that it consists of a combination of SPC queries and package access patterns, and can check bounded evaluability in PTIME, covering all SPC subqueries of TPCH and TPCDS.

[0191] C P kind For any package access schema B and SPC query Q on the same database schema R, (B,Q)∈C P if

[0192] (a) For each package constraint φ = R(|X→Y,N|), N > ||Q||, where ||Q|| is the number of relation atoms in Q; and

[0193] (b)Q does not have self-connection.

[0194] Theorem 2: For any packet access pattern B and SPC Q,

[0195] (1) In PTIME, determine whether (B,Q) is in C. P in; and

[0196] (2) If (B,Q) is in C P In the PTIME function, it is determined whether Q is bounded and evaluable under B.

[0197] Proof: Statement (1) is obviously true. We will now prove (2) by giving a necessary and sufficient condition for PTIME, in order to check the bounded approximability of Q under B.

[0198] This condition needs to cover the concept of SPC queries. It is characterized by a set cov(Q, B), which consists of attributes. Attribute values ​​can be retrieved along with B via a fetch operation without directly accessing the original data in the database. More specifically, let X... C Q It is the set of attributes A in the constant choice predicate of Q, that is, for a constant c, σ A＝c Therefore, cov(Q,B) is inductively defined as:

[0199] (a)

[0200] (b) If A∈cov(Q,B) and (This indicates that A = B can be determined by the transitivity of the equation from the choice predicate Σ) Q (derived from this), then B∈cov(Q,B);

[0201] (c) If And R(|X→Y,N|)∈B, then and

[0202] (d)cov(Q,B) does not contain any other content.

[0203] Use X R Q This represents the set of attributes in the selection or connection predicate of Q, or the top-level projection attributes. We then present the following.

[0204] Lemma 3: For any (B,Q)∈C P Q is bounded and evaluable under B if and only if for every relation R in Q, there exists φ = R(|X→Y,N|)∈B such that

[0205] From Lemma 3, we can derive Theorem 2(2), since we can simply examine the conditions of Lemma 3 in the magnitudes of Q and B in PTIME. We will now prove Lemma 3.

[0206] Suppose that Q is bounded and evaluable under B. Then there exists a bounded program ξ for Q under B. We will first construct a query Q by induction from ξ. ξ Make (a)ξ≡Q ξ ≡ B Q, where Q≡ B Q 0 This means that for any database D|=B, Q(D)=Q 0 (D), and (b)Q ξ The condition for Q in Lemma 3 is satisfied. Then we prove that when Q... ξ When the condition is satisfied, Q also satisfies the condition, therefore Lemma 3 holds.

[0207] Q ξ The construction of ξ. We construct the query Q by inductively applying the ξ structure. ξ as follows.

[0208] If ξ = {c}, then Q ξ ={c}.

[0209] If ξ = σ C (ξ 0 (or π) Y (ξ 0 )), then Q ξ =σ C (Q ξ 0)(or π Y (Q ξ 0), where Q ξ 0 is ξ 0 The constructed query.

[0210] If ξ = fetch(ξ)0 If R(|X→Y,N|)), then Q ξ =π Z (Q ξ 01 X R(X,Y,Z)).

[0211] If ξ = ξ1 × ξ2, then Q ξ =Q ξ1 ×Q ξ2 .

[0212] By inductively summarizing the structure of ξ, it can be easily verified that for Q... ξ For each relation R in the set, there exists a bag constraint φ = R(|X→Y,N|)∈B such that That is, Q ξ The conditions of Lemma 3 are satisfied.

[0213] The query Q satisfies the condition. We will now prove that when Q... ξ When this condition is met, Q satisfies the conditions in Lemma 3. Since for every package constraint φ = R(|X→Y,N|)∈B, N>||Q||, from Q... ξ ≡ B Q, can you verify Q? ξ ≡Q. Therefore, there exists from Q. ξ The homomorphism ρ to Q. Furthermore, since Q is self-join-free, each relation schema R (i.e., relation atom) appears at most once in Q. Therefore, no relation atom in Q can be deleted without altering Q. Thus, Q is minimal (and minimal if the SPC query has no redundant relation atoms).

[0214] Therefore, for each relation R in Q, in Q ξ There must exist a relation R. 0 , such that ρ(R) 0 ) = R, in addition, therefore In other words, Q also satisfies the conditions of Lemma 3.

[0215] Suppose that Q satisfies the conditions of Lemma 3. We construct a 3-step bounded query plan ξ for Q under B:

[0216] (a) For each relation R in Q, it has a bounded subprogram ξ R It obtains all the attribute values ​​needed to answer Q;

[0217] (b) It is achieved through bounded subprograms ξ R c Combine the attribute values ​​of each relation R in Q to ensure that each (partial) tuple retrieved and saved for R extracts values ​​from the same tuple in D; and

[0218] (c) It ultimately applies to each relation R in Q. Execute the operation in Q.

[0219] To prove that Q has such a plan ξ under the conditions of Lemma 3, we only need to prove the following two properties:

[0220] (1) Answering all the necessary attribute values ​​of Q from each relation R in Q can be done through ξ in step (a). R To retrieve, and (2) their combination can be obtained through step (b). To restore.

[0221] The proof of (1). We do this by providing each attribute Construct such a bounded program ξ R[A] To prove (1). Note that answering Q only requires The attributes in the plan. R[A] This is constructed by transforming the proof of witness A∈cov(Q,B). More specifically, since the condition of Lemma 3 holds for Q and B, for For any property A such that A∈cov(Q,B), there must exist a set of rules (a)–(c) applying, defining cov(Q,B) such that

[0222]

[0223] in A∈cov n Step i involves applying rule r from one of rules (a)–(c). i To expand cov i-1 To define the previously given cov(Q,B). We transform For Bounded Plans:

[0224] ξ:ξ0,...,ξ n ,

[0225] Where ξ0 is empty; ξ i Based on steps From ξ0,...,ξ i-1 The derivation process is as follows:

[0226] (i) If r i It is used for X C Q According to rule (a) for the constant c, then ξ i It is {c};

[0227] (ii) If r iRule (b) has A = B such that A ∈ cov i-1 , and B ∈ cov i , then ξ i = ξ i-1 ;

[0228] (iii) If r i is rule (c), then ξ i = fetch(ξ j1 1···ξ j|X| , φ = R(|X → Y,N|)), where ξ j1 ,..., ξ j|X| is a bounded plan for obtaining attributes in X(j1,...,j |X| <i).

[0229] By construction, ξ i is a bounded plan that obtains all A-attribute values for Q. Note that each sub-plan ξ i is bounded because it does not involve a relational scan.

[0230] (2) Proof. For each A ∈ X R Q construct plan ξ A using ξ R c , (i) where the (i ∈ [1,|X|]) range of A i is over all attributes in X, and X Ai is the plan generated above for obtaining the A i value; (ii) if for φ = R(|X → Y,N|) ∈ B, then ξ R c = fetch(T1, φ). Since ξ A is a bounded plan for each A ∈ X R Q , ξ R c is bounded under B.

[0231] Therefore, when the conditions of Lemma 3 hold for Q and B, the ξ constructed above is a bounded plan for Q under B.

[0232] (II) NP case. One might want to remove the condition (b) constraint on the queries in C P . This covers all SPC queries, including those with self-joins. However, unless P = NP, bounded evaluability analysis becomes more difficult.

[0233] C NP kindUsing C NP Let represent the set of packet access patterns B and SPC query Q pairs such that for each packet constraint φ = R(|X→Y,N|), N > ||Q||. We have the following.

[0234] Theorem 4: For any packet access pattern B and SPC Q,

[0235] (a) In PTIME, determine whether (B,Q) is in C. NP in; and

[0236] (b) If (B,Q) is in C NP In this case, it is determined whether Q is bounded and NP-complete under B.

[0237] Proof: Statement (a) is straightforward. To prove statement (b), we first give the query Q under B for any (B,Q)∈C. NP It is a necessary and sufficient condition for being bounded and measurable. Based on the characterization, we then show that checking C... NP The bounded measurability is NP-complete.

[0238] Let Q m The minimal equivalent query for Q, i.e., the minimized version of Q, can be obtained by removing all redundant relations. For an SPC query Q, there exists a unique minimal equivalent query in the isomorphic sense. Similar to the proof of Lemma 3, the following C can be verified. NP Condition.

[0239] Lemma 5: For any (B,Q)∈C NP If and only if for Q m For each relation atom R in B, there exists φ = R(|X→Y,N|)∈B such that Then Q is bounded and evaluable under B.

[0240] Based on this, we prove that for C NP The (B,Q) in the equation determines whether Q is bounded under B and can be evaluated as NP-complete.

[0241] Upper limit. We present an NP-complete algorithm as follows:

[0242] (a) Convert Q to its screen representation (T) Q ,u);

[0243] (b) Guessing the subquery Q of Q 0 =(T 0 ,u) makes And from T Q To T 0 The mapping ρ;

[0244] (c) Check if (i)ρ is from (T) Q ,u) to (T 0 The homomorphism of (i) and (ii) for Q 0 Does each relation atom R in B contain φ = R(|X→Y,N|) ∈ B such that...? If so, return "Yes".

[0245] The algorithm is correct because if conditions (i) and (ii) of steps are met...

[0246] (c) Make Q 0 For them to be valid, they must also make Q the minimum equivalent query Q. m Established. In fact, ρ also determined the origin from (T) Q ,u) to (T Qm The homomorphism of (u) is because Q m It is the minimum equivalent query of Q, that is Therefore, if condition (ii) is in Q 0 If it holds true on Q, then through the homomorphism ρ it must also be true on Q. m The condition of Lemma 5 holds, meaning that the conditions of Lemma 5 apply to both Q and B. Therefore, according to Lemma 5, Q is bounded and evaluable. The algorithm is NP-complete because step (a) is in PTIME, and step (c) is in PTIME within |Q|. 0 In |、|Q|、|ρ| and |B|, |Q 0 |≤|Q| and ρ=O(|Q|). Here, |Q| is the size of Q, i.e., the number of attributes and aggregate fields in Q; |B| is the total length of the package constraints in B.

[0247] Lower bound. To prove that this problem is NP-hard, we consider the following problem, denoted by MinCQ.

[0248] Input: relation schema R and R-based SPC query Q.

[0249] Question: Is Q minimized, i.e., is Q the minimum equivalent query of Q?

[0250] It is easy to verify that MinCQ is coNP complete by simplifying it from an NP-complete 3-coloring problem.

[0251] Lemma 6: The problem MinCQ is coNP complete.

[0252] We proved C NP The bounded evaluability problem is NP-hard, which can be solved by simplifying the MinCQ complement.

[0253] Given a MinCQ instance, i.e., a relation schema R(A1,...,A... m) and an R-based SPC query Q, we construct a database schema R 0 、an R 0 -based SPC query Q 0 and a package access pattern B. We prove that Q is not minimal if and only if Q 0 is boundedly evaluable under B.

[0254] The database schema R 0 consists of a single relational schema

[0255] where n is

[0256] the number of relational atoms occurring in the query Q.

[0257] Intuitively, R 0 extends R with additional attributes . As will be shown later, these new attributes will be used together with Q 0 as join attributes to pairwise join the n relational atoms of Q 0 in Q.

[0258] The query Q 0 is derived from Q as follows:

[0259] The query Q 0 retains the same number of joins and relational atoms as Q, such that each relational atom R i (i.e., a renaming of the relational schema R ∈ R) is replaced by R i 0 (i.e., a renaming of the relational schema R 0 ∈ R 0 ); and

[0260] the selection (join) condition C of the query Q 0 contains all the selection predicates of Q, and in addition, the following predicates: for each pair of relations R i and R j in Q (i < j), add the equality R i 0 [B p = R j 0 [B p to C, where

[0261] intuitively, C retains all the selection conditions of Q and additionally joins each pair of the n relational atoms on dedicated attributes : (a) for each B k , there exist exactly two relational atoms R i 0 and R j0 This makes R i 0 [B k ] = R j 0 [B k (b) For each R i 0 and R j 0 There just happens to be an attribute B. k This makes R i 0 [B k ] = R j 0 [B k ].

[0262] Packet access mode B is determined by It consists of several constraints. Let W be A1,...,A m The set of all attributes of B, so that they appear in the selection / connection conditions or top-level projection attributes in Q. Then B includes

[0263]

[0264]

[0265] ···

[0266]

[0267] We will now prove that the query Q is not minimal if and only if Q is bounded and evaluable under B.

[0268] Suppose Q is not the minimum. Then Q 0 None of the n relation atoms in the equation can be minimized by Q. 0 To remove. Therefore, all Attributes Together with W, according to Lemma 5, for some in B It must be included in XY. This will produce a property. This is impossible because for any φ i ∈B, based solely on the above definition, φ i Include Each attribute.

[0269] Assume Q 0 Under B, it is bounded and evaluable. Since each φ i ∈B contains Each attribute, according to Lemma 5, for Q 0 Each relation atom R in i0 , Must contain at most There are 1 attribute, of which Q′ m It is Q 0 The minimum equivalent search. Because... Include Attributes, query Q 0 It is not the smallest.

[0270] Although it is difficult to handle, C can be examined in practice using Lemma 5. NP Bounded evaluability is feasible. Indeed, efficient algorithms already exist to minimize SPC queries, i.e., to compute Q for Q. m Furthermore, the size of Q is typically small. One algorithm is used to compute Q. m The Turing machine can still efficiently check the bounded quantifiability of general SPC queries: first minimize Q, then produce Q. m Then check Q m Does B satisfy |Q m The conditions of Lemma 5 in PTIME in || and |B|.

[0271] RA aggr Valid syntax

[0272] This disclosure presents a method for inspecting general RA. aggr An efficient method for the bounded assessability of queries. We prove that although the problem is undecidable (Theorem 1), there exists a method for the bounded assessability of RA. aggr The query has a valid syntax, which simplifies the problem to syntax checking. Furthermore, we identified the RA. aggr Two utility subclasses for querying are provided, along with their valid syntax.

[0273] Under access mode B, bounded evaluable queries for L (where L refers to, for example, RA or RA) aggr The valid syntax of ) is a subclass of L. L B Such that for any Q in L,

[0274] (a) If Q is bounded and measurable, then in L L B There exists a query Q 0 , making Q≡ B Q 0 ;

[0275] (b)L L B Each query Q in the equation is bounded and evaluable; and

[0276] (c) It checks whether Q∈L in PTIME by querying the size of Q|Q| and the length of the constraint in B|B. L B .

[0277] For all databases D|=B, if Q(D)=Q 0 (D), where Q≡ B Q 0 Intuitively, an efficient syntax simplifies the problem of determining the bounded evaluability of an L query to a matter of L... L B Syntax checking. In fact, every bounded evaluable L query can be evaluated under B's L. L B We can find an equivalent query within L. Therefore, we can safely use L. L B Solve it by querying in L, because L L B It can represent, or even be equivalent to, all bounded evaluable L queries.

[0278] To some extent, effective syntax L L B The development of this approach is similar to the research on range-safe queries in relational calculus. In fact, the question of checking the "safety" of relational calculus queries is also undecidable. Nevertheless, commercial DBMSs support range-safe queries by using efficient syntax for range-safe relational calculus queries. We adopt the same approach to handle RA. aggr Bounded evaluability of queries.

[0279] Below we define RA as bounded and measurable under B. aggr The query developed such an efficient syntax, using L B express.

[0280] L B kind In short, we use three sets to represent L. B : BA(Q,B), BR(Q,B), and BQ(Q,B). Informally, under packet access mode B, for RA aggr Query Q,

[0281] BA(Q,B) contains attributes (e.g., A) and aggregates (e.g., sum(A)) of Q, the values ​​of which can be obtained through B;

[0282] BR(Q,B) consists of relations in Q, and the partial tuples required to answer Q can be reconstructed from attribute values ​​obtained from BA(Q,B); and

[0283] BQ(Q,B) contains bounded evaluable subqueries of Q. If Q∈BQ(Q,B), then RA aggrQuery Q contains L B middle.

[0284] Intuitively, these sets represent RA aggr Querying Q, the values ​​of all the attributes required to answer Q can be "derived" from constants in Q through access patterns B via joins and retrieval. These attributes participate in the RA of Q. aggr The operation is referred to as querying a nontrivial attribute of Q. The class of such queries is a bounded evaluable Ra. aggr The query provides a valid syntax.

[0285] More specifically, it combines BA, BR, and BQ for use. Figure 8 The rules given are defined recursively, and the symbols are explained in Table 1. Intuitively, (1) Figure 8 Rule γ1 includes the constant property X in BA(Q,B) Q c (See Table 1); (2) γ2 propagates values ​​from attributes and aggregate fields to join attributes; (3) γ3 specifies that values ​​are propagated via fetch; (4) γ4 indicates that if a subquery is bounded and evaluable, then its output attributes and aggregate fields can also be fetched; (5) γ5 adds relation atoms R to BR(Q,B) only if some tuples of R can be reconstructed from the combination of fetched values; (6) γ6 indicates that if all relations of the subquery can be fetched correctly, then the subquery is bounded and evaluable.

[0286] Table 1: Symbols and Definitions

[0287]

[0288] As shown in Table 1, Σ Q This represents a set of equality predicates embedded in the selection or join conditions of Q, and This indicates that the equation A = B can be expressed through the transitivity of the equation relation Σ. Q It was derived.

[0289] Example 7: Recall query Q2 from Example 2 and package access pattern B1 from Example 4. We prove that Q2∈L B1 .

[0290] (1) First, according to rule γ1, BA(Q2,B1) includes f.uid and c.cty, where f (or c) is short for friend (or checkin).

[0291] (2) According to rules γ3 and φ1, BA(Q2,B1) further includes f.fid.

[0292] (3) Because Since f.fid∈BA(Q2,B1), according to rule γ2 we have BA(Q2,B1) including c.uid.

[0293] (4) Since f.uid and f.fid are both in BA(Q2,B1) and are attributes of φ1, by γ5, f∈BR(Q2,B1). Similarly, since φ2, c∈BR(Q2,B1). Note that only c.uid and c.cty are nontrivial attributes of Q in relation c, and they are both in BA(Q2,B1).

[0294] (5) Through γ6, subquery Q3 and query Q2 themselves are in BQ(Q,B).

[0295] To give another example, recall query Q6 in Example 5 and B2 in Example 4. We will now prove that Q6∈L B2 .

[0296] (1) Using γ1 and γ3, it can be easily deduced that BA(Q6,B2) includes B and C, and by γ5, BR(Q6,B2) includes R.

[0297] (2) Through γ6, BQ(Q6,B2) contains the subquery Q7 of Q6.

[0298] (3) Therefore, further through γ4 we have Z Q7 ∈BA(Q6,B2), where Z Q7 It is the output of Q7, which is an aggregate field.

[0299] (4) Through γ2, we know that BA(Q6,B2) includes F, because Furthermore, F is not an aggregate field.

[0300] (5) Therefore, by γ5 and γ6, S∈BR(Q6,B2) and Q6∈BQ(Q6,B2).

[0301] Notice However, Q4≡ B2 Q6, Q6∈L B2 Q6 is bounded and evaluable under B2. Similarly, for Q8 in Example 6, And B3, can be verified but and Under B3, it is bounded and evaluable.

[0302] Next, we will prove L. B Indeed, B is a bounded and measurable RA. aggr Valid query syntax.

[0303] Theorem 7: Under any packet access mode B, L B Bounded measurable RA aggr Valid query syntax.

[0304] Proof: By proving the following lemma, we prove L below. B We have attributes (a) and (b) with valid syntax. We will constructively prove attribute (c).

[0305] (I) For any bounded program ξ under B, L B Q≡ exists in B ξ. (II) For any Q∈L B There exists a bounded program ξ≡ under B. B Q.

[0306] That's enough. In fact, for any Q bounded under B, by definition, there must exist a B-bounded plan ξ. Q Therefore, from (I), L B Q exists in 0 ≡ B ξ Q On the other hand, if Q≡ B Q 0 ∈L B , by (II), Q 0 There is a bounded plan ξ 0 ≡ B Q 0 ≡ B Q, that is, Q is also bounded and evaluable under B. Therefore, L B Attributes (a) and (b) that have valid syntax.

[0307] We will now prove these two lemmas.

[0308] Proof of (I). We prove this by induction of the ξ structure.

[0309] Basic case. When ξ is {c} or At that time, according to the definition, ξ itself is in L B middle.

[0310] In summary, we consider the structure of ξ.

[0311] (i)ξ is gpBy(ξ 0 According to the inductive hypothesis, there exists a query Q. 0 ≡ B ξ 0 Make Q 0 ∈L B Consider Q = gpBy(Q 0According to L B Definition, Q 0 All relationships are in (because Q and Q) 0 (They share the same nontrivial properties). Therefore, according to rule γ6, Q∈BQ(Q,B). That is, Q∈L B Obviously, Q≡ B ξ.

[0312] ξ=π Y (ξ 0 ),σ C (ξ 0 The cases ξ1×ξ2, ξ1∪ξ2, and ξ1-ξ2 are similar and can be verified using the same method.

[0313] (ii)ξ is fetch(ξ) 0 Given a variable , φ), where φ = R(|X→Y,N|). Based on the inductive hypothesis, there exists a query Q. 0 ≡ B ξ 0 Make Q 0 ∈L B Consider Q = π R[XY] (Q 0 1 Z Q0 ＝R[X] According to the semantics of fetch, Q≡ B ξ. We will now prove that Q∈L B Because of Q 0 ∈L B ,Q'∈BQ(Q',B). Therefore, according to the rule γ4, Further from γ2, From γ3, Therefore, from γ5 and γ6, Q∈BQ(Q,B). That is, ξ∈L B .

[0314] Proof of (II). Since Q∈L B According to L B The definition must have a form consisting of Figure 8 The proof composed of the rules applied in the middle Its derivation, Q∈BQ(Q,B), is a sequence of the following form.

[0315]

[0316] Where (a)r i (i∈[1,n]) is Figure 8 One of the rules; (b) (c) For each step i, BA i BRi and BQ i Only one of them was changed to BA. i+1 BR i+1 and BQ i+1 ;(d)r n This is rule γ6, which derives Q∈BQ(Q,B). We will... The length is defined as The number of rules n applied in the process.

[0317] Inductive hypothesis. We prove that for length i...

[0318] If A∈BA i+1 ,but The value of A required to answer Q can then be obtained through the bounded plan under B;

[0319] if R∈BR i+1 but The value in R required by Q can be obtained through a bounded plan under B; and

[0320] if q∈BR i+1 but The exact answer to the subquery q of Q can be obtained using a bounded plan under B.

[0321] It is important to note that for any Q∈L B Q must have a proof that ends with Q∈BQ(Q,B). If the inductive hypothesis holds, Q must have a bounded program under B, which proves Lemma (II). We will then proceed by... length We use induction to prove it.

[0322] Basic situation. When In this case, rule r1 can only be (i). Figure 8 γ1, that is, A∈BA1 is included in the choice A=c from Q; or (ii) Figure 8 γ3, that is, R[Y] is contained in BA1, has For (i), simply let ξ A ={c}. Then ξ A It is a bounded plan that obtains all the necessary values ​​of A. For (ii), let Then, using the semantics of fetch, all the R[Y] values ​​required to answer Q are determined by ξ. R[Y] Get (here we pre-name Q to ensure there are no duplicate attribute names).

[0323] Induction. Assume that the hypothesis holds for proofs of length up to k. Consider a proof of length k + 1 We discuss the last step

[0324] (i) If r k+1 is rule γ1 and the attribute A = c, then A can be obtained in exactly the same way as in the base case.

[0325] (ii) If r k+1 is rule γ2, which includes the attribute B in BA i+1 and A = B, then the attribute A must be included in BA i+1 at some step before k + 1. By the induction hypothesis, there must exist a bounded plan ξ A that obtains all the necessary values for answering Q except B. Thus ξ B = ξ A is also a bounded plan that obtains B for Q by the condition A = B.

[0326] (iii) If r k+1 is rule γ3, which includes R[Y] in BA i+1 and and the constraint φ = R(|X → Y,N|), then there are steps i1,...,i p before k + 1 that include R[X1],...,R[X ip in BA such that R[X1] ∪ ··· ∪ R[X p = R[X]. Thus, by the induction hypothesis, R[X j (j ∈ [1,p]) has a bounded plan ξR[Xj]. Let ξR[X] be 1pj = 1ξR[Xj], then ξR[X] obtains all the values of R[X] that are necessary for answering Q. Therefore, further by the semantics of fetch, R[Y] has a plan fetch(ξ R[X] ,φ) to retrieve all the R[Y] values required for answering Q.

[0327] (iv) If r k+1 is γ4, which includes Z s from the BA of a subquery Q i+1 of Q and is included in BQ Qs at step j < i + 1, then by the induction hypothesis, there is a plan ξ j for Q s under B that exactly answers Q Qs . Thus, we can simply obtain its output attribute Z s through ξ Qs . QsThe value of ξ. Note that due to ξ Qs It is Q s A precise plan, therefore even Q s It is an aggregate subquery, Z Qs The value can also be guaranteed to be correct.

[0328] (v) If r k+1 It is γ5, which includes BR i+1 If R in B has R[X] and φ = R(|X→Y,N|)∈B, then by the inductive hypothesis, there exists a plan ξ. R[X1 ],...,ξ R[Xp ] respectively obtain R[X1],...,R[X p All necessary values ​​of ] such that R[X1]∪···∪R[X p ] = R[X]. Therefore, R[X] has a bounded program ξ. R[X] =1 p j＝1 ξ R[Xj It obtains all the necessary R[X]-values ​​for Q. Since R[XY] covers all the non-trivial properties of R for Q, it is obtained by ξ. R[XY] =fetch(ξ R[X] With φ), we can obtain all the R[XY]-values ​​combinations needed to answer Q.

[0329] (vi) If r k+1 It is γ6, which is included in BQ i+1 Q in s So Q s All relations R1,...,R p This has already been included in BR in previous steps. Therefore, according to the inductive hypothesis, there exists ξ. R1 ,...,ξ Rp They are respectively from R1,...,R p To obtain all the necessary values ​​for Q, now use Q. s Each relation R in i (i∈[1,p]) is replaced with ξ Ri To build a plan Qs So ξ Qs It must be Q of Q. s The query plan, because all the necessary value combinations can be obtained through ξ Ri (i∈[1,p]) is retrieved from D, then Q s The filtering and combination are exactly the same as on D.

[0330] Therefore, the hypothesis holds true for a length of k+1.

[0331] Special circumstances

[0332] This is RA aggr Two important subclasses: (1) RA consists of RAs that do not aggregate. aggr Query components; (2) RA 0 aggr It is RA aggr The query category, where group-by aggregation, if present, only appears at the top level (final operation). In practice, RA and RA have been found... 0 aggr It is very common.

[0333] This disclosure pertains to bounded, measurable RA and RA. 0 aggr The query provides a valid syntax. Use L respectively. B [RA] and L B [RA 0 agg ] represents L B RA class and RA 0 aggr Query classes. They are RA and RA. 0 aggr Generates valid syntax.

[0334] Corollary 8: For any access pattern B, (1)L B [RA] is the valid syntax for RA queries with B as the boundary; (2) L B [RA 0 agg ] is RA with B as the boundary. 0 aggr Valid query syntax.

[0335] Proof: Since checking whether a query is in L within PTIME B In, and L B Each query in L has a bounded plan under B. To prove L B [RA] and These are bounded measurable RA and RA, respectively. 0 aggr The query has an efficient syntax, which is sufficient to prove that for any bounded evaluable RAQ1 and RA... 0 aggr Q2, there exists Q1'∈L B [RA] and Make and This is the same proof as Lemma (I) of Theorem 7 above, by proving that for every bounded RA (or The plan is to be in L B [RA](or) Each of these has an equivalent query to verify it.

[0336] RA 0 aggr There is a close relationship between RA and its effective syntax: any bounded evaluable RA 0 aggr The efficient syntax of queries also provides us with the efficient syntax of bounded, evaluable Ragnarok Online (RA) queries, and vice versa. For any Ragnarok Online... 0 aggr Query Q = gpBy(Q) 0 Class L of X, agg(V)), using (a)L 0 Indicates RA query Q 0 The class is RA, which is embedded in L. aggr (a) Query the subquery in Q; (b) L[RA] represents the RA query class in L. Then we have the following:

[0337] Lemma 9: Under any packet access mode B,

[0338] (1) If L is a bounded and measurable RA 0 aggr If L[RA] is a valid grammar, then L[RA] is a bounded, evaluable, valid grammar of RA.

[0339] (2) If L 0 If L is a bounded, evaluable, and efficient RA, then L is a bounded, evaluable, and efficient RA. 0 aggr Valid syntax.

[0340] Proof: Lemma 9(1) can be verified by the definition of efficient syntax. We focus here on Lemma 9(2) (the proof of Lemma 9(1) is simpler). By the definition of a bounded evaluable query, it is easy to prove the following lemma: for any RA 0 aggr Q = gpBy(Q) 0 ,X,agg(V)), under B, when Q 0 Q is boundedly measurable when it is boundedly measurable.

[0341] We will now use lemma to prove Lemma 9(2). When L 0 When considering the valid syntax of bounded evaluable RA queries under B, consider L. 0 The association class L. (1) First, observe that all queries in L are also bounded and evaluable, because L 0 The RA query in the context is (2) for any bounded evaluable RA. 0 aggrThe query Q = gpBy(Q1, X, agg(V)) is performed. According to the lemma above, Q1 is also bounded and evaluable under B. Therefore, there exists Q1' ∈ L. 0 Make Therefore Q 0 =gpBy(Q′1,X,agg(V))≡ B Q and Q 0 ∈L (due to (3) In addition, in PTIME, by examining the RA query Q embedded in PTIME. 0 Is it in L? 0 We then check whether query Q is in L. From (1), (2) and (3) above, we can derive Lemma 9(2).

[0342] By using Lemma 9, one can easily extend the efficient syntax for bounded evaluable RA queries (e.g., the RA covered in

[10] ) to be used for bounded evaluable RA queries. 0 aggr Valid query syntax.

[0343] Some might argue that such an extension is beneficial for RA. aggr That's also possible. However, when group-by aggregation is used with other RAs... aggr When operators are nested, convenient expansion is not possible. This represents the propagation of values ​​from aggregate subqueries to other relations, or covering all bounded evaluable queries up to the equivalent pair RA. aggr It is even more difficult.

[0344] Proposition 10: For any package access pattern B, the RA query set covered by B is correctly contained in L. B [RA] in.

[0345] Proof: It can be verified that the covered RA query can be used with L even without rule γ4. B [RA] represents it. Therefore, it is L. B A subclass of. To see if it is a proper subclass, consider L on relations R(A,B) and S(C,D). B [RA]Query Q: Q = π D ((σ A =1R1-σ B＝1 R2)1 A＝C S), where R1 and R2 are renamed to R. Consider B, which consists of R(|A→B,N1|), R(|B→A,N2|), R(|B→A,N2|), and S(|C→D,N3|). It can be verified that Q is not covered by B because the subquery S is not covered. However, Q∈L B [RA].

[0346] Example

[0347] The following provides information via RA. aggr Bounded evaluation of queries is used to query various implementations of big data with limited resources.

[0348] Figure 1 A flowchart of an exemplary method 100 for querying large amounts of data under limited resources is shown, according to one aspect. At 110, an SQL aggregation query (by a database) can be received. aggr (Representation). In some embodiments, the database may be a traditional relational database, which may include any large dataset. In some embodiments, the SQL query may be obtained from a client, i.e., user input, a computer program, or a client device. At 120, it is checked whether the SQL aggregation query is bounded and evaluable in package access mode. At 130, if the SQL aggregation query is bounded and evaluable in the package access mode, a bounded query plan is generated. At 140, the SQL aggregation query is executed on the bounded dataset identified by the bounded query plan.

[0349] In some embodiments, the SQL aggregation query includes nested aggregation subqueries.

[0350] As discussed herein, the disclosed embodiments extend the functionality of a DBMS with bounded evaluation and are designed to reduce queries on large datasets to computations on small datasets, thereby enabling the querying of large datasets with limited resources. This embodiment computes accurate answers to SQL aggregation queries by accessing a bounded portion of the database through bounded evaluation.

[0351] Figure 2 A flowchart of another exemplary method 200 for querying large amounts of data with limited resources is shown, according to one aspect. At 210, method 200 further includes generating a partially bounded query plan when the SQL aggregation query is not bounded and evaluable. At 220, the partially bounded query plan is optimized and executed to maximize the utilization of access constraints in the package access pattern.

[0352] This embodiment proposes to compute approximate answers to unbounded queries under constrained resources by accessing a limited amount of data, and to provide deterministic accuracy guarantees in the access mode.

[0353] Figure 3A flowchart of an exemplary method 300 for discovering a packet access pattern is shown according to one aspect. At 310, the method 300 discovers the packet access pattern from instances of a database schema. At 320, the method 300 constructs an index of the packet access pattern on the database. At 330, the method 300 further maintains the packet access pattern in response to updates to the database. In some embodiments, the packet access pattern includes a set of packet access constraints on the database schema.

[0354] Figure 4 A flowchart is shown of an exemplary method 400 for checking whether an SQL aggregation query is bounded and evaluable under a package access pattern, according to one aspect. At 410, method 400 checks whether the SQL aggregation query and the package access pattern belong to a special case. In some embodiments, the special case may include two types of special cases in PTIME, namely CP or CNP.

[0355] At 420, if the SQL aggregation query and the package access mode are special cases, then method 400 determines that the SQL aggregation query is bounded and evaluable under the package access mode.

[0356] Figure 5 A flowchart is shown for another exemplary method 500 for checking whether an SQL aggregation query is bounded and evaluable in package access mode, according to one aspect. At 510, method 500 checks whether the SQL aggregation query is within a valid syntax for a bounded and evaluable query. A valid syntax is... Figure 7 As described in the text, it is a bounded, measurable RA. aggr The core subclass of the query is syntactically checkable without sacrificing expressiveness.

[0357] At 520, if the SQL aggregation query is in a valid syntax for a bounded evaluable query, then method 500 determines that the SQL aggregation query is bounded evaluable in the package access mode.

[0358] Figure 6 A flowchart is shown of an exemplary method 600 for generating a bounded query plan according to one aspect. At 610, method 600 retrieves a finite amount of data for each relation appearing in an SQL aggregation query. At 620, method 600 performs the SQL aggregation query operation on the retrieved data.

[0359] In some embodiments, method 600 can generate bounded logical access paths for relations in the SQL aggregation query. The bounded logical access path retrieves all values ​​(partial tuples) of the relation schema, which are necessary to evaluate the SQL aggregation query using a package access schema. Intuitively, the bounded logical access path functions similarly to a traditional DBMS access path. However, instead of accessing complete tuples through scanning or indexing, the bounded logical access path uses the package access schema to retrieve values ​​(partial tuples), thus limiting the amount of data accessed.

[0360] Figure 7 A block diagram of an exemplary system 700 for querying large amounts of data with limited resources is shown, according to one aspect. The system 700 includes an interface component 710, an inspection component 720, a planning component 730, and an execution component 740. The interface component 710 is configured to receive SQL aggregation queries proposed to a database. The inspection component 720 is configured to inspect whether the SQL aggregation query is bounded and estimable in package access mode. The planning component 730 is configured to generate a bounded query plan when the SQL aggregation query is bounded and estimable in package access mode. The execution component 740 is configured to execute the SQL aggregation query on a bounded dataset identified by the bounded query plan.

[0361] In some embodiments, the system 700 further includes a discovery component 750, a construction component 760, and a maintenance component 770. The discovery component 750 is configured to discover packet access patterns from instances of database patterns. The construction component 760 is configured to build an index of the packet access patterns on the database. The maintenance component 770 is configured to maintain the packet access patterns in response to updates to the database.

[0362] In some embodiments, the inspection component 720 is further configured to check whether the SQL aggregation query and the package access mode belong to a special case. If the SQL aggregation query and the package access mode belong to a special case, then it is determined that the SQL aggregation query is bounded and evaluable under the package access mode.

[0363] In some embodiments, the checking component 720 is further configured to check whether the SQL aggregation query is in a valid syntax for a bounded evaluable query, and if the SQL aggregation query is in a valid syntax for a bounded evaluable query, then determine that the SQL aggregation query is bounded evaluable in package access mode.

[0364] In some embodiments, the planning component 730 is further configured to retrieve a limited number of data for each relation appearing in the SQL aggregation query; and to perform the SQL aggregation query operation on the retrieved data.

[0365] In some embodiments, the planning component 730 is further configured to generate a partially bounded query plan when the SQL aggregation query is not bounded and evaluable. The execution component 740 is further configured to optimize and execute the partially bounded query plan to maximize the utilization of access constraints in the package access pattern.

[0366] Exemplary Implementation

[0367] Based on the embodiments discussed above, we will now introduce an exemplary implementation called BEAS, a framework for querying large datasets. We will then provide the underlying algorithms of the framework for checking bounded estimability and generating bounded plans.

[0368] BEAS framework, for example Figure 9 As shown. Given an application that involves querying instances of a database schema R, BEAS works as follows.

[0369] Offline preprocessing. For example... Figure 8 As shown in C1, as an offline preprocessing step, BEAS discovers the packet access pattern B from the (sample) instance of R, builds an index of B on the database D of R that is in use, and maintains B in response to updates to D.

[0370] Online processing. When a user submits a request for RA on D... aggr When querying Q, BEAS first checks whether Q is bounded and evaluable under B (C2). If so, it generates a bounded query plan ξ for Q under B (C3), which is interpreted as an SQL query for Q. ξ Therefore, the bounded dataset D identified by the underlying DBMS in the plan ξ can be determined by the underlying DBMS. Q Execute directly (C4). If Q is not bounded and evaluable, it will generate a partially bounded query plan ξ for Q. 0 This is to maximize the use of access constraints in B (C5). The (partial) bounded plan is optimized and executed by the DBMS (C4).

[0371] It's important to note that the BEAS framework does not require changes to the underlying DBMS. In fact, it only interacts with the DBMS via SQL. Therefore, BEAS can be built on top of any existing DBMS, providing bounded evaluation capabilities.

[0372] BEAS can also compute approximate answers to unbounded queries under resource constraints and provides a guarantee of deterministic accuracy in access mode.

[0373] Below, we will develop algorithms for the C2 and C3 components of BEAS respectively.

[0374] Checking bounded assessability

[0375] Next, we will develop a practical algorithm for the C2 component of BEAS. Under package access mode B, given an RA... aggr The query Q determines whether Q is bounded and measurable.

[0376] Therefore, we first check in PTIME whether Q and B belong to two special cases, namely C. P Or C NP If so, their bounded assessability can be efficiently determined, as shown in the proofs of Theorems 2 and 4. Otherwise, we check whether Q is valid for use with RA. aggr Valid syntax L B Below, we present a PTIME algorithm for this purpose.

[0377]

[0378] Algorithm BEChk. This algorithm, denoted by BEChk, is shown in Algorithm 1. It first sets BA(Q,B), BR(Q,B), and BQ(Q,B) as... Then it uses Figure 8 The rules in the code iteratively update BA(Q,B), BR(Q,B), and BQ(Q,B). In each iteration, it...

[0379] (a) First, use γ1, γ2 and γ3 to calculate BA(Q,B) (row 3);

[0380] (b) Then update BR(Q,B) using γ4 (line 4); and (c) it finally updates BQ(Q,B) using γ5 (line 5).

[0381] The iteration continues until BQ(Q,B) can no longer be updated (line 6). Return "Yes" if Q∈BQ(Q,B), otherwise return "No" (lines 7-8). Steps (b) and (c) are straightforward in each iteration. We will discuss step (a) in more detail below.

[0382] Calculate BA(Q,B) (line 3 of Algorithm 1). In each iteration, BA(Q,B) is updated in two steps, as shown below.

[0383] (1) Establish a general relation. We first construct a "general schema" U of Q with respect to BQ(Q,B). Q The mapping function ρ maps the attributes and aggregate fields of Q to U. Q The property of. If and only if In the selection criteria for Q, for any two attributes R[A] and S[B] of Q, ρ(R[A]) = ρ(S[B]). For the aggregate field agg(A) and attribute R[B], ρ(R[A]) = ρ(S[B]) only if agg(A) is in Z QsIn the middle of the time (recall table 1), for some Q s ∈BQ(Q,B), ρ(agg(A))=ρ(R[B]). Therefore, the package constraints in B are also mapped to U through ρ. Q .

[0384] (2) Calculate the fetch closure. Then we simplify the calculation of BA(Q,B) to U Q The calculation of the closure is obtained using B with respect to ρ. For U Q A set of W properties, whose fetch closure is denoted as W. B , is a set of U Q The property makes

[0385] (i)

[0386] (ii) If And φ=R(|X→Y,N|)∈B such that ρ(R[X])=X 0 And ρ(R[Y])=Y 0 ,but and

[0387] (iii)W B It contains no other content.

[0388] Let W = ρ(X) Q c )∪ρ(BA(Q,B))∪ S Q s∈ BQ(Q,B) ρ(Z Qs We set BA(Q,B)={A∈X) Q |ρ(A)∈W B (See Z in Table 1) Q X Q ).

[0389] Example 8: Recall Q2 in Example 2 and packet access pattern B1 in Example 4. Algorithm BEChk iteratively updates BA, BR, and BQ for Q2, which were initially...

[0390] In the first iteration, BECHk starts by updating BA(Q2,B1) (row 3). To do this, it constructs a general relation U using the function ρ. Q2 = {f.uid, f.fid, c.cty, c.date}, mapping c.uid to f.fid while keeping all other properties unchanged (f and c represent friends and check-in respectively). Because ρ(X Q c 2) = {f.uid, c.cty} and BEChk sets W to ρ(X) Q c 2) And calculate the acquire closure W of W. B1 , producing W B1 = {f.uid, f.fid, c.cty}. Therefore, it updates BA(Q2, B1) to {f.uid, f.fid, c.cty}. Then it updates BR(Q2, B1) to {f, c}, since all the non-trivial properties of f and c are already in BA(Q2, B1) (line 4). BEChk eventually updates BQ(Q2, B1) to {Q3, Q2} (line 5) and terminates in the next iteration, returning "yes".

[0391] Q6 in Example 5 and B2 in Example 4 involve more. In the first iteration, BEChk constructs a general pattern U by preserving the mapping function ρ of the R and S attributes. Q6 ={A,B,C,E,F,W,F 0}, and map the aggregate field (i.e., the output) sum(y) of Q7 to F 0 It should be noted that, although because ρ does not map sum(y) to E. BEChk then calculates X. Q C 6 in U Q6 It fetches the closure of R and sets BA(Q6,B2) to {B,C,F}. Then it finds that all non-trivial properties of R are in BA(Q6,B2), so it updates BR(Q6,B2) to {R}. Therefore, it also sets BQ(Q6,B2) to {Q7}. In the second iteration, BEChk constructs an updated general relation U. Q6 ={A,B,C,E,F,W}, because Q7∈BQ(Q6,B2) and It continues by updating BA(Q6,B2) to {B,C,E,F,W}, BR(Q6,B2) to {R,S}, and BQ(Q6,B2) to {Q7,Q6}. It terminates after the third iteration, returning "Yes" for Q6 under B2.

[0392] Correctness and complexity. To view BEChk correctness checks... Figure 8 Valid syntax L B Please observe the following content. (1)L B For any fixed BQ(Q,B), the corresponding BA(Q,B) determined by the rules γ1,...,γ is the fetch closure W. B (Recalling the previous text W) B(2) The while loop propagates the changes from BA to BR and BQ, and finally back to BA, until it reaches the definition of BA. Figure 8 A fixed point in the rules.

[0393] BEChk can be in O(p Q It is implemented in time (||Q|||B|+|Q|)), where p Q Let |Q|| be the number of subqueries in Q, |Q|| be the number of relation atoms in Q, |Q| be the number of attributes and aggregate fields in the relation atoms and predicates of Q, and |B|| and |B| be the number and total length of package constraints in B, respectively (see Table 1). In fact, calculating the fetch closure can be done in O(|Q||||B||) time, therefore each iteration takes O(|Q||||B||+|Q|) time; at most p Q The next iteration.

[0394] Algorithm BEChk is an effective syntax L in Theorem 7 B Attribute (c) provides a constructive proof, namely, checking RA in PTIME. aggr Query whether Q ∈ L B .

[0395] This completes the proof of Theorem 7.

[0396] Table 2: Table with parameters c()

[0397]

[0398] Generate bounded plans

[0399] We will next provide the underlying algorithm of the BEAS component C3, represented by BPlan. Given a packet access pattern B and a RA... aggr Given a query Q, which is determined by BEChk in Section 5.2 to be bounded and evaluable under B, BPlan generates a bounded RA for Q under B. aggr Query plan.

[0400] Algorithm BPlan. Given a bounded and evaluable RA. aggr Given a query Q∈LB, BPlan generates a bounded plan ξQ for Q under B, as follows: (1) Obtain the bounded amount of data for each relation R that appears in Q, and (2) Perform the operations of Q on the obtained data. Although step (2) is simple, step (1) is quite complex.

[0401] To perform step (1), BPlan generates bounded logical access paths (bLAPs). The bLAP ξ is used for relation R in Q. RObtain all values ​​(partial tuples) of R, which are necessary for evaluating Q using B; additionally, ξ R It is a bounded RA under B. aggr Intuitively, bLAPs function similarly to traditional DBMS access paths. However, instead of accessing complete tuples through scanning or indexing, bLAPs use B to retrieve values ​​(partial tuples), thus limiting the amount of data accessed.

[0402] More specifically, we present an algorithm, denoted by BAP, as a subprocess of BPlan, to find bLAPξ under B for R. R While there may be an exponential number of such bLAPs, BAP is designed to compute those at minimal cost.

[0403] Calculate bLAPξ for each relation R in BAP for Q. R Then, the algorithm BPlan replaces each R in Q with its bLAPξ. R and for ξ R The retrieved data is used to perform the RA of Q. aggr The operation generates a bounded plan ξ for Q under B. Q .

[0404] In the remainder of this section, we will focus on the BAP algorithm.

[0405] Parametric Cost Measurement. To evaluate the quality of bLAPs discovered by BAP, we begin with a general class of cost functions. Traditional access path measures evaluate the cost of physical table access methods, such as sequential scans and index scans. These metrics are not applicable to bLAPs, which involve, for example, fetch and join. Therefore, BAP employs a general cost function c(ξ). R ), taking the user-specified function as an argument, and ξ R Various cost measurements on the platform are represented as bLAPs, such as output size, data access, etc.

[0406] For B under R, bLAPξ R The cost, expressed as c(ξ) R This indicates that, as summarized in Table 2, there are five user-configurable parameter functions Γ1, Γ2, and Γ3. U ,Γ - ,Γ fetch and Γ gpBy .

[0407] By parameterizing these user-configurable functions, we can support a wide range of measurements for bLAPs. For example, to estimate the worst-case output size of ξ, we simply set (i) c1*c2、(ii)Γ fetch(c',N) is c0 *N、(iii)Γ ∪ (c1,c2) ​​is c1+c2, (iv)Γ-(c1,c2) ​​is c1, and (v) if c≠0, Γ gpBy ( c0 c) is c0 Otherwise, it is 1 (when When, assume λ π (X) = 0).

[0408] The BAP algorithm works in two steps:

[0409] (1) It simplifies bLAPs to proofs of R∈BR(Q,B) and encodes all proofs in PTIME using a directed graph G(Q,B); and

[0410] (2) It searches G(Q,B) to find a proof with minimum cost, where one proof corresponds to a subgraph in the search trajectory.

[0411] The proof for R∈BR(Q,B) is as follows: Figure 8 A series of applications of the rules given in [the document]. Each step of the proof corresponds to bLAPξ of R. R One or more operations.

[0412] Below we outline BAP:

[0413] (1) Simplification. It simplifies the problem of generating bLAPs for Q under B to find proofs of R∈BR(Q,B). It encodes all proofs of R∈BR(Q,B) (and therefore all bLAPs are R) in a weighted directed graph G(Q,B), where (a) the nodes R(|X→Y,N|)∈B encode the attributes R[X] and R[XY] in the constraints, and (b) the nodes encode the relations and subqueries of Q. Edges are encoded for value propagation between them. It ensures that every proof of R∈BR(Q,B) is generated from virtual nodes. Traverse to the node u that encodes R in G(Q,B) R Encode the graph. Graph G(Q,B) has at most 2||B||+|Q| nodes and ||B||(||B||+|Q|) edges.

[0414] We will use the following example to illustrate the simplified diagram.

[0415] Example 9: Recall the RA in Example 2 aggr Query Q2 and Q6 of Example 5, as well as package access patterns B1 and B2 in Example 4. Figures G(Q2,B1) and G(Q6,B2) are shown below. Figure 10 As shown. Here It is a virtual node connected to all constant properties in Q. Edges with numerical weights will use... Figure 8 The rule γ3 encoding derivation steps, where the weight is the cardinality N of the corresponding access constraint. 0 .

[0416] As shown below, the proof of relation R∈BR(Q,B) can be encoded as G(Q,B) from to u R The traversal.

[0417] (2) Conditional Dijkstra's Search. Then, the BAP algorithm employs a Dijkstra-like search on G(Q,B), starting from the virtual nodes. To the relation node u of encoding R R This makes the trajectory encoding B under R a bLAP (i.e., the proof). It extends Dijkstra's algorithm

[29] as follows.

[0418] Conditional extension. Using U u This represents the attribute, relation, or subquery encoded by node u in G(Q,B). Note that U u This can be derived from attributes or subqueries encoded by multiple predecessor nodes of u, as a precondition in the proof of R∈BR(Q,B). To capture this, in U u BAP can access a new node u under conditions obtained from u's predecessor nodes, such as joins or fetch.

[0419] With this condition, BAP ensures that for node u encoding relation R∈BR(Q,B), in G(Q,B)... To find the bLAP of the relation atom R in Q under the traversal encoding B of u, let's consider the following example.

[0420] Example 10: The proof of f∈BR(Q2,B1) (where f represents a friend) consists of derivation steps (1), (2), and (4) from Example 7. It is derived from G(Q2,B1) from... to u f The unique path encoding; similarly, prove c∈BR(Q2,B1).

[0421] A more informative example is S∈BR(Q6,B2). Its proof is also described in Example 7. The proof is derived from... to u S The traversal encoding is that there are no edges (u s ,u Q6 G(Q6,B2), such as Figure 10 As shown. It should be noted that although there is from... to uS Neither of the two simple paths is a valid traversal due to condition expansion.

[0422] The bLAPs encoded by these proofs are precisely the subprograms of Q2 and Q6 given in Example 5. In fact, the bLAPξ of the relation c of Q2 under B1 is... c It is the bounded plan ξ in Example 5. Q2 (T1,T2), and for f, bLAPξ f That is, T1. Similarly, for R of Q6 under B2, bLAPξ R That is, ξ in Example 5 Q4 T1; for S's bLAPξ S It is ξ Q4 (T1,T2,T3).

[0423] It is important to note that a bLAP may involve multiple relations through fetching and joining; for example, for ξ of S in Q6. S Therefore, its cost cannot be evaluated using traditional access path measurements, as these methods were developed for evaluating access methods of individual relationships, such as sequential or indexed scans.

[0424] Search revisions. Note that RA... aggr The output of the subquery can be used to retrieve the already inferred attributes, possibly with a smaller value determined by c(ξ). R This reduces costs. To maintain optimality of the search, when a node u encoded by a subquery of Q is visited, the algorithm BAP checks whether this produces a better bLAP by starting a new search from u and marking all nodes as unvisited. If it cannot further improve the bLAP of the previous search, it terminates.

[0425] Example 11: Continuing from Example 9, suppose we use c(ξ) R ) to represent ξ R The worst-case output size (recall the parameter function described earlier). Then BAP calculates the exact bLAP for Q2 and Q6 under B1 and B2 respectively, as described in Example 10. Specifically, for Q2 under B1, c(ξ) f ) = 5000 and c(ξ) c ) = 5000 * 193; For Q6 under B2, c(ξ) R ) = 10 and c(ξ) S =10. In this case, calculate ξ for the relation S of Q6 under B2. S At that time, because of the aggregate subquery Q7, it restarts the search once, which does not improve bLAPsξ R and ξ S .

[0426] Correctness and complexity. The correctness of the BAP algorithm is guaranteed by the following points: (1) Each search trajectory of BAP encodes the proof of R∈BR(Q,B); (2) The proof of R∈BR(Q,B) encodes the bLAP of R under B. It can be O(|Q||B|(||B||+|Q|log(|Q|+2||B||)))-time(ignore c(ξ) R The complexity of the parameter function is considered when implementing BAP. It can be verified that BAP restarts at most N times, where N is the number of nodes in G(Q,B).

[0427] Optimality. Algorithm BAP can achieve c(ξ) R Find the optimal bLAP from a large class of parameter functions.

[0428] Experimental research

[0429] We developed BEAS@PG by extending PostgreSQL with bounded evaluation. Using a benchmark and two real-world datasets, we conducted four sets of experiments to evaluate (1) the overall performance of BEAS@PG over PostgreSQL; (2) the effectiveness of bounded evaluation for bounded queries; and (3) the effectiveness of bounded evaluation for unbounded queries.

[0430] Experiment setup. Let's start with the setup.

[0431] benchmark We used the TPCH benchmark. It uses TPCH dbgen to generate 8 relations with 61 attributes at different scales. It includes 22 built-in benchmark queries.

[0432] Real-world datasets. We also used two real-world datasets.

[0433] USAir Carriers (AIRCA) records flight and statistics for US airlines. It includes on-time performance data for departures and arrivals, as well as carrier statistics and segmented data for the airline market. It has 3 tables, 200 attributes, and approximately 16GB of data, recording data from 1990 to 1997.

[0434] UKMOTdata (UKMOT) integrates anonymized data recording MOT tests and results, as well as roadside surveys including vehicle observations as vehicles pass through UK observation points. It has 3 tables, 42 attributes, and approximately 16GB of data from 2007 to 2011.

[0435] Query. To test the impact of query structure on the effectiveness of bounded evaluation, we designed a generator to generate queries with different structures on two real datasets. More specifically, we manually created 30 query templates for each of the two datasets (Q1-Q15 are bounded and evaluable, Q16-Q30 are unbounded), with 0 to 4 joins. The generator populated these templates by randomly instantiating parameters in the templates using values ​​from the datasets, generating 150 queries for each real dataset.

[0436] Access Mode We constructed access patterns with 59, 18, and 14 access constraints on TPCH, AIRCA, and UKMOT, respectively. We extended TANE, an algorithm for discovering functional dependencies, to first find candidate constraints φ = R(|X→Y,N|) on a small 100MB dataset and sort them by their cardinality N. Then, we checked whether their N was insensitive to the size of the dataset D by changing the size of D, for example, to 200MB and 500MB. We selected those access constraints with small and size-insensitive N such that the total size of the index was at most three times the size of its D.

[0437] Configuration. For the DBMS, we used PostgreSQL 9.6 with all optimizations enabled (BEAS@PG was built using PostgreSQL 9.6). To support PostgreSQL, in addition to the indexes for access constraints, we also built the following additional indexes for PostgreSQL: (1) For each access constraint R(|X→Y,N|), we also built a B-tree index on attribute X on R; (2) We built all primary key and foreign key indexes; (3) We also built B-trees on numeric attributes. It should be noted that these are only for PostgreSQL, not built for BEAS@PG. We set the cost measurement parameter for BEAS@PG to the worst-case output size estimate.

[0438] The experiments were conducted on an Amazon EC2 dense storage instance, m4.xlarge, with 16GB of memory, four Intel Xeon E5-2676 vCPUs, and 500GB of EBS SSD storage. Planned build time and build schedule execution time are included in the evaluation time. All experiments were run three times. The average value is reported here.

[0439] Experimental results

[0440] We found the following: (1) BEAS@PG (a PostgreSQL instance with BEAS built on top) outperformed PostgreSQL for every query in all cases, even with additional indexes built for the latter. On average, BEAS@PG outperformed PostgreSQL by 7.32, 9.58 × 10³, and 2.06 × 10⁴ times on the TPCH benchmarks for 16GB, AIRCA, and UKMOT, respectively, with best-case improvements of 40.46, 3.44 × 10⁴, and 2.52 × 10⁵ times. (2) For bounded evaluable queries, BEAS@PG outperformed PostgreSQL by 1.9 × 10⁴ times on AIRCA and 3.6 × 10⁴ times on UKMOT. (3) BEAS@PG was particularly effective for queries with complex joins, such as joins with non-key attributes (AIRCA and UKMOT queries), even for unbounded queries. For example, for queries that are not bounded and evaluable on AIRCA and UKMOT, BEAS@PG improves PostgreSQL performance by an average of 5.97 × 10² and 1.90 × 10³ times, respectively. Even for traditional DBMS best-performing scenarios such as table scans / aggregations and key-foreign key joins (most TPCH queries), BEAS@PG still outperforms PostgreSQL. (4) The storage cost of access pattern indexes is moderate, at 2.98, 0.01, and 0.25 times that of 16GB TPCH, AIRCA, and UKMOT sizes, respectively.

[0441] It should be noted that the above embodiments can be implemented using hardware elements, software elements, or some combination of software and hardware. Hardware elements may include circuits. Software elements may include computer code stored as machine-readable instructions on a tangible, non-transitory machine-readable storage medium. Some embodiments can be implemented using hardware, firmware, and software, or a combination thereof.

[0442] Some embodiments may be implemented in a computing system or computing device that includes memory, which includes instructions and one or more processors that communicate with the memory, the one or more processors executing the instructions to perform the functions or operations described in this disclosure.

[0443] Some embodiments may also be implemented as instructions stored on a machine-readable medium that can be read and executed by a computing platform to perform the operations described in this disclosure. The machine-readable medium may include any mechanism for storing or transferring data in a machine-readable, such as computer-readable, form. For example, a machine-readable storage medium may include read-only memory (ROM); random access memory (RAM); disk storage media; optical storage media; flash memory devices; or any other machine-readable storage medium. Some embodiments may also be software products that include a machine-readable medium storing instructions that, when executed, cause one or more processors to perform the functions or operations described in this disclosure.

[0444] Various embodiments of this disclosure have been described for illustrative purposes, but are not intended to be exhaustive or limited to the disclosed embodiments. Many modifications and variations will be apparent to those skilled in the art without departing from the scope and spirit of this disclosure.

Claims

1. A computer-based method for querying large datasets with limited resources, comprising: Receive SQL aggregation queries submitted to the database, the SQL aggregation queries including aggregation extensions of the query under package semantics; Check whether the SQL aggregation query is bounded and evaluable in the package access mode, which supports package semantics and includes a set of package access constraints on the database schema. The package access constraints are access constraints extended by merging multiplicity, wherein the index of the package access constraints simultaneously retrieves different associated values ​​in the database and the number of times each associated value appears. When the SQL aggregation query is bounded and evaluable in the package access mode, a bounded query plan is generated, wherein the bounded query plan includes retrieving a finite amount of data for each relation appearing in the SQL aggregation query and performing the SQL aggregation query on the retrieved data; and Execute the SQL aggregation query on the bounded dataset identified by the bounded query plan.

2. The method according to claim 1, further comprising: If the SQL aggregation query is not bounded and evaluable, generate a partially bounded query plan; Optimize and execute the partially bounded query plan to maximize the use of access constraints in the package access pattern.

3. The method according to claim 1, wherein, Before checking whether the SQL aggregation query is bounded and evaluable in package access mode, the following is also included: Discover packet access patterns from instances of database schemas; Build an index of the packet access patterns on the database; and Maintain the packet access pattern in response to updates to the database.

4. The method according to claim 1, wherein, Verifying that the SQL aggregation query is bounded and evaluable in package access mode includes: Check whether the SQL aggregation query and the package access mode belong to special cases; If the SQL aggregation query and the package access pattern are special cases, then the SQL aggregation query is determined to be bounded and evaluable under the package access pattern. The special cases include two categories of special cases C in PTIME. P and C NP , where C P The class includes SPC-type SQL aggregation queries without self-joins where the cardinality of the package access constraint is greater than the number of relation atoms in the query, C NP The class includes SPC-type SQL aggregation queries with self-joins and where the cardinality of the package access constraints is greater than the number of relation atoms in the query.

5. The method according to claim 4, wherein, Verifying that the SQL aggregation query is bounded and evaluable in package access mode also includes: Check whether the SQL aggregation query is within the valid syntax for bounded evaluable queries; If the SQL aggregation query is in a valid syntax for a bounded evaluable query, then the SQL aggregation query is determined to be bounded evaluable in the package access mode.

6. The method according to claim 1, wherein, The SQL aggregation query includes nested aggregation subqueries.

7. A computing system, comprising: Memory containing instructions, and One or more processors communicating with the memory, wherein the one or more processors execute the instructions to implement a method for querying large amounts of data with limited resources by: Receive SQL aggregation queries submitted to the database, the SQL aggregation queries including aggregation extensions of the query under package semantics; Check whether the SQL aggregation query is bounded and evaluable in the package access mode, which supports package semantics and includes a set of package access constraints on the database schema. The package access constraints are access constraints extended by merging multiplicity, wherein the index of the package access constraints simultaneously retrieves different associated values ​​in the database and the number of times each associated value appears. When the SQL aggregation query is bounded and evaluable in the package access mode, a bounded query plan is generated, wherein the bounded query plan includes retrieving a finite amount of data for each relation appearing in the SQL aggregation query and performing the SQL aggregation query on the retrieved data; and Execute the SQL aggregation query on the bounded dataset identified by the bounded query plan.

8. The computing system according to claim 7, wherein, The one or more processors further execute the instructions to: If the SQL aggregation query is not bounded and evaluable, generate a partially bounded query plan; Optimize and execute the partially bounded query plan to maximize the use of access constraints in the package access pattern.

9. The computing system according to claim 7, wherein, Before checking whether the SQL aggregation query is bounded and evaluable in package access mode, the one or more processors further execute the instructions to: Discover packet access patterns from instances of database schemas; Build an index of the packet access patterns on the database; and Maintain the packet access pattern in response to updates to the database.

10. The computing system according to claim 7, wherein, The one or more processors execute the instructions to check whether the SQL aggregation query is bounded and evaluable in package access mode, including: Check whether the SQL aggregation query and the package access mode belong to special cases; If the SQL aggregation query and the package access pattern are special cases, then the SQL aggregation query is determined to be bounded and evaluable under the package access pattern. The special cases include two categories of special cases C in PTIME. P and C NP , where C P The class includes SPC-type SQL aggregation queries without self-joins where the cardinality of the package access constraint is greater than the number of relation atoms in the query, C NP The class includes SPC-type SQL aggregation queries with self-joins and where the cardinality of the package access constraints is greater than the number of relation atoms in the query.

11. The computing system according to claim 10, wherein, The one or more processors execute the instructions to check whether the SQL aggregation query is bounded and evaluable in package access mode, including: Check whether the SQL aggregation query is within the valid syntax for bounded evaluable queries; If the SQL aggregation query is in a valid syntax for a bounded evaluable query, then the SQL aggregation query is determined to be bounded evaluable in the package access mode.

12. The computing system according to claim 7, wherein, The SQL aggregation query includes nested aggregation subqueries.

13. A computer-readable storage medium comprising computer instructions, which, when executed by one or more processors, cause the one or more processors to perform a method for querying large amounts of data with limited resources: Receive SQL aggregation queries submitted to the database, the SQL aggregation queries including aggregation extensions of the query under package semantics; The SQL aggregation query is checked to determine whether it is bounded and evaluable under package access mode, which supports package semantics and includes a set of package access constraints on the database schema. These package access constraints are access constraints extended by merging multiplicity. The index of the package access constraint simultaneously retrieves different associated values ​​in the database and the number of times each associated value appears; When the SQL aggregation query is bounded and evaluable in the package access mode, a bounded query plan is generated, wherein the bounded query plan includes retrieving a finite amount of data for each relation appearing in the SQL aggregation query and performing the SQL aggregation query on the retrieved data; and Execute the SQL aggregation query on the bounded dataset identified by the bounded query plan.

14. The computer-readable storage medium of claim 13, further comprising instructions for causing the one or more processors to perform the following operations: If the SQL aggregation query is not bounded and evaluable, generate a partially bounded query plan; Optimize and execute the partially bounded query plan to maximize the use of access constraints in the package access pattern.

15. The computer-readable storage medium of claim 13, further comprising instructions for causing the one or more processors to perform the following operations: Discover packet access patterns from instances of database schemas; Build an index of the packet access patterns on the database; and Maintain the packet access pattern in response to updates to the database.

16. The computer-readable storage medium of claim 13, further comprising instructions for causing the one or more processors to perform the following operations: Check whether the SQL aggregation query and the package access mode belong to special cases; If the SQL aggregation query and the package access pattern are special cases, then the SQL aggregation query is determined to be bounded and evaluable under the package access pattern. The special cases include two categories of special cases C in PTIME. P and C NP ,in, C P The class includes SPC-type SQL aggregation queries without self-joins where the cardinality of the package access constraint is greater than the number of relation atoms in the query, C NP The class includes SPC-type SQL aggregation queries with self-joins and where the cardinality of the package access constraints is greater than the number of relation atoms in the query.

17. The computer-readable storage medium of claim 16, further comprising instructions for causing the one or more processors to perform the following operations: Check whether the SQL aggregation query is within the valid syntax for bounded evaluable queries; If the SQL aggregation query is in a valid syntax for a bounded evaluable query, then the SQL aggregation query is determined to be bounded evaluable in the package access mode.

18. The computer-readable storage medium according to claim 13, wherein, The SQL aggregation query includes nested aggregation subqueries.

Images