DETAILED ACTION
Notice of Pre-AIA or AIA Status
The present application, filed on or after March 16, 2013, is being examined under the first inventor to file provisions of the AIA .
Claim Status
Claims 1-22 are pending.
Response to Arguments
101 rejection: Applicant’s arguments, filed 11/10/2025, with respect to claims 1-22 have been fully considered and are persuasive. The limitation “performing the group-by pushdown operation on the query comprises modifying at least a given branch of the two or more branches to include a respective group-by clause that groups results of the given branch by the one or more columns of the given branch” integrates the judicial exception into the technological improvement disclosed in the specification (Para 0016, Group-by and distinct placement optimizations may take place in the union-all branches with pushed-down group-by and distinct respectively. This may make joins in the branches more efficient). Accordingly, the 101 rejection of claims 1-22 has been withdrawn.
Prior Art Rejection: Applicant’s arguments with respect to claims 1-19, and 21 have been fully considered and are persuasive. Upon further consideration, and in view of applicant’s amendments, a new grounds of rejection is made in view of newly cited reference Li.
Applicant's arguments with respect to claim 20 and 22 have been fully considered but they are not persuasive. Applicant argues Fender does not teach or suggest, "the outer query block comprises an outer query statement specifying one or more columns of the union-all view and a distinct keyword that removes duplicates of results of the outer query statement”. Examiner disagrees with this argument because the office action does not claim this limitation is taught by Fender. Applicant argues Fender does not teach or suggest, "performing the distinct pushdown operation on the query comprises modifying at least a given branch of the two or more branches to include a distinct keyword that removes duplicates of results of the given branch". Examiner disagrees with this assessment because Fender Para 0076 discloses “At step 103, a distinct operation is applied to the identifier column to remove duplicates”.
Claim Rejections - 35 USC § 103
The following is a quotation of 35 U.S.C. 103 which forms the basis for all obviousness rejections set forth in this Office action:
A patent for a claimed invention may not be obtained, notwithstanding that the claimed invention is not identically disclosed as set forth in section 102, if the differences between the claimed invention and the prior art are such that the claimed invention as a whole would have been obvious before the effective filing date of the claimed invention to a person having ordinary skill in the art to which the claimed invention pertains. Patentability shall not be negated by the manner in which the invention was made.
Claims 1-19, and 21 are rejected under 35 U.S.C. 103 as being unpatentable over Chen et al (US 20030055814 A1) hereafter Chen in view of Songting (US 8918388 B1) hereafter Songting further in view of Li et al (US 5418950 A) hereafter Li
Regarding claim 1, Chen teaches a computer-executed method comprising: compiling, by a database management system, a query, wherein: the union-all operator combines result sets of two or more branches (Para 0025, one table is joined with the result table of a UNION ALL of two input tables); the outer query block comprises an outer query statement referencing one or more columns of the union-all view and a group-by statement that groups results of the outer query statement (Para 0035, For aggregation column functions with group-by applied to the result of a UNION ALL operator, a set of new column functions is provided to apply the specified column functions with the group-by to each input table of the UNION ALL); each branch in the two or more branches projects one or more columns (Para 0020, The indexes 8 provide an ordered set of pointers to data in the table 10 based on the data in one or more columns of the table); compiling the query comprises performing a group-by pushdown operation on the query to form a modified query (Para 0038, logic implemented by the query engine 20 to perform a SUM operation on column C grouped by column C prime of the UNION ALL of two input tables T1 and T2, beginning at block 100); and performing the group-by pushdown operation on the query comprises modifying at least a given branch of the two or more branches to include a group-by clause that groups results of the given branch by the one or more columns of the given branch(Para 0038, The summed values for each group according to column C prime in tables T1 and T2 are then added together to return the total of the values in column C in tables T1 and T2 grouped by the values in column C prime); and executing, by the database management system, the modified query (Para 0029, produce a final result table that is materialized at block 62).
Chen does not appear to explicitly teach the query comprises an outer query block containing a view with a union-all operator that forms a union-all view within a FROM clause of the outer query block.
In analogous art, Songting teaches the query comprises an outer query block containing a view with a union-all operator that forms a union-all view within a FROM clause of the outer query block (Column 17 lines 28-33, wherein when there are two or more virtual views in the FROM clause, performing a union on all the rows from each individual virtual view and when a specific column referred to in the query does not exist in the view, treat the specific column as a NULL value). It would have been obvious to one having ordinary skill in the art before the effective filing date of the claimed invention to modify Chen to include the teaching of Songting. One of ordinary skill in the art would be motivated to implement this modification in order to in order to perform query processing, as taught by Songting (Abs, A SQL-like, but much more succinct, query language is provided to leverage the virtual view).
Chen in view of Songting does not appear to explicitly teach performing the group-by pushdown operation on the query comprises modifying at least a given branch of the two or more branches to include a respective group-by clause that groups results of the given branch by the one or more columns of the given branch, wherein modifying the at least a given branch includes not modifying another branch of said two or more branches to include a group-by clause in response to determining said another branch is not valid for the group-by pushdown operation.
In analogous art, Li teaches performing the group-by pushdown operation on the query comprises modifying at least a given branch of the two or more branches to include a respective group-by clause that groups results of the given branch by the one or more columns of the given branch(Para 47, The GROUP-BY clause in a SQL SELECT statement specifies the columns upon which a regrouping will be performed on the selected rows of the referenced tables), wherein modifying the at least a given branch includes not modifying another branch of said two or more branches to include a group-by clause in response to determining said another branch is not valid for the group-by pushdown operation (Para 47, After the regrouping, all rows (records) within any one group have the same value for the GROUP-BY columns. This further step is performed after the qualified rows from referenced tables and views have been selected). It would have been obvious to one having ordinary skill in the art before the effective filing date of the claimed invention to modify Chen in view of Songting to include the teaching of Li. One of ordinary skill in the art would be motivated to implement this modification in order to improve database systems, as taught by Li (Para 2, This invention generally relates to improvements in computer based filing systems and more particularly to a system and method for accessing information in a database application).
Regarding claim 2, Chen in view of Songting further in view of Li teaching the method of claim 1, wherein compiling the query further comprises: responsive to determining that another branch of the two or more branches contains a single table, has a unique column, and the unique column is a non-aggregation column, determining that the another branch is not considered valid for the group- by pushdown operation (Li, Para 47, This further step is performed after the qualified rows from referenced tables and views have been selected. The GROUP-BY operation basically rearranges the qualified rows based on the specified GROUP-BY columns). It would have been obvious to one having ordinary skill in the art before the effective filing date of the claimed invention to modify Chen in view of Songting to include the teaching of Li. One of ordinary skill in the art would be motivated to implement this modification in order to provide a database interface, as taught by Li (Para 20, it is a primary object of the present invention to provide a user with a database interface matching a user's mental model for defining SQL SELECT statements).
Regarding claim 3, Chen in view of Songting in view of Li teaches the method of claim 2, wherein the one or more criteria comprise at least one branch of the two or more branches being a branch that is valid for the group-by pushdown operation (Li, Para 47, This further step is performed after the qualified rows from referenced tables and views have been selected. The GROUP-BY operation basically rearranges the qualified rows based on the specified GROUP-BY columns). It would have been obvious to one having ordinary skill in the art before the effective filing date of the claimed invention to modify Chen in view of Songting to include the teaching of Li. One of ordinary skill in the art would be motivated to implement this modification in order to provide a database interface (Para 20, it is a primary object of the present invention to provide a user with a database interface matching a user's mental model for defining SQL SELECT statements).
Regarding claim 4, Chen in view of Songting in view of Li teaches the method of claim 1, wherein: the outer query statement includes an aggregate function; and performing the group-by pushdown operation on the query further comprises performing a decomposition operation on the aggregate function of the outer query statement; and performing the decomposition operation comprises: modifying the given branch to include a branch aggregate function; assigning an alias to the branch aggregate function; and modifying the aggregate function of the outer query statement, wherein modifying the aggregate function of the outer query statement comprises modifying the aggregate function of the outer query statement to reference the branch aggregate function by the alias as an argument (Li, Para 55, In the user interface, each entry of a table list includes a field for the table name and a field for the corresponding alias. If it is a base table or view entry, the alias field is an editable field displaying the alias name. If it is a logical table, the alias field is not editable and displays no text in an inactive or "greyed" shade). It would have been obvious to one having ordinary skill in the art before the effective filing date of the claimed invention to modify Chen in view of Songting to include the teaching of Li. One of ordinary skill in the art would be motivated to implement this modification in order to improve database systems, as taught by Li (Para 2, This invention generally relates to improvements in computer based filing systems and more particularly to a system and method for accessing information in a database application).
Regarding claim 5, Chen in view of Songting in view of Li teaches the method of claim 4, wherein: the aggregate function of the outer query statement is a count function; modifying the aggregate function of the outer query statement comprises replacing the count function with a sum function; and the branch aggregate function is a branch count function (Chen, Para 0039, The query engine 20 separately determines (at block 122) the COUNTs of T1 and T2 and SUMs on column C of T1 and T2).
Regarding claim 6, Chen in view of Songting in view of Li teaches the method of claim 4, wherein modifying the aggregate function of the outer query statement comprises: replacing the aggregate function of the outer query statement with a first aggregate function in response to a determination that the outer query block involves a first type of join operation; and replacing the aggregate function of the outer query statement with a second aggregate function that is different from the first aggregate function in response to a determination that the outer query block involves a second type of join operation (Chen, Para 25, It is known that there are two ways to implement the JOIN operator, namely, either at the map phase or at the reduce phase. The implementation at the reduce phase may be more general and applicable to all scenarios).
Regarding claim 7, Chen in view of Songting in view of Li teaches the method of claim 6, wherein the first type of join operation is a join operation where the union-all view is inner joined or on a left of a left outer join and the second type of join operation is a join operation where the union-all view is on a right of a left outer join (Chen, Para 25, For example, T1 JOIN (T2 UNION ALL T3) becomes (T1 JOIN T2) UNION ALL (T1 JOIN T3)).
Regarding claim 8, Chen in view of Songting in view of Li teaches the method of claim 6, wherein modifying the aggregate function of the outer query statement comprises replacing the aggregate function of the outer query statement with the first aggregate function in response to a determination that the outer query block does not involve a join with the union-all view (Chen, Para 0026, The projection (SELECT clause in SQL queries) applied to the result table of a UNION ALL operator can be performed on each subselect of the UNION ALL before applying the UNION ALL operator).
Regarding claim 9, Chen in view of Songting in view of Li teaches the method of claim 4, wherein performing the decomposition operation further comprises: modifying said another branch of the two or more branches that is not considered valid for the group-by pushdown operation to include an item corresponding to the branch aggregate function; and assigning the alias to the item (Li, Para 55, In the user interface, each entry of a table list includes a field for the table name and a field for the corresponding alias. If it is a base table or view entry, the alias field is an editable field displaying the alias name. If it is a logical table, the alias field is not editable and displays no text in an inactive or "greyed" shade). It would have been obvious to one having ordinary skill in the art before the effective filing date of the claimed invention to modify Chen in view of Songting to include the teaching of Li. One of ordinary skill in the art would be motivated to implement this modification in order to improve database systems, as taught by Li (Para 2, This invention generally relates to improvements in computer based filing systems and more particularly to a system and method for accessing information in a database application).
Regarding claim 10, Chen in view of Songting in view of Li teaches the method of claim 9, wherein the item comprises one of: a column specified by the another branch, a function that returns one if the column specified by the another branch is not null and returns a value of zero if the column specified by the another branch is null, or a value of one (Chen, Para 34, If a specific column referred to in the query does not exist in the view, treat it as a NULL value. To have meaningful query semantics, all group by columns are required to exist in each of the views in the FROM clause).
Regarding claim 11, Chen in view of Songting in view of Li teaches the method of claim 4, wherein: the aggregate function of the outer query statement references an item of the given branch; and modifying the given branch to include the branch aggregate function comprises replacing the item of the given branch with the branch aggregate function (Chen, Para 143, Third, the modified distinct clause removes duplicates per user. Hence the nested query according to an embodiment can be more efficiently processed than by standard methods).
Regarding claim 12, Chen in view of Songting in view of Li teaches the method of claim 4, wherein: the union-all view is a first union-all view; the branch aggregate function is a first branch aggregate function; the outer query block further comprises a second view with a second union-all operator that forms a second union-all view; compiling the query further comprises performing a second group-by pushdown operation on at least one branch of the second union-all operator; performing the second group-by pushdown operation comprises: modifying a particular branch of the second union-all operator to include a second branch aggregate function; and modifying the aggregate function of the outer query statement comprises modifying the aggregate function of the outer query statement to reference the first branch aggregate function of the first union-all view and the second branch aggregate function of the second union-all view by the alias (Li, Para 46, The FROM clause in a SQL SELECT statement specifies the database tables and views to which referenced columns belong. The referenced columns include columns specified in the other clauses of the SELECT statement. There are occasions where the same table must be referenced more than once in the FROM clause. In those cases, each reference should be assigned with its own alias for unique identification and every referenced column should have the appropriate alias for correct identification. When there is no reference column in any other clause, then a user can select table names from a table list). It would have been obvious to one having ordinary skill in the art before the effective filing date of the claimed invention to modify Chen in view of Songting to include the teaching of Li. One of ordinary skill in the art would be motivated to implement this modification in order to improve database systems, as taught by Li (Para 2, This invention generally relates to improvements in computer based filing systems and more particularly to a system and method for accessing information in a database application).
Regarding claim 13, Chen in view of Songting in view of Li teaches the method of claim 4, wherein: the aggregate function of the outer query statement is a sum function having an argument that comes from the union-all view; and the branch aggregate function comprises a sum of a column of the one or more columns (Chen, Para 0037, FIG. 3a illustrates logic implemented by the query engine 20 to perform a SUM operation on column C of the UNION ALL of input tables T1 and T2, which are subselects of base tables, beginning at block 80. The query engine 20 distributes the sums to separately sum (at block 82) the values in column C of tables T1 and T2 and then return (at block 84) the sum of these two intermediate sums of T1 and T2 on column C).
Regarding claim 14, Chen in view of Songting in view of Li teaches the method of claim 4, wherein: the aggregate function of the outer query statement is a sum function having an argument that comes from a table or view for which a group-by pushdown has not been performed; and the branch aggregate function comprises a count of a column of the one or more columns (Chen, Para 0035, For aggregation (column functions with group-by) applied to the result of a UNION ALL operator, a set of new column functions is provided to apply the specified column functions with the group-by (new aggregation) to each input table of the UNION ALL ).
Regarding claim 15, Chen in view of Songting in view of Li teaches the method of claim 4, wherein: the aggregate function of the outer query statement is an average function; and performing the group-by pushdown operation on the query further comprises replacing the average function with a sum function divided by a count function prior to performing the decomposition operation (Chen, Para 0039, FIG. 4a illustrates logic implemented by the query engine 20 to perform an average (AVG) operation on column C of the UNION ALL of tables T1 and T2, beginning at block 120. The query engine 20 separately determines (at block 122) the COUNTs of T1 and T2 and SUMs on column C of T1 and T2. The query engine 20 then adds (at block 124) the SUMs of T1 and T2 on column C together and divides by the sum of the COUNTs of T1 and T2).
Regarding claim 16, Chen in view of Songting further in view of Li teaches the method of claim 1, wherein the outer query block further comprises a join or a filter predicate (Chen, Para 0025, where the join predicate between T1 and the result table of T2 UNION ALL T3 is transformed to the join predicates between T1 and T2 and between T1 and T3).
Regarding claim 17, Chen in view of Songting further in view of Li teaches the method of claim 1, wherein the given branch further includes a join or a filter predicate (Chen, Para 0025, where the join predicate between T1 and the result table of T2 UNION ALL T3 is transformed to the join predicates between T1 and T2 and between T1 and T3).
Regarding claim 18, Chen in view of Songting further in view of Li teaches the method of claim 1, wherein compiling the query further comprises performing a group-by placement operation on the group-by clause of the given branch (Chen, Para 0038, The summed values for each group according to column C prime in tables T1 and T2 are then added together at block 104 to return the total of the values in column C in tables T1 and T2 grouped by the values in column C prime).
Regarding claim 19, Chen in view of Songting further in view of Li teaches the method of claim 1, wherein compiling said query includes selecting said modified query by performing a cost-based transformation, wherein said cost-based transformation compares a cost of said modified query to a cost of said query or a transformation of said query (Chen, Para 0014, The described implementations provide techniques to optimize query processing when query operations are performed on tables derived from set operators).
Claim 21 is the media claim corresponding to the method claim 1, and is analyzed and rejected accordingly.
Claims 20 and 22 are rejected under 35 U.S.C. 103 as being unpatentable over Chen in view of Songting in view of Fender et al (US 20200341981 A1) hereafter Fender
Regarding claim 20, Chen teaches a computer-executed method comprising: compiling, by a database management system, a query, wherein: the union-all operator combines result sets of two or more branches(Para 0025, one table is joined with the result table of a UNION ALL of two input tables); the outer query block comprises an outer query statement specifying one or more columns of the union-all view (Para 0035, For aggregation column functions with group-by applied to the result of a UNION ALL operator, a set of new column functions is provided to apply the specified column functions with the group-by to each input table of the UNION ALL); each branch in the two or more branches projects one or more columns(Para 0038, The summed values for each group according to column C prime in tables T1 and T2 are then added together to return the total of the values in column C in tables T1 and T2 grouped by the values in column C prime); and executing, by a database management system, the modified query(Para 0029, produce a final result table that is materialized at block 62).
Chen does not appear to explicitly teach the query comprises an outer query block and a view with a union-all operator that forms a union-all view within a FROM clause of the outer query block.
In analogous art, Songting teaches the query comprises an outer query block containing a view with a union-all operator that forms a union-all view within a FROM clause of the outer query block (Column 17 lines 28-33, wherein when there are two or more virtual views in the FROM clause, performing a union on all the rows from each individual virtual view and when a specific column referred to in the query does not exist in the view, treat the specific column as a NULL value). It would have been obvious to one having ordinary skill in the art before the effective filing date of the claimed invention to modify Chen to include the teaching of Songting. One of ordinary skill in the art would be motivated to implement this modification in order to in order to perform query processing, as taught by Songting (Abs, A SQL-like, but much more succinct, query language is provided to leverage the virtual view).
Chen in view of Songting does not appear to explicitly teach a distinct keyword that removes duplicates of results of the outer query statement; compiling the query comprises performing a distinct pushdown operation on the query to form a modified query; performing the distinct pushdown operation on the query comprises modifying at least a given branch of the two or more branches to include a distinct keyword that removes duplicates of results of the given branch.
In analogous art, Fender teaches a distinct keyword that removes duplicates of results of the outer query statement (Para 0033, The Pre-Join Semi-Join Rewrite Technique uses a distinct operator to determine whether there are duplicates in the inner relation T2 when projected on the attributes that are referenced in P); compiling the query comprises performing a distinct pushdown operation on the query to form a modified query in response to a determination that the query satisfies one or more criteria(Para 0036, A distinct operation is applied to the tuple identifier to remove duplicates after applying inner-join between the preserving table and the non-preserving table on join predicate P); performing the distinct pushdown operation on the query comprises modifying at least a given branch of the two or more branches to include a distinct keyword that removes duplicates of results of the given branch(Para 0076, At step 103, a distinct operation is applied to the identifier column to remove duplicates. FIG. 5 illustrates the distinct operation result applied to the identifier column of table 400). It would have been obvious to one having ordinary skill in the art before the effective filing date of the claimed invention to modify Chen in view of Songting to include the teaching of Fender. One of ordinary skill in the art would be motivated to implement this modification in order to optimize database queries, as taught by Fender (Para 0002, The present invention relates to the field of database system, and in particular to substituting a semi-join operator with alternative execution strategies and transforming and optimizing representations of queries).
Claim 22 is the media claim corresponding to the method claim 20, and is analyzed and rejected accordingly.
Conclusion
Applicant's amendment necessitated the new ground(s) of rejection presented in this Office action. Accordingly, THIS ACTION IS MADE FINAL. See MPEP § 706.07(a). Applicant is reminded of the extension of time policy as set forth in 37 CFR 1.136(a).
A shortened statutory period for reply to this final action is set to expire THREE MONTHS from the mailing date of this action. In the event a first reply is filed within TWO MONTHS of the mailing date of this final action and the advisory action is not mailed until after the end of the THREE-MONTH shortened statutory period, then the shortened statutory period will expire on the date the advisory action is mailed, and any nonprovisional extension fee (37 CFR 1.17(a)) pursuant to 37 CFR 1.136(a) will be calculated from the mailing date of the advisory action. In no event, however, will the statutory period for reply expire later than SIX MONTHS from the mailing date of this final action.
Any inquiry concerning this communication or earlier communications from the examiner should be directed to Brooks Hale whose telephone number is 571-272-0160. The examiner can normally be reached 9am to 5pm est.
Examiner interviews are available via telephone, in-person, and video conferencing using a USPTO supplied web-based collaboration tool. To schedule an interview, applicant is encouraged to use the USPTO Automated Interview Request (AIR) at http://www.uspto.gov/interviewpractice.
If attempts to reach the examiner by telephone are unsuccessful, the examiner’s supervisor, Sanjiv Shah can be reached on (571) 272-4098. The fax phone number for the organization where this application or proceeding is assigned is 571-273-8300.
Information regarding the status of published or unpublished applications may be obtained from Patent Center. Unpublished application information in Patent Center is available to registered users. To file and manage patent submissions in Patent Center, visit: https://patentcenter.uspto.gov. Visit https://www.uspto.gov/patents/apply/patent-center for more information about Patent Center and https://www.uspto.gov/patents/docx for information about filing in DOCX format. For additional questions, contact the Electronic Business Center (EBC) at 866-217-9197 (toll-free). If you would like assistance from a USPTO Customer Service Representative, call 800-786-9199 (IN USA OR CANADA) or 571-272-1000.
/B.T.H./Examiner, Art Unit 2166
/SANJIV SHAH/Supervisory Patent Examiner, Art Unit 2166
Prosecution Insights