Ting Yu Leung

US Patent:

6339768, Jan 15, 2002

Filed:

Aug 13, 1998

Appl. No.:

09/133335

Inventors:

Ting Yu Leung - San Jose CA
Mir Hamid Pirahesh - San Jose CA

Assignee:

International Business Machines Corporation - Armonk NY

International Classification:

G06F 1730

US Classification:

707 2, 707 3, 707 4

Abstract:

An optimization technique for SQL queries. A query is analyzed to determine whether it includes one or more scalar subqueries and one or more existential subqueries. The scalar subqueries and the existential subqueries are transformed into join operations using subsumption, and the join operations are merged into a single join query block. The join query block can then be executed in the computer to efficiently retrieve data from the relational database.

US Patent:

6339769, Jan 15, 2002

Filed:

Sep 14, 1998

Appl. No.:

09/152551

Inventors:

Roberta Jo Cochrane - Los Gatos CA
George Lapis - San Jose CA
Ting Yu Leung - San Jose CA
Mir Hamid Pirahesh - San Jose CA
Monica Sachiye Urata - Saratoga CA
Richard Sefton Sidle - Mountain View CA
David E. Simmen - San Jose CA
Calisto Paul Zuzarte - Pickering, CA

Assignee:

International Business Machines Corporation - Armonk NY

International Classification:

G06F 1730

US Classification:

707 2, 707 3, 707 4, 707 10, 707100, 707104

Abstract:

A method, apparatus, and article of manufacture for optimizing database queries using a materialized view for a table referenced in the query, wherein the materialized view has different properties than the referenced table. The materialized view may be replicated across multiple processors of the computer system, so that some or all of the query can be executed in a local fashion wherein there is no data movement required to perform the operations. The materialized view also may be partitioned across multiple processors of the computer system using a different partitioning key than the referenced table. The materialized view may be a vertical and/or horizontal subset of the table, so that only selected columns and/or tuples from the table are present therein. Columns may be added to the materialized view to contain pre-computed results of complex expressions, and indices may be created on the columns of the materialized view.

US Patent:

6339770, Jan 15, 2002

Filed:

Aug 12, 1999

Appl. No.:

09/373924

Inventors:

Ting Yu Leung - San Jose CA
Mir Hamid Pirahesh - San Jose CA

Assignee:

International Business Machines Corporation - Armonk NY

International Classification:

G06F 1730

US Classification:

707 2, 707 3, 707 5, 707 7, 707102

Abstract:

A method, apparatus, and article of manufacture for simplifying and optimizing queries involving GROUP BY operations, when GROUP BY items involve constants and when there are âfalseâ predicates in SELECT-HAVING clauses.

US Patent:

6438542, Aug 20, 2002

Filed:

Aug 30, 1999

Appl. No.:

09/385325

Inventors:

Fred Koo - Thornhill, CA
Ting Yu Leung - San Jose CA

Assignee:

International Business Machines Corporation - Armonk NY

International Classification:

G06F 1730

US Classification:

707 4, 707 2, 707 3, 707 5, 707100, 709201

Abstract:

A method, apparatus, and article of manufacture for optimizing a query being performed by a computer system to retrieve data from a database stored on the computer system. The query is analyzed to identify any joins therein that are lossless and to identify any tables of the identified joins that are eligible for removal. This analysis includes partitioning the joins into lossless and lossy joins, and partitioning the tables of the joins according to their associated quantifiers, wherein each of the quantifiers has a quantifier state indicating whether the table participates in a join that is lossless. The query is then rewritten to eliminate the identified tables that are eligible for removal.

US Patent:

6460027, Oct 1, 2002

Filed:

Sep 14, 1998

Appl. No.:

09/152559

Inventors:

Roberta Jo Cochrane - Los Gatos CA
George Lapis - San Jose CA
Ting Yu Leung - San Jose CA
Mir Hamid Pirahesh - San Jose CA
Monica Sachiye Urata - Saratoga CA

Assignee:

International Business Machines Corporation - Armonk NY

International Classification:

G06F 1730

US Classification:

707 2, 707 3, 707 4

Abstract:

A method, apparatus, and article of manufacture for optimizing database queries uses a derived summary table, wherein a definition of the summary table is based on a full select statement that is materialized in the table and describes how the summary table was derived. The summary table definition can be a complex query involving nested GROUP BY operations as well as complex HAVING clauses with subqueries or join operations. A query is analyzed using subsumption tests between the query and the definition of the summary table to determine whether expressions occurring anywhere in the query, but not in the summary table, can be subsumed in the summary table. In performing the query, an expression in the query is subsumed in the summary table when the expression can be re-derived from one or more of the columns of the summary table.

US Patent:

6496828, Dec 17, 2002

Filed:

Dec 17, 1999

Appl. No.:

09/466862

Inventors:

Roberta Jo Cochrane - Los Gatos CA
James C. Kleewein - San Jose CA
Ting Yu Leung - San Jose CA
Mir Hamid Pirahesh - San Jose CA
Narayana Subramanian - San Jose CA
Shivakumar Venkataraman - Sunnyvale CA

Assignee:

International Business Machines Corporation - Armonk NY

International Classification:

G06R 1730

US Classification:

707 10, 707 3

Abstract:

A system, method, and article of manufacture for supporting summary tables in a distributed database environment is disclosed. The system generally comprises a central program and a plurality of remote database systems that may be heterogeneous. The central program is configured to communicate with the database systems and to support summary tables (also referred to as materialized views) within the central program or within one or more of the database systems. The summary tables may contain summary data from one or more of the database systems. The central program may initiate the generation of summary tables, which may be populated local to the central program or local to one or more of the database systems. The central program may also maintain or coordinate maintenance of the summary tables. In addition, the central program may be configured to receive user queries on one or more of the database systems and to generate optimized query plans based upon the user queries, considering in so doing, the summary tables.

US Patent:

6513034, Jan 28, 2003

Filed:

Dec 21, 1999

Appl. No.:

09/469711

Inventors:

Ting Yu Leung - San Jose CA
David E. Simmen - San Jose CA

Assignee:

International Business Machines Corporation - Armonk NY

International Classification:

G06F 1730

US Classification:

707 3, 707 2

Abstract:

A method, apparatus, and article of manufacture for optimizing a query by deriving uniqueness for indices on the summary tables. The query is analyzed to determine whether a summary table can be used to answer the query or a summary table is directly referenced in the query. A determination is made whether a non-unique index of the summary table is unique based on a query definition of the summary table. If the non-unique index of the summary table is unique, then query optimization techniques can be applied.

US Patent:

6529896, Mar 4, 2003

Filed:

Feb 17, 2000

Appl. No.:

09/506471

Inventors:

Ting Yu Leung - San Jose CA
Tuong Chanh Truong - San Jose CA
Monica Sachiye Urata - Saratoga CA

Assignee:

International Business Machines Corporation - Armonk NY

International Classification:

G06F 1730

US Classification:

707 2

Abstract:

An optimization technique for SQL queries, a program storage device storing the optimization program, and an apparatus for optimizing a query is provided. A query is analyzed to determine whether it includes the WHERE clause which contains an âEXISTSâ subquery and a âNOT EXISTSâ subquery, or EXISTS-equivalent subqueries, in Boolean factor. If so, the subsumption test is performed on two subqueries. Then, the compensation predicate is applied to one of them, to perform the QGM transformation of the query. One subquery block is stacked on top of the other subquery block, in order to eliminate one subquery. This procedure allows the transformed query to perform more efficiently than the original query while providing same results. The query is then executed in the computer to efficiently retrieve data from the relational database.