Determination of database statistics using application logic
View Patent ↗A database system may provide determination of statistics for a field of a database table. The determination may include determination of whether the field of the table is associated with a check table, and, if the field of the table is associated with a check table, determination of a number of distinct values of the field in the table based on a number of distinct values of the field in the check table.
1. A method comprising:
storing definitions of tables associated with an application in an application data dictionary;
determining, by a database management system in communication with the database data dictionary, that a field of one of the stored tables is associated with a field of a check table;
determining, by the database management system, a cardinality indicative of a logical relationship between the field of the table and the field of the check table is 1:0 . . . n, n=0,1,2 . . . , or 1:1 . . . n, n=1,2,3 . . . ;
reading, by the database management system, in an instance the determined cardinality is 1:0 . . . n, n =0,1,2 . . . , rows of the table to identify a number of distinct values of the field in the table until the first of: 1) each row of the table has been read; and 2) the identified number is equal to a number of distinct values of the field in the check table; and
determining, by the database management system, in an instance the determined cardinality is 1:1 . . . n, n=1,2,3 . . . , that the number of distinct values of the field in the table is equal to the number of distinct values of the field in the check table without reading a row of the table.
2. The method according to claim 1 , further comprising determining, by the database management system, whether statistics associated with the check table have been updated within a previous predetermined period; and in an instance the statistics associated with the check table have not been updated within the previous predetermined period, determining the number of distinct values of the field in the check table by reading one or more rows of the check table.
3. A method according to claim 1 , further comprising:
storing second definitions of the tables in a database data dictionary; and
storing tables associated with the second definitions a database.
4. A method according to claim 3 , wherein the determining that a field of one of the stored tables is associated with a field of a check table comprises determining that one of the second definitions associated with the stored table indicates that the field of the stored table is associated with a check table.
5. A method according to claim 3 , further comprising determining, by the database management system, whether the second definition indicates that the field of the stored table is not to be checked against the check table during an operation to modify data of the field.
6. A non-transitory computer-readable medium storing program instructions thereon, the medium comprising:
program instructions to store definitions of tables associated with an application in an application data dictionary;
program instructions to determine, by a database management system in communication with the database data dictionary, that a field of one of the stored tables is associated with a field of a check table;
program instructions to determine, by the database management system, a cardinality indicative of a logical relationship between the field of the table and the field of the check table is 1:0 . . . n, n=0,1,2 . . . , or 1:1 . . . n, n=1,2,3 . . . ;
program instructions to read, by the database management system, in an instance the determined cardinality is 1:0 . . . n, n =0,1,2 . . . , rows of the table to identify a number of distinct values of the field in the table until the first of: 1) each row of the table has been read; and 2) the identified number is equal to a number of distinct values of the field in the check table; and
program instructions to determine, by the database management system, in an instance the determined cardinality is 1:1 . . . n, n =1,2,3 . . . , that the number of distinct values of the field in the table is equal to the number of distinct values of the field in the check table without reading a row of the table.
7. The medium according to claim 6 , further comprising program instructions to determine, by the database management system, whether statistics associated with the check table have been updated within a previous predetermined period; and in an instance the statistics associated with the check table have not been updated within the previous predetermined period, program instructions to determine the number of distinct values of the field in the check table by reading one or more rows of the check table.
8. A medium according to claim 6 , further comprising:
program instructions to store second definitions of the tables in a database data dictionary; and
program instructions to store tables associated with the second definitions a database.
9. A medium according to claim 8 , wherein the program instructions to determine that a field of one of the stored tables is associated with a field of a check table comprises program instructions to determine that one of the second definitions associated with the stored table indicates that the field of the stored table is associated with a check table.
10. A method according to claim 8 , further comprising program instructions to determine, by the database management system, whether the second definition indicates that the field of the stored table is not to be checked against the check table during an operation to modify data of the field.