System and method for efficiently indexing and storing a large database with high data insertion frequency

• US 5,204,958 A
• Filed: 06/27/1991
• Issued: 04/20/1993
• Est. Priority Date: 06/27/1991
• Status: Expired due to Term

• Current Assignee: Hewlett-Packard Development Company, L.P. (HP Inc.)
• Original Assignee: Digital Equipment Corporation (HP Inc.)
• Inventors: O'Neil, Patrick E., Gawlick, Dieter, Cheng, Edward C.
• Primary Examiner(s): Clark, David L.
• Assistant Examiner(s): AMSBURY, WAYNE P
• Application Number:
  US07/722,007
• Time in Patent Office: 663 Days
• Field of Search: 364/200 MS File, 395/600
• US Class Current: 1/1
• CPC Class Codes: G06F 16/9027 Trees
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A database index file is maintained by a computer system having primary random access memory and secondary memory. A record for each item added to the database is stored in a sequential file in secondary memory (disk storage) and an indexed pointer to the new record is stored in a small B-tree stored in primary random access memory. The full index file for the database is a second, large B-tree stored in secondary memory. Leaf-nodes of the full index file are stored in indexed order. Periodically, a portion of the memory resident small B-tree is merged with a corresponding portion of the large B-tree by selecting a range of index values and retrieving from secondary memory all indexed pointers in the selected range of index values. The indexed pointers in the first B-tree in the selected range of index values are merged into the retrieved records, the resulting merged set of indexed pointers are stored in secondary memory in indexed order in a contiguous area of secondary memory. As a result, the indexed pointers for newly added database records are written to secondary memory in batches, thereby accessing secondary memory very efficiently.

Claims

1. A method of storing and maintaining a database of entries in a computer system having primary random access memory and secondary memory, the steps of the method performed by said computer system comprising:

upon request, storing new records in a database file;

storing indexed pointers to said new records in a first tree data structure which is stored in primary memory;

storing a second tree data structure in secondary memory, said second tree data structure containing indexed pointers to all records in said database file other than records for which indexed pointers are stored in said first tree data structure;

wherein said indexed pointers in said second tree data structure are stored in sequential order in said secondary memory;

periodically merging a portion of said first tree data structure into said second tree data structure by (1A) selecting a portion of the indexed pointers in said first tree data structure, (1B) retrieving from said secondary memory a corresponding portion of said indexed pointers in said second tree data structure, (1C) merging said selected portion of the indexed pointers in said first tree data structure into said indexed pointers retrieved from said second tree data structure to produce sequentially ordered merged indexed pointers, (1D) storing the merged index pointers in said secondary memory so as to maintain the stored index pointers in sequential order, and (1E) removing from said first tree data structure those indexed pointers which were merged with indexed pointers from said second tree data structure.

2. The method of claim 1, said periodically merging step including

(1F) periodically changing said selected portion of the indexed pointers in said first tree data structure prior to performing steps (1A) through (1E) so that all indexed pointers stored in said first tree data structure are merged in said second tree data structure.

3. The method of claim 1, further including:

responding to each request to delete a specified record in said database file having a specified index value by (2A) searching said first tree data structure for an indexed pointer to said specified record, (2B) if said indexed pointer is found in said first tree data structure, deleting said specified record from said data file and deleting said indexed pointer from said first tree data structure, and (2C) otherwise storing in primary memory a delete note entry noting said specified index value;

said periodically merging step including;

(1G) while performing step (1C), deleting each retrieved indexed pointer in said second tree data structure having an index value which matches the index value specified by one of said delete note entries, and deleting the record in said database file corresponding to said deleted indexed pointer.

4. The method of claim 3,

said step (2C) including storing in said first tree data structure, in accordance with said specified index value, a datum comprising said delete note entry;

and

said step (1G) including, when merging indexed pointers from said first tree data structure into said retrieved indexed pointers, deleting each retrieved indexed pointer having an index value which matches the index value specified by a delete note entry in said first tree data structure, and deleting the record in said database file corresponding to said deleted indexed pointer.

5. A method of storing and maintaining a database of entries in a computer system having primary random access memory and secondary memory,the steps of the method performed by said computer system comprising:

