CA2168287A1 - Method for Detecting and Optimizing Relational Queries with Encoding/Decoding Tables - Google Patents

Method for Detecting and Optimizing Relational Queries with Encoding/Decoding Tables

Info

Publication number
CA2168287A1
CA2168287A1 CA2168287A CA2168287A CA2168287A1 CA 2168287 A1 CA2168287 A1 CA 2168287A1 CA 2168287 A CA2168287 A CA 2168287A CA 2168287 A CA2168287 A CA 2168287A CA 2168287 A1 CA2168287 A1 CA 2168287A1
Authority
CA
Canada
Prior art keywords
join
hub table
plan
encoding
optimizer
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Granted
Application number
CA2168287A
Other languages
French (fr)
Other versions
CA2168287C (en
Inventor
Guy M. Lohman
K. Bernhard Schiefer
Monica S. Urata
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
IBM Canada Ltd
Original Assignee
Guy M. Lohman
K. Bernhard Schiefer
Monica S. Urata
Ibm Canada Limited-Ibm Canada Limitee
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Guy M. Lohman, K. Bernhard Schiefer, Monica S. Urata, Ibm Canada Limited-Ibm Canada Limitee filed Critical Guy M. Lohman
Publication of CA2168287A1 publication Critical patent/CA2168287A1/en
Application granted granted Critical
Publication of CA2168287C publication Critical patent/CA2168287C/en
Anticipated expiration legal-status Critical
Expired - Fee Related legal-status Critical Current

Links

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
    • G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
    • G06F16/24—Querying
    • G06F16/245—Query processing
    • G06F16/2453—Query optimisation
    • G06F16/24534—Query rewriting; Transformation
    • G06F16/24542—Plan optimisation
    • G06F16/24544—Join order optimisation
    • Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
    • Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10S707/00—Data processing: database and file management or data structures
    • Y10S707/99931—Database or file accessing
    • Y10S707/99932—Access augmentation or optimizing
    • Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
    • Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10S707/00—Data processing: database and file management or data structures
    • Y10S707/99931—Database or file accessing
    • Y10S707/99933—Query processing, i.e. searching
    • Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10—TECHNICAL SUBJECTS COVERED BY FORMER USPC
    • Y10S—TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y10S707/00—Data processing: database and file management or data structures
    • Y10S707/99931—Database or file accessing
    • Y10S707/99933—Query processing, i.e. searching
    • Y10S707/99934—Query formulation, input preparation, or translation

Abstract

A join optimizer and method for a relational database management system including a data processor, a stored database, and a plurality of database relations, wherein one or more of the relations are retrieved by the processor by means of query commands by performing a plurality of join operations on the relations, the system employing a general purpose heuristic algorithm which excludes or defers Cartesian products as late in the join sequence as possible, the method includes the steps of determining, in association with the execution of, or preferably prior to executing the general purpose algorithm, whether tables referenced in a query command includes a hub table and at least two encoding tables related to the hub table and, when the query command references a hub table and at least two encoding tables, determining the best access plan for the hub table, determining whether the best access plan utilizes an index used to access the hub table and, if so, constructing a plan to join the encoding tables as Cartesian products, construction a plan to join the hub table and the encoding tables and storing the plans in the data structures of the optimizer for enumeration with other access plans constructed by the optimizer.
CA002168287A 1995-03-31 1996-01-29 Method for detecting and optimizing relational queries with encoding/decoding tables Expired - Fee Related CA2168287C (en)

Applications Claiming Priority (2)

Application Number Priority Date Filing Date Title
US41483595A 1995-03-31 1995-03-31
US081/414,835 1995-03-31

Publications (2)

Publication Number Publication Date
CA2168287A1 true CA2168287A1 (en) 1996-10-01
CA2168287C CA2168287C (en) 2000-05-23

Family

ID=23643174

Family Applications (1)

Application Number Title Priority Date Filing Date
CA002168287A Expired - Fee Related CA2168287C (en) 1995-03-31 1996-01-29 Method for detecting and optimizing relational queries with encoding/decoding tables

Country Status (2)

Country Link
US (1) US5930785A (en)
CA (1) CA2168287C (en)

Cited By (1)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US6438542B1 (en) 1999-08-30 2002-08-20 International Business Machines Corporation Method of optimally determining lossless joins

Families Citing this family (61)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US6134540A (en) * 1997-05-09 2000-10-17 International Business Machines Corporation System, method, and program for applying query rewrite technology to object building
US6493700B2 (en) * 1997-10-14 2002-12-10 International Business Machines Corporation System and method for specifying custom qualifiers for explain tables
CA2249096C (en) * 1998-09-30 2001-12-04 Ibm Canada Limited-Ibm Canada Limitee Method for determining optimal database materializations using a query optimizer
US6370522B1 (en) * 1999-03-18 2002-04-09 Oracle Corporation Method and mechanism for extending native optimization in a database system
US6351742B1 (en) * 1999-03-18 2002-02-26 Oracle Corporation Method and mechanism for database statement optimization
US6401083B1 (en) * 1999-03-18 2002-06-04 Oracle Corporation Method and mechanism for associating properties with objects and instances
DK173451B1 (en) * 1999-04-16 2000-11-20 Targit As Method, apparatus and data carrier for processing queries to a database
US6421663B1 (en) * 1999-06-14 2002-07-16 International Business Machines Corporation Optimization of joined table expressions by extended access path selection
US6397204B1 (en) * 1999-06-25 2002-05-28 International Business Machines Corporation Method, system, and program for determining the join ordering of tables in a join query
US6374235B1 (en) * 1999-06-25 2002-04-16 International Business Machines Corporation Method, system, and program for a join operation on a multi-column table and satellite tables including duplicate values
US6446063B1 (en) * 1999-06-25 2002-09-03 International Business Machines Corporation Method, system, and program for performing a join operation on a multi column table and satellite tables
US6353821B1 (en) * 1999-12-23 2002-03-05 Bull Hn Information Systems Inc. Method and data processing system for detecting patterns in SQL to allow optimized use of multi-column indexes
US7191138B1 (en) 2000-04-15 2007-03-13 Mindloft Corporation System for cataloging, inventorying selecting, measuring, valuing and matching intellectual capital and skills with a skill requirement
US7747572B2 (en) * 2000-07-28 2010-06-29 Waypoint Global Ii, Inc. Method and system for supply chain product and process development collaboration
US6850925B2 (en) * 2001-05-15 2005-02-01 Microsoft Corporation Query optimization by sub-plan memoization
US6996567B2 (en) * 2001-05-31 2006-02-07 Heuristic Physics Laboratories, Inc. Automatic generation of join graphs for relational database queries
CA2363187A1 (en) * 2001-11-19 2003-05-19 Ibm Canada Limited-Ibm Canada Limitee Index sampled tablescan
US6968330B2 (en) * 2001-11-29 2005-11-22 International Business Machines Corporation Database query optimization apparatus and method
EP1349082A1 (en) * 2002-03-28 2003-10-01 LION Bioscience AG Method and apparatus for querying relational databases
JP3861044B2 (en) * 2002-10-24 2006-12-20 株式会社ターボデータラボラトリー Conversion method of chained join table to tree structure and conversion program
US7835953B2 (en) * 2003-09-29 2010-11-16 International Business Machines Corporation Method and structure for monitoring moving objects
US8972380B2 (en) * 2003-09-29 2015-03-03 International Business Machines Corporaton System and method for monitoring events against continual range queries
US7383246B2 (en) 2003-10-31 2008-06-03 International Business Machines Corporation System, method, and computer program product for progressive query processing
CN100383784C (en) * 2004-01-02 2008-04-23 联想(北京)有限公司 On-line analysing and treating system and method
US7406477B2 (en) * 2004-03-12 2008-07-29 Sybase, Inc. Database system with methodology for automated determination and selection of optimal indexes
US7873629B1 (en) * 2004-06-07 2011-01-18 Teradata Us, Inc. Dynamic partition enhanced inequality joining using a value-count index
US7319997B1 (en) * 2004-06-07 2008-01-15 Ncr Corp. Dynamic partition enhanced joining
US7640244B1 (en) 2004-06-07 2009-12-29 Teredata Us, Inc. Dynamic partition enhanced joining using a value-count index
US7529845B2 (en) * 2004-09-15 2009-05-05 Nokia Corporation Compressing, filtering, and transmitting of protocol messages via a protocol-aware intermediary node
US7536379B2 (en) * 2004-12-15 2009-05-19 International Business Machines Corporation Performing a multiple table join operating based on generated predicates from materialized results
US20060136380A1 (en) * 2004-12-17 2006-06-22 Purcell Terence P System and method for executing a multi-table query
US20060212429A1 (en) * 2005-03-17 2006-09-21 Microsoft Corporation Answering top-K selection queries in a relational engine
WO2006102227A2 (en) * 2005-03-19 2006-09-28 Activeprime, Inc. Systems and methods for manipulation of inexact semi-structured data
US7730079B2 (en) * 2005-08-30 2010-06-01 Microsoft Corporation Query comprehensions
JP3962417B2 (en) * 2005-08-31 2007-08-22 インターナショナル・ビジネス・マシーンズ・コーポレーション System and method for controlling access to database
US8117187B2 (en) * 2005-10-28 2012-02-14 Mediareif Moestl & Reif Kommunikations-Und Informationstechnologien Oeg Method for controlling a relational database system
US7769744B2 (en) * 2005-12-02 2010-08-03 Microsoft Cororation Missing index analysis and index useage statistics
US7185004B1 (en) 2005-12-09 2007-02-27 International Business Machines Corporation System and method for reverse routing materialized query tables in a database
US20070192215A1 (en) * 2006-02-10 2007-08-16 Taylor Thomas B Computer-implemented registration for providing inventory fulfillment services to merchants
US8607350B2 (en) * 2006-03-30 2013-12-10 International Business Machines Corporation Sovereign information sharing service
US7937390B2 (en) * 2006-06-01 2011-05-03 Mediareif Moestl & Reif Kommunikations-Und Informationstechnologien Oeg Method for controlling a relational database system
US7774337B2 (en) * 2006-07-11 2010-08-10 Mediareif Moestl & Reif Kommunikations-Und Informationstechnologien Oeg Method for controlling a relational database system
US8688682B2 (en) * 2007-03-23 2014-04-01 International Business Machines Corporation Query expression evaluation using sample based projected selectivity
US7853480B2 (en) * 2007-05-21 2010-12-14 Amazon Technologies, Inc. System and method for providing export services to merchants
US7917547B2 (en) * 2008-06-10 2011-03-29 Microsoft Corporation Virtualizing objects within queries
US8832112B2 (en) * 2008-06-17 2014-09-09 International Business Machines Corporation Encoded matrix index
US9709965B2 (en) * 2008-12-04 2017-07-18 Baselayer Technology, Llc Data center intelligent control and optimization
US8239406B2 (en) * 2008-12-31 2012-08-07 International Business Machines Corporation Expression tree data structure for representing a database query
US9317548B2 (en) 2013-01-30 2016-04-19 International Business Machines Corporation Reducing collisions within a hash table
US9311359B2 (en) 2013-01-30 2016-04-12 International Business Machines Corporation Join operation partitioning
US9367556B2 (en) 2013-06-14 2016-06-14 International Business Machines Corporation Hashing scheme using compact array tables
US9471710B2 (en) * 2013-06-14 2016-10-18 International Business Machines Corporation On-the-fly encoding method for efficient grouping and aggregation
US9672248B2 (en) 2014-10-08 2017-06-06 International Business Machines Corporation Embracing and exploiting data skew during a join or groupby
US10303791B2 (en) 2015-03-20 2019-05-28 International Business Machines Corporation Efficient join on dynamically compressed inner for improved fit into cache hierarchy
US10650011B2 (en) 2015-03-20 2020-05-12 International Business Machines Corporation Efficient performance of insert and point query operations in a column store
US9922064B2 (en) 2015-03-20 2018-03-20 International Business Machines Corporation Parallel build of non-partitioned join hash tables and non-enforced N:1 join hash tables
US10831736B2 (en) 2015-03-27 2020-11-10 International Business Machines Corporation Fast multi-tier indexing supporting dynamic update
US10108653B2 (en) 2015-03-27 2018-10-23 International Business Machines Corporation Concurrent reads and inserts into a data structure without latching or waiting by readers
US11169995B2 (en) * 2017-11-21 2021-11-09 Oracle International Corporation Relational dictionaries
US11061880B2 (en) 2018-01-25 2021-07-13 Hewlett-Packard Development Company, L.P. Data structure with identifiers
US10929402B1 (en) * 2018-08-10 2021-02-23 Amazon Technologies, Inc. Secure join protocol in encrypted databases

Family Cites Families (8)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
JPH022459A (en) * 1987-12-11 1990-01-08 Hewlett Packard Co <Hp> Inquiry processing
SE466029B (en) * 1989-03-06 1991-12-02 Ibm Svenska Ab DEVICE AND PROCEDURE FOR ANALYSIS OF NATURAL LANGUAGES IN A COMPUTER-BASED INFORMATION PROCESSING SYSTEM
US5197005A (en) * 1989-05-01 1993-03-23 Intelligent Business Systems Database retrieval system having a natural language interface
US5367675A (en) * 1991-12-13 1994-11-22 International Business Machines Corporation Computer automated system and method for optimizing the processing of a query in a relational database system by merging subqueries with the query
US5469568A (en) * 1993-01-07 1995-11-21 International Business Machines Corporation Method for choosing largest selectivities among eligible predicates of join equivalence classes for query optimization
US5560007A (en) * 1993-06-30 1996-09-24 Borland International, Inc. B-tree key-range bit map index optimization of database queries
US5519859A (en) * 1993-11-15 1996-05-21 Grace; John A. Method and apparatus for automatic table selection and generation of structured query language instructions
US5548755A (en) * 1995-02-17 1996-08-20 International Business Machines Corporation System for optimizing correlated SQL queries in a relational database using magic decorrelation

Cited By (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US6438542B1 (en) 1999-08-30 2002-08-20 International Business Machines Corporation Method of optimally determining lossless joins
US6601063B2 (en) 1999-08-30 2003-07-29 International Business Machines Corporation Method of optimally determining lossless joins

Also Published As

Publication number Publication date
CA2168287C (en) 2000-05-23
US5930785A (en) 1999-07-27

Similar Documents

Publication Publication Date Title
CA2168287A1 (en) Method for Detecting and Optimizing Relational Queries with Encoding/Decoding Tables
O'Neil et al. Multi-table joins through bitmapped join indices
US6581205B1 (en) Intelligent compilation of materialized view maintenance for query processing systems
CN106547796B (en) Database execution method and device
US5797000A (en) Method of performing a parallel relational database query in a multiprocessor environment
US6341281B1 (en) Database system with methods for optimizing performance of correlated subqueries by reusing invariant results of operator tree
Swami et al. On the estimation of join result sizes
US7962442B2 (en) Managing execution of a query against selected data partitions of a partitioned database
US7783625B2 (en) Using data in materialized query tables as a source for query optimization statistics
Stonebraker Triggers and inference in database systems
EP1164510A3 (en) Virtual join index for relational databases
US7702627B2 (en) Efficient interaction among cost-based transformations
JPH07219825A (en) Method for selection of coupling selectivity in enquiry optimizer and relational-database management system
JP2003150414A5 (en)
WO2002089013A3 (en) Method, system, program, and computer readable medium for indexing object oriented objects in an object oriented database
US20050021503A1 (en) Method and system for inclusion hash joins and exclusion hash joins in relational databases
EP1164509A3 (en) Aggregate join index for relational databases
EP1313034A3 (en) Storage system having means for acquiring execution information of a database management system
WO2004037994A3 (en) Managing biological databases
US20080147593A1 (en) Runtime resource sensitive and data driven optimization
US9177024B2 (en) System, method, and computer-readable medium for optimizing database queries which use spools during query execution
US7085754B2 (en) System and a two-pass algorithm for determining the optimum access path for multi-table SQL queries
US6253197B1 (en) System and method for hash loops join of data using outer join and early-out join
AU685657B2 (en) Method for performing joins in a database system
CA2253345A1 (en) Relational database compiled/stored on a memory structure

Legal Events

Date Code Title Description
EEER Examination request
MKLA Lapsed