Synopsis based advanced partition elimination
Disclosed herein are system, method, and computer program product embodiments for determining a constraint data statistics object is consistent. An embodiment operates by receiving a query for a database table. The embodiment determines a constraint data statistic object associated with a partition of the database table. The embodiment determines the constraint data statistics object is consistent with a data state of the partition of the database table based on consistency metadata of the constraint data statistics object. The embodiment processes the query for the database table based on the determining the constraint data statistics object is consistent with the data state of the partition of the database table.
1. A computer implemented method for determining constraint data statistics objects are consistent, comprising:
receiving, by at least one processor of a database management system (DBMS), a query referencing a single database object;
determining, by the at least one processor, a constraint data statistics object, wherein the constraint data statistics object stores and tracks a single data statistic of the single database object, the constraint data statistics object stores consistency metadata that specifies a most recent time point when the single database object was created or last updated using a first MaxRowlD value that specifies a largest row identifier of the single database object, and the DBMS is configured to automatically update the constraint data statistics object in response to a data change being made to the single database object;
determining, by the at least one processor, the single data statistic stored in the constraint data statistics object reflects a current data state of the single database object using the first MaxRowlD value in the consistency metadata;
deriving, by the at least one processor, a dynamic data integrity constraint for the single database object from the single data statistic stored in the constraint data statistics object, in response to the determining the single data statistic stored in the constraint data statistics object reflects the current data state of the single database object, wherein the dynamic data integrity constraint defines a condition for inclusion of data in the single database object;
processing, by the at least one processor, the query for the single database object by comparing a predicate of the query against the condition of the dynamic data integrity constraint for the single database object; and
outputting, by the at least one processor, a result of processing the query.
2. The method of claim 1 , wherein the current data state of the single database object comprises a second MaxRowlD value.
3. The method of claim 2 , further comprising:
incrementing the second MaxRowlD value based on insertion of a row into the single database object.
4. The method of claim 2 , wherein the determining the single data statistic stored in the constraint data statistics object reflects the current data state of the single database object further comprises:
determining the first MaxRowlD value of the consistency metadata is equal to the second MaxRowlD value for the current data state of the single database object.
5. The method of claim 1 , the processing the query further comprising:
performing at least one of dynamic partition pruning, query optimization, semi-join reduction optimization, or query transformation.
6. The method of claim 1 , the processing the query further comprising:
skipping a portion of the single database object based on the predicate of the query being disjoint with the condition of the dynamic data integrity constraint for the single database object.
7. A system, comprising:
a memory; and
at least one processor coupled to the memory and configured to:
receive a query referencing a single database object;
determine a constraint data statistics object, wherein the constraint data statistics object stores and tracks a single data statistic of the single database object, the constraint data statistics object stores consistency metadata that specifies a most recent time point when the single database object was created or last updated using a first MaxRowlD value that specifies a largest row identifier of the single database object, and the at least one processor is configured to automatically update the constraint data statistics object in response to a data change being made to the single database object;
determine the single data statistic stored in the constraint data statistics object reflects a current data state of the single database object using the first MaxRowlD value in the consistency metadata;
derive a dynamic data integrity constraint for the single database object from the single data statistic stored in the constraint data statistics object in response to the determining the single data statistic stored in the constraint data statistics object reflects the current data state of the single database object, wherein the dynamic data integrity constraint defines a condition for inclusion of data in the single database object;
process the query for the single database object by comparing a predicate of the query against the condition of the dynamic data integrity constraint for the single database object; and
output a result of processing the query.
8. The system of claim 7 , wherein the current data state of the single database object comprises a second MaxRowlD value.
9. The system of claim 8 , the at least one processor further configured to:
increment the second MaxRowlD value based on insertion of a row into the single database object.
10. The system of claim 8 , wherein to determine the single data statistic stored in the constraint data statistics object reflects the current data state of the single database object, the at least one processor is further configured to:
determine the first MaxRowlD value of the consistency metadata is equal to the second MaxRowlD value for the current data state of the single database object.
11. The system of claim 7 , wherein to process the query, the at least one processor is further configured to:
perform at least one of dynamic partition pruning, query optimization, semi-join reduction optimization, or query transformation.
12. The system of claim 7 , wherein to process the query, the at least one processor is further configured to:
skip a portion of the single database object based on the predicate of the query being disjoint with the condition of the dynamic data integrity constraint for the single database object.
13. A non-transitory computer readable medium having instructions stored thereon that, when executed by at least one computing device, cause the at least one computing device to perform operations comprising:
receiving a query referencing a single database object;
determining a constraint data statistics object, wherein the constraint data statistics object stores and tracks a single data statistic of the single database object, the constraint data statistics object stores consistency metadata that specifies a most recent time point when the single database object was created or last updated using a first MaxRowlD value that specifies a largest row identifier of the single database object, and the computing device is configured to automatically update the constraint data statistics object in response to a data change being made to the single database object;
determining the single data statistic stored in the constraint data statistics object reflects a current data state of the single database object using the first MaxRowlD value in the consistency metadata;
deriving, by the at least one processor, a dynamic data integrity constraint for the single database object from the single data statistic stored in the constraint data statistics object in response to the determining the single data statistic stored in the constraint data statistics object reflects the current data state of the single database object, wherein the dynamic data integrity constraint defines a condition for inclusion of data in the single database object;
processing the query for the single database object by comparing a predicate of the query against the condition of the dynamic data integrity constraint for the single database object. and
outputting a result of processing the query.
14. The non-transitory computer readable medium of claim 13 , wherein the current data state of the single database object comprises a second MaxRowlD value.
15. The non-transitory computer readable medium of claim 14 , the determining the single data statistic stored in the constraint data statistics object reflects the current data state of the single database object comprising:
determining the first MaxRowlD value of the consistency metadata is equal to the second MaxRowlD value for the current data state of the single database object.
16. The non-transitory computer readable medium of claim 13 , the processing the query comprising:
performing at least one of dynamic partition pruning, query optimization, semi-join reduction optimization, or query transformation.
17. The non-transitory computer readable medium of claim 13 , the processing the query further comprising:
skipping a portion of the single database object based on the predicate of the query being disjoint with the condition of the dynamic data integrity constraint for the single database object.