upon request, storing new records in a database file;

storing indexed pointers to said new records in a first tree data structure which is stored in primary memory;

storing a second tree data structure in secondary memory, said second tree data structure containing indexed pointers to all records in said database file other than records for which indexed pointers are stored in said first tree data structure;

wherein said indexed pointers in said second tree data structure are stored in indexed order in said secondary memory;

periodically merging a portion of said first tree data structure into said second tree data structure by (1A) selecting a range of index values, (1B) retrieving from said secondary memory all indexed pointers in said second tree data structure in said selected range of index values, (1C) merging into said retrieved indexed pointers those indexed pointers in said first tree data structure matching said selected range of index values to produce merged indexed pointers in indexed order, (1D) storing said merged index pointers in said secondary memory so as to maintain the stored index pointers in indexed order, and (1E) removing from said first tree data structure those indexed pointers which were merged with indexed pointers from said second tree data structure.

6. The method of claim 5, said periodically merging step including

(1F) periodically changing said selected range of index values prior to performing steps (1A) through (1E) so that all indexed pointers stored in said first tree data structure are merged into said second tree data structure.

7. The method of claim 5, further including:

responding to each request to delete a specified record in said database file having a specified index value by (2A) searching said first tree data structure for an indexed pointer to said specified record, (2B) if said indexed pointer is found in said first tree data structure, deleting said specified record from said data file and deleting said indexed pointer from said first tree data structure, and (2C) otherwise storing in primary memory a delete note entry noting said specified index value;

said periodically merging step including;

(1G) while performing step (1C), deleting each retrieved indexed pointer in said second tree data structure having an index value which matches the index value specified by one of said delete note entries, and deleting the record in said database file corresponding to said deleted indexed pointer.

8. The method of claim 7,

said step (2C) including storing in said first tree data structure, in accordance with said specified index value, a datum comprising said delete note entry;

and

said step (1G) including, when merging indexed pointers from said first tree data structure into said retrieved indexed pointers, deleting each retrieved indexed pointer having an index value which matches the index value specified by a delete note entry in said first tree data structure, and deleting the record in said database file corresponding to said deleted indexed pointer.

9. A method of storing and maintaining a database of entries in a computer system having primary random access memory and secondary memory, the steps of the method performed by said computer system comprising:

storing a set of records in a database file in said secondary memory, each record in said file including a set of fields, wherein a specified set of said fields is designated to be said record'"'"'s index value;

storing in primary random access memory a first tree data structure representing indexed pointers to a first subset of said records in said file;

said first tree data structure including a multiplicity of leaf-nodes, each containing a multiplicity of indexed pointers to records in said file;

storing in secondary memory a second tree data structure representing indexed pointers a second subset of said records, said second subset of said records comprising those records in said file not included in said first subset of said records;

said second tree data structure containing a multiplicity of leaf-nodes, said leaf-nodes in said second tree data structure containing indexed pointers with non-overlapping sets of index values, wherein said leaf-nodes are stored in said secondary memory in indexed order;

responding to requests to store new records by storing each new record in said file and storing an indexed pointer to said new record in said first tree data structure in primary random access memory in accordance with said record'"'"'s index value; and

periodically merging a portion of said first tree data structure into said second tree data structure by (1A) selecting a range of index values, (1B) retrieving from said second tree data structure in said secondary memory all indexed pointers in said selected range of index values, (1C) merging into said retrieved indexed pointers those indexed pointers in said first tree data structure matching said selected range of index values to produce merged indexed pointers in indexed order, (1D) storing said merged index pointers in said secondary memory in leaf-nodes in indexed order so as to maintain the index pointers stored in said secondary memory in indexed order, and (1E) removing from said first tree data structure those indexed pointers which were merged with indexed pointers from said second tree data structure.

10. The method of claim 9, said periodically merging step including

(1F) periodically changing said selected range of index values prior to performing steps (1A) through (1E) so that all indexed pointers stored in said first tree data structure are merged into said second tree data structure.

11. The method of claim 9, further including:

