Set function calculation in a database
View Patent ↗A database system uses indexes to improve performance. The system can use one or more of the following: differentiated subfields for index clustering; set function calculations to reduce the amount of I/O; and/or using an inferred index in a circular table.
1. A computer implemented method of calculating a set function, including criteria, defined against fields in a table in a database, the method comprising:
creating an index list, using a processor of the computer, from at least one of the fields, the index list comprising nodes and having position counters in at least each node of the index list and further the index list having positional awareness whereby each of the nodes is enabled to identify its relative position within the index list;
traversing the index list, using the processor of the computer, and finding values of the index list consistent with the criteria;
utilizing the positional awareness by identifying a first positional counter corresponding to a first value of the index list, and a second positional counter value corresponding to a second value of the index list, and determining a number of records, associated with the first value based on the first and second positional counters, without reading the records;
calculating a set function from the positional awareness of the index list; and
using the calculation to provide a response to a query to the database.
2. The method of claim 1 , wherein some of the values of the index are used in calculating the set function.
3. The method of claim 1 , wherein the index list is a multi-field index.
4. The method of claim 3 , wherein the set function includes a COUNT function and wherein at least one of the fields in the multi-field index is a counted field.
5. The method of claim 3 , wherein the set function includes a calculation based on a numerical field in the table and wherein at least one of the fields in the multi-field index is the numerical field.
6. The method of claim 3 , wherein at least one of the fields in the multi-field index is one of the fields in the criteria.
7. The method of claim 3 , wherein the multi-field index comprises subfields of a field in the table.
8. The method of claim 7 , wherein the subfields are defined based on criteria in the set function.
9. The method of claim 6 , wherein the table includes a time field, and wherein the criteria includes a time criterion, and wherein at least one of the fields in the multi-field index is derived from the time field.
10. The method of claim 9 , wherein subfields of the time field, chosen based on the time criterion, are included in the multi-field index.
11. The method of claim 3 , wherein the multi-field index includes fields not relevant to the criteria.
12. The method of claim 11 , wherein in the multi-field index fields not relevant to the criteria precede fields relevant to the criteria.
13. The method of claim 1 , wherein the set function comprises a COUNT function where the index entries are read without reading the records in the table.
14. The method of claim 3 , wherein the set function comprises a COUNT function where the index entries are read without reading the records in the table.
15. A non-transitory computer readable medium comprising instructions thereon to cause one or more processors of a computer to perform a computer implemented method of calculating a set function, including criteria, defined against fields in a table in a database, the method comprising:
creating an index list, using a processor of the computer, from at least one of the fields, the index list comprising nodes and having position counters in at least each node of the index list and further the index list having positional awareness whereby each of the nodes is enabled to identify its relative position within the index list;
traversing the index list, using the processor of the computer, and finding values of the index list consistent with the criteria;
utilizing the positional awareness by identifying a first positional counter corresponding to a first value of the index list, and a second positional counter value corresponding to a second value of the index list, and determining a number of records, associated with the first value based on the first and second positional counters, without reading the records;
calculating a set function from the positional awareness of the index list; and
using the calculation to provide a response to a query to the database.
16. The non-transitory computer readable medium of claim 15 , wherein some of the values of the index are used in calculating the set function.
17. The non-transitory computer readable medium of claim 15 , wherein the index list is a multi-field index.
18. The non-transitory computer readable medium of claim 17 , wherein the set function includes a COUNT function and wherein at least one of the fields in the multi-field index is a counted field.
19. A computer system comprising:
a memory; and
one or more processors communicatively coupled to the memory wherein, the memory comprises instructions stored therein to cause the one or more processors to perform a computer implemented method of calculating a set function, including criteria, defined against fields in a table in a database, the method comprising:
creating an index list, using a processor of the computer, from at least one of the fields, the index list comprising nodes and having position counters in at least each node of the index list and further the index list having positional awareness whereby each of the nodes is enabled to identify its relative position within the index list;
traversing the index list, using the processor of the computer, and finding values of the index list consistent with the criteria;
utilizing the positional awareness by identifying a first positional counter corresponding to a first value of the index list, and a second positional counter value corresponding to a second value of the index list, and determining a number of records, associated with the first value based on the first and second positional counters, without reading the records;
calculating a set function from the positional awareness of the index list; and
using the calculation to provide a response to a query to the database.