Database query optimization for count distinct operations
AssigneeZoom Communications, Inc.
InventorsJiyue Ma, Wei Zhang
Life of this patent
Estimating the term…
Abstract
A query that includes a plurality of COUNT(DISTINCT) operations applied to a same dimensional column with conditional filtering is received. The query is transformed to obtain a transformed query. Transforming the query includes rewriting the plurality of COUNT(DISTINCT) operations into corresponding aggregation operations using a first combination of SUM( ) and COUNT( ) or a second combination using SUM( ) and MAX( ); and generating the transformed query incorporating the aggregation operations. The transformed query is then transmitted to a database system for execution to generate a query result.
Claims20
A method, comprising:
- receiving, during runtime execution of an executing application, a query including a plurality of COUNT(DISTINCT) operations applied to a same dimensional column with conditional filtering;
- transforming the query to obtain a transformed query by:
- retrieving statistical properties of columns in a database system, the statistical properties including one or more of value distributions, cardinality estimates, null value frequencies, or minimum and maximum values of the columns;
- rewriting the plurality of COUNT(DISTINCT) operations into aggregation operations using a first combination of SUM( ) and COUNT( ) or a second combination using SUM( ) and MAX( ) based on the statistical properties;
- and
- generating the transformed query incorporating the aggregation operations;
- transmitting the transformed query to the database system for execution to generate a query result;
- and
- transmitting the query result to the executing application.
A system, comprising:
- a memory subsystem;
- and
- processing circuitry, the processing circuitry configured to execute instructions stored in the memory subsystem to:
- receive, during runtime execution of an executing application, a query including a plurality of COUNT(DISTINCT) operations applied to a same dimensional column with conditional filtering;
- transform the query to obtain a transformed query by instructions to:
- retrieve statistical properties of columns in a database system, the statistical properties including one or more of value distributions, cardinality estimates, null value frequencies, or minimum and maximum values of the columns;
- rewrite the plurality of COUNT(DISTINCT) operations into aggregation operations using a first combination of SUM( ) and COUNT( ) or a second combination using SUM( ) and MAX( ) based on the statistical properties;
- and
- generate the transformed query incorporating the aggregation operations;
- transmit the transformed query to the database system for execution to generate a query result;
- and
- transmit the query result to the executing application.
One or more non-transitory computer readable media storing instructions operable to cause one or more processors to perform operations comprising:
- receiving, during runtime execution of an executing application, a query including a plurality of COUNT(DISTINCT) operations applied to a same dimensional column with conditional filtering;
- transforming the query to obtain a transformed query by:
- retrieving statistical properties of columns in a database system, the statistical properties including one or more of value distributions, cardinality estimates, null value frequencies, or minimum and maximum values of the columns;
- rewriting the plurality of COUNT(DISTINCT) operations into aggregation operations using a first combination of SUM( ) and COUNT( ) or a second combination using SUM( ) and MAX( ) based on the statistical properties;
- and
- generating the transformed query incorporating the aggregation operations;
- transmitting the transformed query to the database system for execution to generate a query result;
- and
- transmitting the query result to the executing application.
Dependencies are inferred from the claim text.