responding to each request to delete a specified record in said database file having a specified index value by (2A) searching said first tree data structure for an indexed pointer to said specified record, (2B) if said indexed pointer is found in said first tree data structure, deleting said specified record from said data file and deleting said indexed pointer from said first tree data structure, and (2C) otherwise storing in primary memory a delete note entry noting said specified index value;

said periodically merging step including;

(1G) while performing step (1C), deleting each retrieved indexed pointer in said second tree data structure having an index value which matches the index value specified by one of said delete note entries, and deleting the record in said database file corresponding to said deleted indexed pointer.

12. The method in claim 11,

said step (2C) including storing in said first tree data structure, in accordance with said specified index value, a datum comprising said delete note entry;

and

said step (1G) including, when merging indexed pointers from said first tree data structure into said retrieved indexed pointers, deleting each retrieved indexed pointer having an index value which matches the index value specified by a delete note entry in said first tree data structure, and deleting the record in said database file corresponding to said deleted indexed pointer.

13. A database management system, having primary random access memory, secondary memory, and a central processing unit coupled to said primary memory and second secondary memory;

the database management system comprising;

a database file, containing numerous records, which is at least partially stored in secondary memory;

an index for said records in said database file, said index comprising;

a first tree data structure which is stored in primary memory, said first tree data structure containing indexed pointers to a subset of the records in said database file; and

a second tree data structure which is stored in secondary memory, said second tree data structure containing indexed pointers to all records in said database file other than records for which indexed pointers are stored in said first tree data structure;

wherein said indexed pointers in said second tree data structure are stored in sequential order in secondary memory; and

a database management program which is executed by said CPU;

said database management program including;

new record storing means for storing new records in said database file and storing indexed pointers to said new records in said first tree data structure; and

tree merge means, for periodically merging a portion of said first tree data structure into said second tree data structure by (1A) selecting a range of index values, (1B) retrieving from said secondary memory a corresponding portion of said indexed pointers in said second tree data structure, (1C) merging said selected portion of the indexed pointers in said first tree data structure into said indexed pointers retrieved from said second tree data structure to produce sequentially ordered merged indexed pointers, (1D) storing said merged index pointers in said secondary memory so as to maintain the stored index pointers in sequential order, and (1E) removing from said first tree data structure those indexed pointers which were merged with indexed pointers from said second tree data structure.

14. The database management system of claim 13, said tree merge means including means for periodically changing said selected portion of the indexed pointers in said first tree data structure prior to merging indexed pointers from said first tree data structure into said second tree data structure, so that all indexed pointers stored in said first tree data structure are merged into said second tree data structure.

15. The database management system of claim 13, said database management program further including:

delete means, responsive to requests to delete a specified record in said database file having a specified index value, which (2A) searches said first tree data structure for an indexed pointer to said specified record, (2B) if said indexed pointer is found in said first tree data structure, deletes said specified record from said data file and deletes said indexed pointer from said first tree data structure, and (2C) otherwise, stores in primary memory a delete note entry noting said specified index value;

said tree merge means including delete filter means which deletes each retrieved indexed pointer in said second tree data structure having an index value which matches the index value specified by one of said delete note entries, and which deletes the record in said database file corresponding to said deleted indexed pointer.

16. The database management system of claim 15,

said delete means including means for storing in said first tree data structure, in accordance with said specified index value, a datum comprising said delete note entry;

and

said delete filter means including means for deleting each retrieved indexed pointer having an index value which matches the index value specified by a delete note entry in said first tree data structure, and for deleting the record in said database file corresponding to said deleted indexed pointer.

17. A database management system, having primary random access memory, secondary memory, and a central processing unit coupled to said primary memory and second secondary memory;

the database management system comprising;

a database file, containing numerous records, which is at least partially stored in secondary memory;

an index for said records in said database file, said index comprising;

a first tree data structure which is stored in primary memory, said first tree data structure containing indexed pointers to a subset of the records in said database file; and

a second tree data structure which is stored in secondary memory, said second tree data structure containing indexed pointers to all records in said database file other than records for which indexed pointers are stored in said first tree data structure;

wherein said indexed pointers in said second tree data structure are stored in indexed order in secondary memory; and

a database management program which is executed by said CPU;

said database management program including;

