公开(公告)号：CA2427202A1

公开(公告)日：2004-10-30

申请号：CA2427202

申请日：2003-04-30

Applicant: IBM CANADA

Inventor： MA WENBIN ,  ZUZARTE CALISTO P ,  CHENG QI

IPC: G06F7/00 ,  G06F17/30

Abstract: A method for use with an information retrieval system, in which the method i s ued for transforming a database statement containing a first subquery and a seco nd subquery joined together, the first subquery including a group by operation over a child subquery, the method comprising removing the first subquery from the database statemen t thereby removing the join between the first subquery and the second subquery.

公开(公告)号：CA2418163A1

公开(公告)日：2004-07-31

申请号：CA2418163

申请日：2003-01-31

Applicant: IBM CANADA

Inventor： LIU LINQI ,  MA WENBIN ,  CHENG QI ,  PIRAHESH HAMID MIR ,  ZUZARTE CALISTO P

IPC: G06F17/30

Abstract: A method of query transformation using window aggregation to transform queri es involving subqueries. An optimizer in a relational database management system transfor ms queries to optimize their efficiency and speed. The method transforms queries that have a subquery, replacing the subquery with a window aggregation function. In the case of a correlated subquery, the window aggregation function is partitioned by a correlated column of a correlated table. All data in the main select, or outer block, of the query that was obtained through references to the correlated table is instead obtained through the new window aggregation subquery. By using windo w aggregation, the aggregation is performed at the same time as the selection of relevant data from the correlated table, thereby compiling all needed data in a single pass through the table or view . Reducing the number of times that tables or views are accessed reduces the computational demands of a query.