EXTRAPOLATING FROM DATE STATISTICS
A database system extrapolates from date statistics maintained on a table in the system. In doing so, the system receives at a current date a date value in relation to which statistics are required, calculates a highest date for the date statistics, and compares the date value with the highest date. If the date value is greater than the highest date, then the system repeatedly reduces the date value by a fixed time period until the date value is less than or equal to the highest date. The system then calculates cardinality and number of unique values based on the reduced data value.
1 . A method for extrapolating from date statistics maintained on a table in a database system, the method comprising:
receiving at a current date a date value in relation to which statistics are required;
calculating a highest date for the date statistics;
comparing the date value with the highest date;
if the date value is greater than the highest date, then repeatedly reducing the date value by a fixed time period until the date value is less than or equal to the highest date; and
calculating cardinality, and number of unique values based on the reduced data value.
2 . The method of claim 1 , comprising the step of calculating the highest date as the max value of the last interval.
3 . The method of claim 1 , comprising the step of calculating the highest date as the max value of the penultimate interval if the max value of the last interval is greater than both the received date value and the current date.
4 . The method of claim 1 further comprising the step of calculating the highest date as the collection date if the max value of the last interval is greater than both the received date value and the current date, and the collection date is greater than the mode value of the last interval.
5 . The method of claim 4 wherein at least the last interval includes the max value of the interval, the method further comprising the step of reducing the max of the last interval to the collection date.
6 . The method of claim 4 wherein at least the last interval includes the number of unique values other than the mode value and the number of occurrences of the unique values in the rows of the table represented by the interval, the method further comprising the step of reducing the number of other unique values in the last interval and the total number of those occurrences by one.
7 . The method of claim 1 wherein the fixed time period is one week.
8 . The method of claim 1 wherein the fixed time period is 52 weeks.
9 . The method of claim 1 wherein the fixed time period is 3 months.
10 . The method of claim 1 wherein the fixed time period is 1 year.
11 . The method of claim 1 wherein the information generated includes the cardinality of data in the table specified by the date value.
12 . A method for extrapolating from date statistics maintained on a table in a database system, the method comprising:
receiving at a current date a date range in relation to which statistics are required, the date range including a start date representing the lower end of the date range and an end date representing the higher end of the date range;
calculating a highest date in the date statistics;
comparing the end date with the highest date;
if the end date is greater than the highest date, then repeatedly reducing the start date and the end date by a fixed time period until the end date is less than or equal to the highest date; and
calculating cardinality and number of unique values based on the reduced date range.
13 . The method of claim 12 , comprising the step of calculating the highest date as the max value of the last interval.
14 . The method of claim 12 , comprising the step of calculating the highest date as the max value of the penultimate interval if the max value of the last interval is greater than both the received date value and the current date.
15 . The method of claim 12 further comprising the step of calculating the highest date as the collection date if the max value of the last interval is greater than both the received date value and the current date, and the collection date is greater than the mode value of the last interval.
16 . The method of claim 15 wherein at least the last interval includes the max value of the interval, the method further comprising the step of reducing the max of the last interval to the collection date.
17 . The method of claim 15 wherein at least the last interval includes the number of unique values other than the mode value and the number of occurrences of the unique values in the rows of the table represented by the interval, the method further comprising the step of reducing the number of other unique values in the last interval and the total number of those occurrences by one.
18 . The method of claim 12 wherein the fixed time period is one week.
19 . The method of claim 12 wherein the fixed time period is 52 weeks.
20 . The method of claim 12 wherein the fixed time period is 3 months.
21 . The method of claim 12 wherein the fixed time period is 1 year.
22 . The method of claim 12 wherein the information generated includes the cardinality of data in the table specified by the date value.