new record storing means for storing new records in said database file and storing indexed pointers to said new records in said first tree data structure; and

tree merge means, for periodically merging a portion of said first tree data structure into said second tree data structure by (1A) selecting a range of index values, (1B) retrieving from secondary memory all indexed pointers in said second tree data structure in said selected range of index values, (1C) merging into said retrieved indexed pointers those indexed pointers in said first tree data structure matching said selected range of index values to produce merged indexed pointers in indexed order, (1D) storing said merged index pointers in said secondary memory in indexed order so as to maintain the index pointers stored in said secondary memory in indexed order, and (1E) removing from said first tree data structure those indexed pointers which were merged with indexed pointers from said second tree data structure.

18. The database management system of claim 17, said tree merge means including means for periodically changing said selected range of index values prior to merging indexed pointers from said first tree data structure into said second tree data structure, so that all indexed pointers stored in said first tree data structure are merged into said second tree data structure.

19. The database management system of claim 17, said database management program further including:

delete means, responsive to requests to delete a specified record in said database file having a specified index value, which (2A) searches said first tree data structure for an indexed pointer to said specified record, (2B) if said indexed pointer is found in said first data structure, deletes said specified record from said data file and deletes said indexed pointer from said first tree data structure, and (2C) otherwise, stores in primary memory a delete note entry noting said specified index value;

said tree merge means including delete filter means which deletes each retrieved indexed pointer in said second tree data structure having an index value which matches the index value specified by one of said delete note entries, and which deletes the record in said database file corresponding to said deleted indexed pointer.

20. The database management system of claim 19,

said delete means including means for storing in said first tree data structure, in accordance with said specified value, a datum comprising said delete note entry;

and

said delete filter means including means for deleting each retrieved indexed pointer having an index value which matches the index value specified by a delete note entry in said first tree data structure, and for deleting the record in said database file corresponding to said deleted indexed pointer.

21. A method of storing and maintaining a database of entries in a computer system having primary random access memory and second memory, the steps of the method performed by said computer system comprising:

upon request, storing new records in a database file;

storing indexed pointers to said new records in a first data structure which is stored in primary memory;

storing a second data structure in secondary memory, said second data structure containing indexed pointers to all records in said database file other than records for which indexed pointers are stored in said first data structure;

wherein said indexed pointers in said second data structure are stored in sequential order in said secondary memory;

periodically merging a portion of said first data structure into said second data structure by (1A) selecting a portion of the indexed pointers in said first data structure, (1B) retrieving from said secondary memory a corresponding portion of said indexed pointers in said second data structure, (1C) merging said selected portion of the indexed pointers in said first data structure into said indexed pointers retrieved from said second data structure to produce sequentially ordered merged indexed pointers. (1D) storing the merged index pointers in said secondary memory so as to maintain the stored index pointers in sequential order, and (1E) removing from said first data structure those indexed pointers which were merged with indexed pointers from said second data structure.

22. A database management system, having primary random access memory, secondary memory, and a central processing unit coupled to said primary memory and second secondary memory;

the database management system comprising;

a database file, containing numerous records, which is at least partially stored in secondary memory;

an index for said records in said database file, said index comprising;

a first data structure which is stored in primary memory, said first data structure containing indexed pointers to a subset of the records in said database file; and

a second data structure which is stored in secondary memory, said second data structure containing indexed pointers to all records in said database file other than records for which indexed pointers are stored in said first data structure;

wherein said indexed pointers in said second data structure are stored in sequential order in secondary memory; and

a database management program which is executed by said CPU;

said database management program including;

new record storing means for storing new records in said database file and storing indexed pointers to said new records in said first data structure; and

merge means, for periodically merging a portion of said first data structure into said second data structure by (1A) selecting a range of index values, (1B) retrieving from said secondary memory a corresponding portion of said indexed pointers in said second data structure, (1C) merging said selected portion of the indexed pointers in said first data structure into said indexed pointers retrieved from said second data structure to produce sequentially ordered merged indexed pointers, (1D) storing said merged index pointers in said secondary memory so as to maintain the stored index pointers in sequential order, and (1E) removing from said first data structure those indexed pointers which were merged with indexed pointers from said second data structure.