CA2146171A1 - Method for Estimating Cardinalities for Query Processing in a Relational Database Management System - Google Patents
Method for Estimating Cardinalities for Query Processing in a Relational Database Management SystemInfo
- Publication number
- CA2146171A1 CA2146171A1 CA2146171A CA2146171A CA2146171A1 CA 2146171 A1 CA2146171 A1 CA 2146171A1 CA 2146171 A CA2146171 A CA 2146171A CA 2146171 A CA2146171 A CA 2146171A CA 2146171 A1 CA2146171 A1 CA 2146171A1
- Authority
- CA
- Canada
- Prior art keywords
- cardinalities
- columns
- grouping
- key
- management system
- 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
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/24545—Selectivity estimation or determination
-
- 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
-
- 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/953—Organization of data
- Y10S707/954—Relational
-
- 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
Abstract
A method for estimating key cardinalities for a grouping of columns for use by a query optimizer in a relational database management system (RDBMS). The grouping of columns or key results from a grouping operation or duplicate removal operation in a query. The method accounts for the effects of local predicates on the columns and also the effects of local predicates on columns not belonging to the grouping.
The method also utilizes selected index key cardinalities which are available in the catalog of the RDBMS for estimating the cardinalities of columns groupings and accounts for the effects of local predicates on the index key cardinalities. The method also includes a number of additional operations which utilize other attributes in order to produce an accurate estimate of the key cardinality.
The method also utilizes selected index key cardinalities which are available in the catalog of the RDBMS for estimating the cardinalities of columns groupings and accounts for the effects of local predicates on the index key cardinalities. The method also includes a number of additional operations which utilize other attributes in order to produce an accurate estimate of the key cardinality.
Priority Applications (2)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
CA002146171A CA2146171C (en) | 1995-04-03 | 1995-04-03 | Method for estimating cardinalities for query processing in a relational database management system |
US08/626,887 US5761653A (en) | 1995-04-03 | 1996-04-03 | Method for estimating cardinalities for query processing in a relational database management system |
Applications Claiming Priority (1)
Application Number | Priority Date | Filing Date | Title |
---|---|---|---|
CA002146171A CA2146171C (en) | 1995-04-03 | 1995-04-03 | Method for estimating cardinalities for query processing in a relational database management system |
Publications (2)
Publication Number | Publication Date |
---|---|
CA2146171A1 true CA2146171A1 (en) | 1996-10-04 |
CA2146171C CA2146171C (en) | 2000-01-11 |
Family
ID=4155553
Family Applications (1)
Application Number | Title | Priority Date | Filing Date |
---|---|---|---|
CA002146171A Expired - Fee Related CA2146171C (en) | 1995-04-03 | 1995-04-03 | Method for estimating cardinalities for query processing in a relational database management system |
Country Status (2)
Country | Link |
---|---|
US (1) | US5761653A (en) |
CA (1) | CA2146171C (en) |
Families Citing this family (66)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US6014656A (en) * | 1996-06-21 | 2000-01-11 | Oracle Corporation | Using overlapping partitions of data for query optimization |
US5903888A (en) * | 1997-02-28 | 1999-05-11 | Oracle Corporation | Method and apparatus for using incompatible types of indexes to process a single query |
US5924088A (en) * | 1997-02-28 | 1999-07-13 | Oracle Corporation | Index selection for an index access path |
US5995957A (en) * | 1997-02-28 | 1999-11-30 | International Business Machines Corporation | Query optimization through the use of multi-column statistics to avoid the problems of column correlation |
US6609100B2 (en) * | 1997-03-07 | 2003-08-19 | Lockhead Martin Corporation | Program planning management system |
US5963936A (en) * | 1997-06-30 | 1999-10-05 | International Business Machines Corporation | Query processing system that computes GROUPING SETS, ROLLUP, and CUBE with a reduced number of GROUP BYs in a query graph model |
US6081801A (en) * | 1997-06-30 | 2000-06-27 | International Business Machines Corporation | Shared nothing parallel execution of procedural constructs in SQL |
US5943666A (en) * | 1997-09-15 | 1999-08-24 | International Business Machines Corporation | Method and apparatus for optimizing queries across heterogeneous databases |
US6295608B1 (en) * | 1998-02-17 | 2001-09-25 | Microsoft Corporation | Optimized allocation of data elements among cache lines |
US6189069B1 (en) | 1998-02-17 | 2001-02-13 | Microsoft Corporation | Optimized logging of data elements to a data storage device |
US6513034B1 (en) | 1999-04-16 | 2003-01-28 | International Business Machines Corporation | Deriving uniqueness for indices on summary tables |
US6738755B1 (en) * | 1999-05-19 | 2004-05-18 | International Business Machines Corporation | Query optimization method for incrementally estimating the cardinality of a derived relation when statistically correlated predicates are applied |
US6363371B1 (en) * | 1999-06-29 | 2002-03-26 | Microsoft Corporation | Identifying essential statistics for query optimization for databases |
CA2279222C (en) * | 1999-07-30 | 2002-07-16 | Ibm Canada Limited-Ibm Canada Limitee | Direct call threaded code |
US6339770B1 (en) * | 1999-08-12 | 2002-01-15 | International Business Machines Corporation | Query simplification and optimization involving eliminating grouping column from group by operation corresponds to group by item that is constant |
US6704724B1 (en) * | 1999-12-14 | 2004-03-09 | Ncr Corporation | Parallel optimizer hints with a direct manipulation user interface |
US7890491B1 (en) * | 1999-12-22 | 2011-02-15 | International Business Machines Corporation | Query optimization technique for obtaining improved cardinality estimates using statistics on automatic summary tables |
US6636846B1 (en) * | 2000-04-28 | 2003-10-21 | International Business Machines Corporation | Method for providing a system maintained materialized functionally dependent column for a database management system |
CA2317081C (en) | 2000-08-28 | 2004-06-01 | Ibm Canada Limited-Ibm Canada Limitee | Estimation of column cardinality in a partitioned relational database |
US6895412B1 (en) | 2001-04-12 | 2005-05-17 | Ncr Corporation | Methods for dynamically configuring the cardinality of keyword attributes |
CA2359296A1 (en) * | 2001-10-18 | 2003-04-18 | Ibm Canada Limited-Ibm Canada Limitee | Method of cardinality estimation using statistical soft constraints |
US8140568B2 (en) * | 2001-12-13 | 2012-03-20 | International Business Machines Corporation | Estimation and use of access plan statistics |
CA2390350A1 (en) | 2002-06-10 | 2003-12-10 | Ibm Canada Limited-Ibm Canada Limitee | Incremental cardinality estimation for a set of data values |
US7136850B2 (en) * | 2002-12-20 | 2006-11-14 | International Business Machines Corporation | Self tuning database retrieval optimization using regression functions |
JP4327481B2 (en) * | 2003-03-17 | 2009-09-09 | 株式会社日立製作所 | Database system, server, inquiry input method and data update method |
JP2004280690A (en) * | 2003-03-18 | 2004-10-07 | Hitachi Ltd | Information processing system, and system setting method |
US7146363B2 (en) * | 2003-05-20 | 2006-12-05 | Microsoft Corporation | System and method for cardinality estimation based on query execution feedback |
US7356526B2 (en) * | 2003-09-30 | 2008-04-08 | International Business Machines Corporation | Estimating the compilation time of a query optimizer |
US7373354B2 (en) * | 2004-02-26 | 2008-05-13 | Sap Ag | Automatic elimination of functional dependencies between columns |
US7281004B2 (en) * | 2004-02-27 | 2007-10-09 | International Business Machines Corporation | Method, system and program for optimizing compression of a workload processed by a database management system |
US7343366B2 (en) * | 2004-03-30 | 2008-03-11 | International Business Machines Corporation | Group-By result size estimation |
US7337164B2 (en) * | 2004-03-31 | 2008-02-26 | Sap Ag | Fast search with very large result set |
US7353219B2 (en) | 2004-05-28 | 2008-04-01 | International Business Machines Corporation | Determining validity ranges of query plans based on suboptimality |
US7283990B2 (en) * | 2004-07-27 | 2007-10-16 | Xerox Corporation | Method and system for managing resources for multi-service jobs based on location |
US7483873B2 (en) | 2005-01-18 | 2009-01-27 | International Business Machines Corporation | Method, system and article of manufacture for improving execution efficiency of a database workload |
US20060253422A1 (en) * | 2005-05-06 | 2006-11-09 | Microsoft Corporation | Efficient computation of multiple group by queries |
US7562067B2 (en) * | 2005-05-06 | 2009-07-14 | Microsoft Corporation | Systems and methods for estimating functional relationships in a database |
US8027969B2 (en) * | 2005-12-29 | 2011-09-27 | Sap Ag | Efficient calculation of sets of distinct results in an information retrieval service |
US7882121B2 (en) * | 2006-01-27 | 2011-02-01 | Microsoft Corporation | Generating queries using cardinality constraints |
US8572067B2 (en) * | 2006-03-14 | 2013-10-29 | International Business Machines Corporation | Method to estimate the number of distinct value combinations for a set of attributes in a database system |
US7461050B2 (en) * | 2006-03-30 | 2008-12-02 | International Business Machines Corporation | Methods of cost estimation using partially applied predicates |
US7478083B2 (en) * | 2006-04-03 | 2009-01-13 | International Business Machines Corporation | Method and system for estimating cardinality in a database system |
US20080195577A1 (en) * | 2007-02-09 | 2008-08-14 | Wei Fan | Automatically and adaptively determining execution plans for queries with parameter markers |
US20080306903A1 (en) * | 2007-06-08 | 2008-12-11 | Microsoft Corporation | Cardinality estimation in database systems using sample views |
US8620888B2 (en) | 2007-12-06 | 2013-12-31 | Oracle International Corporation | Partitioning in virtual columns |
US8046352B2 (en) * | 2007-12-06 | 2011-10-25 | Oracle International Corporation | Expression replacement in virtual columns |
US8078652B2 (en) * | 2007-12-06 | 2011-12-13 | Oracle International Corporation | Virtual columns |
US8185519B2 (en) * | 2009-03-14 | 2012-05-22 | Microsoft Corporation | Techniques for exact cardinality query optimization |
US20110184934A1 (en) * | 2010-01-28 | 2011-07-28 | Choudur Lakshminarayan | Wavelet compression with bootstrap sampling |
US9171044B2 (en) * | 2010-02-16 | 2015-10-27 | Oracle International Corporation | Method and system for parallelizing database requests |
US9292569B2 (en) * | 2012-10-02 | 2016-03-22 | Oracle International Corporation | Semi-join acceleration |
US8849871B2 (en) | 2012-10-04 | 2014-09-30 | Oracle International Corporation | Efficient pushdown of joins in a heterogeneous database system involving a large-scale low-power cluster |
GB2508223A (en) | 2012-11-26 | 2014-05-28 | Ibm | Estimating the size of a joined table in a database |
GB2508603A (en) * | 2012-12-04 | 2014-06-11 | Ibm | Optimizing the order of execution of multiple join operations |
US9135280B2 (en) | 2013-02-11 | 2015-09-15 | Oracle International Corporation | Grouping interdependent fields |
US9471545B2 (en) | 2013-02-11 | 2016-10-18 | Oracle International Corporation | Approximating value densities |
US9110949B2 (en) * | 2013-02-11 | 2015-08-18 | Oracle International Corporation | Generating estimates for query optimization |
US10204140B2 (en) * | 2013-03-14 | 2019-02-12 | Oracle International Corporation | Massively parallel and in-memory execution of grouping and aggregation in a heterogeneous system |
EP2992447A4 (en) * | 2013-04-30 | 2016-09-21 | Hewlett Packard Entpr Dev Lp | Database table column annotation |
US9715527B2 (en) * | 2013-11-18 | 2017-07-25 | Sap Se | Join optimization in a database |
US9922088B2 (en) | 2013-12-31 | 2018-03-20 | Sybase, Inc. | Cardinality estimation using spanning trees |
US9846712B2 (en) * | 2015-04-25 | 2017-12-19 | International Business Machines Corporation | Index-only multi-index access |
US11388040B2 (en) | 2018-10-31 | 2022-07-12 | EXFO Solutions SAS | Automatic root cause diagnosis in networks |
US11645293B2 (en) | 2018-12-11 | 2023-05-09 | EXFO Solutions SAS | Anomaly detection in big data time series analysis |
US11522766B2 (en) | 2020-02-12 | 2022-12-06 | EXFO Solutions SAS | Method and system for determining root-cause diagnosis of events occurring during the operation of a communication network |
US20230394039A1 (en) * | 2022-06-01 | 2023-12-07 | Sap Se | Aggregation over high-cardinality dimensions using uniqueness indices |
Family Cites Families (9)
Publication number | Priority date | Publication date | Assignee | Title |
---|---|---|---|---|
US4769772A (en) * | 1985-02-28 | 1988-09-06 | Honeywell Bull, Inc. | Automated query optimization method using both global and parallel local optimizations for materialization access planning for distributed databases |
US5325525A (en) * | 1991-04-04 | 1994-06-28 | Hewlett-Packard Company | Method of automatically controlling the allocation of resources of a parallel processor computer system by calculating a minimum execution time of a task and scheduling subtasks against resources to execute the task in the minimum time |
US5345585A (en) * | 1991-12-02 | 1994-09-06 | International Business Machines Corporation | Method for optimizing processing of join queries by determining optimal processing order and assigning optimal join methods to each of the join operations |
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 |
US5544355A (en) * | 1993-06-14 | 1996-08-06 | Hewlett-Packard Company | Method and apparatus for query optimization in a relational database system having foreign functions |
DE19515020A1 (en) * | 1994-07-01 | 1996-01-04 | Hewlett Packard Co | Method and device for optimizing queries with group-by-operators |
US5615361A (en) * | 1995-02-07 | 1997-03-25 | International Business Machines Corporation | Exploitation of uniqueness properties using a 1-tuple condition for the optimization of SQL queries |
US5608904A (en) * | 1995-02-13 | 1997-03-04 | Hewlett-Packard Company | Method and apparatus for processing and optimizing queries having joins between structured data and text data |
-
1995
- 1995-04-03 CA CA002146171A patent/CA2146171C/en not_active Expired - Fee Related
-
1996
- 1996-04-03 US US08/626,887 patent/US5761653A/en not_active Expired - Fee Related
Also Published As
Publication number | Publication date |
---|---|
CA2146171C (en) | 2000-01-11 |
US5761653A (en) | 1998-06-02 |
Similar Documents
Publication | Publication Date | Title |
---|---|---|
CA2146171A1 (en) | Method for Estimating Cardinalities for Query Processing in a Relational Database Management System | |
WO1995021407A3 (en) | Improved method and apparatus for data access in multiprocessor digital data processing systems | |
CA2092629A1 (en) | Database searching system and method using a two dimensional marking matrix | |
DE69222169D1 (en) | METHOD AND DEVICE FOR RECOVERY OF DATA IN MULTI-STAGE DATABASE SYSTEMS | |
WO2002005116A3 (en) | Automatic database replication server and methods | |
WO2001009754A3 (en) | Modular method and system for performing database queries | |
EP1313037A3 (en) | X.500 System and methods including searching | |
WO1997038388A3 (en) | Method of integrating schemas of distributed heterogeneous databases | |
WO2001080095A3 (en) | Analytical server including metrics engine | |
WO2000011574A3 (en) | System and method for updating a credit information database | |
CA2232938A1 (en) | Method and apparatus for performing a join query in a database system | |
GB9411286D0 (en) | Query optimization in a relational database system | |
EP0772139A3 (en) | Method and apparatus for segmenting a database | |
CA2210097A1 (en) | Query translation system | |
GB2397406A (en) | Index structure of metadata, method for providing indices of metatdata, and metadata searching method and apparatus using the indices of metadata | |
AU2296302A (en) | Performing spreadsheet-like calculations in a database system | |
DE69033117T2 (en) | SYSTEM FOR STORING AND RETURNING ON RECORDS | |
WO2002093420A3 (en) | Method for managing distributed savepoints across multiple dbms's within a distributed transaction | |
EP1164509A3 (en) | Aggregate join index for relational databases | |
ATE246823T1 (en) | TRANSFORMATION OF PERSPECTIVE ON TABLES OF RELATIONAL DATABASES | |
IL139201A0 (en) | Method and system for past memory-resident processing of transaction data | |
CA2027779A1 (en) | Data retrieval system for relational database | |
EP1172740A3 (en) | SQL-based analytic algorithm for cluster analysis | |
ITMI951510A0 (en) | EMULATOR FOR A RELATIONAL DATABASE IN SQL LANGUAGE | |
EP0775963A3 (en) | Indexing a database by finite-state transducer |
Legal Events
Date | Code | Title | Description |
---|---|---|---|
EEER | Examination request | ||
MKLA | Lapsed |