Clustered index with differentiated subfields
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 method for creating an index for a database table, the database table having at least a first field and a second field, the method comprising the steps of:
dividing, by a processor, the first field into a first segment and a second segment, wherein the first segment comprises a most significant part of the first field and the second segment comprises a least significant part of the first field, wherein the most significant part of the first field is selected to allow a range of data associated with the segment comprising the most significant part to be entirely contained in memory; and
arranging the first and second segments in an index for the database table, the index arranged in a sequence beginning with the first segment, followed by the second field, followed by the second segment,
wherein neither the first segment nor the second segment are fields in the database table.
2. The method of claim 1 , wherein the dividing step is performed in response to a query that employs the first field.
3. The method of claim 1 , wherein the memory is cache memory.
4. The method of claim 1 , further comprising:
arranging the first and second segments in the index in a sequence beginning with the first segment, followed by the second field, followed by a next field, followed by the second segment.
5. The method of claim 1 , wherein the dividing step further comprises:
dividing the first field into more than two segments.
6. The method of claim 1 , wherein the first field comprises a timestamp, wherein the first segment comprises a date, and wherein the second segment comprises a time.
7. A system comprising:
a first memory for storing a database table having at least first field and a second field:
a second memory for storing an index for the database table, the index comprising a sequential arrangement of a first segment of the first field, followed by the second field, followed by a second segment of the first field; and
wherein, the first segment comprises a most significant part of the first field, and the second segment comprises a least significant part of the first field and wherein neither the first segment nor the second segment are fields in the database table,
wherein the most significant part of the first field is selected to allow a range of data associated with the segment comprising the most significant part to be entirely contained in the second memory.
8. The system of claim 7 , wherein the first memory and the second memory reside on the same computer.
9. The system of claim 7 , wherein the database table represents data having an insertion order based upon time.
10. The system of claim 7 , wherein the database table represents data comprising a plurality of logged events.
11. The system of claim 7 , wherein the first memory and the second memory reside on the same physical memory device.
12. The system of claim 7 , wherein the index comprises a sequential arrangement of a first segment of the first field, followed by the second field, followed by a next field, followed by a second segment of the first field.
13. The system of claim 7 , wherein the first field is divided into more than two segments.
14. A non-transitory computer program product embodied on a non-transitory computer readable medium comprising instructions to cause one or more processors to:
store a database table having at least first field and a second field;
store an index for the database table, the index comprising at least sequential arrangement of a first segment of the first field, followed by the second field, followed by a second segment of the first field; and
wherein, the first segment comprises a most significant part of the first field, and the second segment comprises a least significant part of the first field and wherein neither the first segment nor the second segment are fields in the database table,
wherein the most significant part of the first field is selected to allow a range of data associated with the segment comprising the most significant part to be entirely contained in memory.
15. The computer program product of claim 14 , wherein the database table represents data having an insertion order based upon time.
16. The computer program product of claim 14 , wherein the database table represents data comprising a plurality of logged events.
17. The computer program product of claim 14 , wherein the index comprises a sequential arrangement of a first segment of the first field, followed by the second field, followed by a next field, followed by a second segment of the first field.
18. The computer program product of claim 14 , wherein the first field is divided into more than two segments, and wherein the segments are not fields in the database table.
19. The computer program product of claim 14 , wherein the first field comprises a timestamp, wherein the first segment comprises a date, and wherein the second segment comprises a time.