Rewrite of queries containing rank or rownumber or min/max aggregate functions using a materialized view
IPC분류정보
국가/구분
United States(US) Patent
등록
국제특허분류(IPC7판)
G06F-017/30
출원번호
US-0005436
(2011-01-12)
등록번호
US-8103689
(2012-01-24)
발명자
/ 주소
Gupta, Abhinav
Witkowski, Andrew
출원인 / 주소
Oracle International Corporation
대리인 / 주소
Hickman Palermo Truong & Becker LLP
인용정보
피인용 횟수 :
4인용 특허 :
79
초록▼
Techniques are provided for improving efficiency of database systems, and in particular, to refreshing materialized views maintained by database systems and rewriting queries to access the materialized views. According to the approaches, a ranked materialized view is incrementally refreshed, and dur
Techniques are provided for improving efficiency of database systems, and in particular, to refreshing materialized views maintained by database systems and rewriting queries to access the materialized views. According to the approaches, a ranked materialized view is incrementally refreshed, and during the incremental refresh operation, rows in the partitions of the materialized view are ranked within the partitions.
대표청구항▼
1. A method of rewriting queries, the method comprising: a database computer system performing the steps of:receiving a query that requests ranked results, each result produced by an aggregate function applied to a group of a plurality of groups of particular rows from: a base table, ora join of bas
1. A method of rewriting queries, the method comprising: a database computer system performing the steps of:receiving a query that requests ranked results, each result produced by an aggregate function applied to a group of a plurality of groups of particular rows from: a base table, ora join of base tables;determining whether said received query satisfies one or more criteria for rewriting; andif said received query satisfies said one or more criteria, then rewriting said query to produce a rewritten query that references a materialized view; andwherein said materialized view ranks aggregate function results of said aggregate function applied to groups of said particular rows, said aggregate function results including an aggregate function result for each group of said groups of particular rows. 2. The method of claim 1, wherein said aggregate function is either the SUM function, MAX function, MIN function, COUNT function, or AVG function. 3. The method of claim 1, wherein said materialized view is a nested materialized view reflecting rankings of rows from a summary table that summarizes data based on: said aggregate function, andsaid base table or said join of base tables. 4. A non-transitory computer-readable volatile or non-volatile medium storing one or more sequences of instructions which, when executed by one or more processors, causes the one or more processors to perform the steps of: receiving a query that requests ranked results, each result produced by an aggregate function applied to a group of a plurality of groups of particular rows from: a base table, ora join of base tables;determining whether said received query satisfies one or more criteria for rewriting; andif said received query satisfies said one or more criteria, then rewriting said query to produce a rewritten query that references a materialized view; andwherein said materialized view ranks aggregate function results of said aggregate function applied to groups of said particular rows, said aggregate function results including an aggregate function result for each group of said groups of particular rows. 5. A non-transitory computer-readable volatile or non-volatile medium as recited in claim 4, wherein said aggregate function is either the SUM function, MAX function, MIN function, COUNT function, or AVG function. 6. A non-transitory computer-readable volatile or non-volatile medium as recited in claim 4, wherein said materialized view is a nested materialized view reflecting rankings of rows from a summary table that summarizes data based on: said aggregate function, andsaid base table or said join of base tables.
연구과제 타임라인
LOADING...
LOADING...
LOADING...
LOADING...
LOADING...
이 특허에 인용된 특허 (79)
Blakeley Jose Alfredo ; Thompson Craig W., Apparatus and method for adding an associative query capability to a programming language.
Levy Alon Y. (New Providence NJ) Srivastava Divesh (New Providence NJ), Apparatus and methods for retrieving information by modifying query plan based on description of information sources.
Burnett Colin J. (Sydney AUX) Haas Garry L. (San Jose CA), Computer program product for object specification, generation, and management in a distributed database.
Griffin Timothy G. ; Hull Richard Baxter ; Kumar Bharat ; Lieuwen Daniel Francis ; Zhou Gang, Data architecture for fetch-intensive database applications.
Farber David A. ; Lachman Ronald D., Data processing system using substantially unique identifiers to identify data items, whereby identical data items hav.
Boyer Philip L. ; Carey Michael James ; Kiernan Gerald G., Database method and apparatus for interactively retrieving data members and related members from a collection of data.
Pirahesh Mir Hamid ; Simmen David E. ; Truong Tuong Chanh, Database system and method for supporting current of cursor updates and deletes from a select query from one or more upd.
Pelletier, Jean Pierre; Pelletier, Johane; de Guise, M. Jacques; Raynauld, Jean-Pierre; Barthiaume, Marie-Josee; Beaudoin, Gilles; Godbout, M. Benoit; Kauffmann, M. Claude, Evaluating disease progression using magnetic resonance imaging.
Cochrane, Roberta Jo; Lehner, Wolfgang; Pirahesh, Mir Hamid; Sidle, Richard Sefton, Incremental maintenance of summary tables with complex grouping expressions.
Gupta,Abhinav; Witkowski,Andrew, Incremental refresh of materialized views containing rank function, and rewrite of queries containing rank or rownumber or min/max aggregate functions using such a materialized view.
Lawande, Shilpa; Gupta, Abhinav; Dageville, Benoit, Incremental refresh of materialized views with joins and aggregates after arbitrary DML operations to multiple tables.
Cochrane Roberta Jo ; Pirahesh Mir Hamid ; Rielau Serge Philippe,DEX ; Sidle Richard Sefton,CAX ; Urhan Tolga, Intelligent compilation of procedural functions for query processing systems.
Katin Neil (Los Gatos CA) Leventer Ruthellen (Potomac MD) Priyadarshan Eswar (Reading MA) Ruberg Alan (Arlington MA) Shaio Sami (Palo Alto CA), Method and apparatus for deriving object type and obtaining object type attribute values.
Bentson Sheridan (c/o DataView Partners ; 5699 Kanan Rd. ; Suite 253 Agoura CA 91301) Rassman William R. (c/o DataView Partners ; 5699 Kanan Rd. ; Suite 253 Agoura CA 91301), Method and apparatus for displaying and updating structured information.
Norcott William D. ; Ziauddin Mohamed, Method and apparatus for efficiently refreshing sets of summary tables and materialized views in a database management system.
Jacobs Lawrence ; Adunuthula Seshu ; Anand Mala, Method and apparatus for performing transactions in a stateless web environment which supports a declarative paradigm.
Bhargava Gautam (Cupertino CA) Goel Piyush (Monte Sereno CA) Iyer Balakrishna Ragmavendra (San Jose CA), Method and apparatus for reordering complex SQL queries using a modified generalized outer join operator.
Dar Shaul,ILX ; Jagadish Hosagrahar Visvesvaraya ; Levy Alon Yitzchak ; Srivastava Divesh, Method and system for using materialized views to evaluate queries involving aggregation.
Anand Thulusalamatom Krishnamurthi ; Tucker Peter A. ; Bennett John G. ; Bath Kamaljit S. ; Krishnaswamy Raja, Method of creating a tabular data stream for sending rows of data between client and server.
Hoover Michael K. (Roswell GA) Miller Barrick H. (Marietta GA) Schurenberg Kurt (Roswell GA) Daigle Richard A. (Atlanta GA), Methods and systems for object-based relational distributed databases.
Knudsen Helge (Oakville CAX) Chong Daniel T. (Woodbridge CAX) Yaffe John (Mississauga CAX) Taugher James E. (Mississauga CAX) Robertson Michael (Mississauga CAX) Plazak Zbigniew (Etobicoke CAX), Operating system and data base having an access structure formed by a plurality of tables.
Roberta Jo Cochrane ; George Lapis ; Ting Yu Leung ; Mir Hamid Pirahesh ; Monica Sachiye Urata ; Richard Sefton Sidle ; David E. Simmen ; Calisto Paul Zuzarte CA, Query optimization by transparently altering properties of relational tables using materialized views.
Lieuwen Daniel Francis ; Kawaguchi Akira ; Colby Latha Sankar ; Ross Kenneth A., Refreshing materialized views of a database to maintain consistency with underlying data.
Raitto John ; Ziauddin Mohamed ; Finnerty James, Rewriting a query in terms of a summary based on aggregate computability and canonical format, and when a dimension tabl.
Randall Bello ; James Finnerty ; Mohamed Ziauddin ; Andrew Witkowski, Rewriting a query in terms of a summary based on one-to-one and one-to-many losslessness of joins.
Roberta Jo Cochrane ; James C. Kleewein ; Ting Yu Leung ; Mir Hamid Pirahesh ; Narayana Subramanian ; Shivakumar Venkataraman, Support for summary tables in a heterogeneous database environment.
Latha S. Colby ; Richard L. Cole ; Edward P. Haslam ; Nasi Jazayeri ; Galt Johnson ; William J. McKenna ; David G. Wilhite, Jr., System and method for determining and generating candidate views for a database.
Castelli Vittorio ; Jhingran Anant Deep ; Li Chung-Sheng ; Robinson John Timothy, System and method for generating multi-representations of a data cube.
Bhargava Gautam (Cupertino CA) Goel Piyush (Monte Sereno CA) Iyer Balakrishna R. (San Jose CA), System and method for generating uniqueness information for optimizing an SQL query.
Colby Latha S. ; Cole Richard L. ; Haslam Edward P. ; Jazayeri Nasi ; Johnson Galt ; McKenna William J. ; Schumacher Lee E. ; Wilhite ; Jr. David G., System and method for rewriting relational database queries.
Colby, Latha S.; Cole, Richard L.; Haslam, Edward P.; Jazayeri, Nasi; Johnson, Galt; McKenna, William; Schumacher, Lee E.; Wilhite, Jr., David G., System and method for rewriting relational database queries.
Leung Ting Y. (San Jose CA) Pirahesh Mir H. (San Jose CA) Seshadri Praveen (Madison WI), System for optimizing correlated SQL queries in a relational database using magic decorrelation.
※ AI-Helper는 부적절한 답변을 할 수 있습니다.