Method and apparatus for building one or more indexes on data concurrent with manipulation of data

[0001] This application is related to the application by Sockut et al. entitled “Interaction Between Application of a Log and Maintenance of a Table that Maps Records Identifiers During Online Reorganization of a Database”, now U.S. Pat. No. 6,026,412, which is hereby incorporated by reference in its entirety, including any appendices and references thereto. This application is related to the application by Blank et al. entitled “High Performance Recover/Build Index System by Unloading Database Files in Parallel”, now U.S. Pat. No. 5,842,208, which is hereby incorporated by reference in its entirety, including any appendices and references thereto.

[0002] 1. Field of Invention

[0003] The present invention relates generally to the field of indexing data. More specifically, the present invention is related to indexing data without suspending data manipulation.

[0004] 2. Discussion of Prior Art

[0005] Current state-of-the-art-schemes for building a new index on data require that all insert, update, or delete operations (on the data) be suspended for the duration of the index build operation. The emphasis in such prior art schemes is on raw speed to reduce the amount of time the data is not available for manipulation.

[0006] FIG. 1 illustrates such a prior art scheme, wherein the scheme involves: (a) scanning a data set; (b) extracting an index key from each data record, (c) pairing the index key with a record identifier (also called a RID), (d) sorting the data by key and RID; and (e) building the index structure from these ordered key/RID pairs. In such prior art schemes, the amount of time in which the data is not available for manipulation may be considerable.

[0007] The references describe, in general, the procedure of indexing data. It should be noted that the prior art fails to teach, either directly or indirectly, the present invention's method for building indexes on data concurrent with the manipulation of data.

[0008] The U.S. patent to Ambroziac (U.S. Pat. No. 6,460,047 B1) provides for a technique for indexing data. The disclosed method describes compressing an index to obtain an index that is easily stored and transmitted. The disclosed invention also provides for the decompression of such a compressed index. One disclosed embodiment maintains a separate index for each document, thereby allowing for easy updating of indexes in response to changes in documents and easy transmission of indexes, which allows distributed searching. The claimed technique provides very compact indexing information, but allows the indexing information to be very rapidly processed.

[0009] The U.S. patent to Kirsch et al. (U.S. Pat. No. 5,920,854) provides for a real-time document collection search engine with phrase indexing. The disclosed collection search system is responsive to a user query against a collection of documents to provide a search report. The collection search system includes a collection index, including: (a) a first predetermined single word and multiple word phrases as indexed terms occurring in the collection of documents, (b) a linguistic parser that identifies a list of search terms from a user query, the linguistic parser identifying the list from second predetermined single words and multiple word phrases, and (c) a search engine coupled to receive the list from the linguistic parser.

[0010] The U.S. patent to Agarwal et al. (U.S. Pat. No. 5,842,196) provides for a database system and method for updating records such as are commonly used in a relational database environment. Updates are carried out in a manner which allows a substantial portion of the work to be performed in direct mode (when possible), thereby avoiding the inefficiency of re-reading records. In this fashion, a scenario which requires deferred updating, in accordance with the present invention, can be treated mostly as a direct update with minimal deferred updating.

[0011] The U.S. patent to Reinsch et al. (U.S. Pat. No. 4,868,744) provides for a method for restarting a long-running, fault-tolerant operation in a transaction-oriented database system without burdening-the system log. A restartable load without logging method permits the restart of a LOAD operation from the last COMMIT point without requiring the writing of images of loaded records to the log. Instead, the method logs only a minimal amount of information, recording positions within the data sets to be loaded and within the tablespace being loaded.

[0012] The European patent to Fuller (EP0767435 A1) provides for a transaction device driver technique for a journaling file system. The transaction device driver logs any updates as the data appears through normal read/write/strategy entry points into the driver and, should the system fail while there are outstanding operations, the driver ensures that either all or none of the changes for the operation will appear in the file system.

[0013] Whatever the precise merits, features, and advantages of the above-cited references, none of them achieves or fulfills the purposes of the present invention.

[0014] The present invention describes a method and apparatus for building a new index on data concurrent with insert, update, or delete operations where the amount of time in which the data is not available for manipulation may be approach-insignificant. According to the present invention's method, the data is scanned similar to a classic index build operation, but without restricting the manipulation of the data resulting in an index which is inconsistent with the current data content. The index is then brought to consistency with the data by processing the recovery log records for the data to simulate the index manipulations which normally occur during data insert, update, or delete operations.

[0022] While this invention is illustrated and described in a preferred embodiment, the invention may be produced in many different configurations, forms, and materials. There is depicted in the drawings, and will herein be described in detail, a preferred embodiment of the invention, with the understanding that the present disclosure is to be considered as an exemplification of the principles of the invention and the associated functional specifications for its construction and is not intended to limit the invention to the embodiment illustrated. Those skilled in the art will envision many other possible variations within the scope of the present invention.

[0023] FIG. 2 illustrates a flow chart 200 outlining a general overview of the exemplary embodiment of the present invention's method. The method comprises: (a) performing an index build operation without restricting the manipulation of data resulting in an index which is inconsistent with current data content 202; and (b) bringing the index to consistency with the data by processing log records for data to simulate the index manipulations which normally occur during data insert, update, or delete operations 204.

[0024] In the preferred embodiment, the log is recorded as described in Sockut et al. (U.S. Pat. No. 6,026,412) (which is incorporated by reference). FIG. 3 illustrates the structure of file pages 310 and 312, which include a header indicating the position of the log. Each file page shown generally at 310 and 312 contains zero or more data records (shown generally as 316, 318, and 330), which DBMS allocates at the beginning of each of pages 310 and 312. There are three types of data records: regular (316), pointer (330), and overflow (318). The end of file pages 310 and 312 contains an ID map, shown generally at 320, which is an array of pointers (offsets of data records within the file page). “Slot” refers to the space (if any) to which an ID maps entry points. Specifically, in file page 310, an entry (shown as “*”) in pointer 324 points to a slot for regular data record 316 and is shown generally as arrow 340; an entry in pointer 322 points to a slot for pointer data record 330 and is shown generally as arrow 338. In file page 312, an entry in pointer 344 points to a slot for regular data record 316 and is shown generally as arrow 328; an entry in pointer 342 points to a slot for overflow data record 318 and is shown generally as arrow 326. The header of each page includes the position in the log, i.e., the relative byte address (RBA 314) that was current when file page 310 or 312 was most recently written. Whenever users insert (I), update (U) or delete (D), DBMS records the activity in the log and thus increases the current RBA.

[0025] FIG. 4 illustrates a first embodiment of the present invention's method. At the beginning of the index build operation, the current position of the recovery log is recorded and the data is scanned with regard to consistency (sometimes called a “fuzzy” read) to extract the index keys. It should be noted that the step of recording the position of the recovery log is envisioned to occur in close proximity to the step of scanning data. Index keys are paired with an identifier (e.g., record identifier or RID). The data is then sorted by key and RID. As a next step, the index is built (e.g., with parallelism as described in Blank, et al. in U.S. Pat. No. 5,842,208) from the sorted data. Next, the recovery log is read from the recorded point to the current point, extracting key, RID, and Log Record Sequence Number (LRSN) from the redo log records for simulating index inserts, and from the undo log records for simulating index deletes.

[0026] FIG. 5 illustrates another embodiment of the present invention's method. This embodiment applies to systems that optimize the information logged for updates and record only the changed portions of the data record posing a problem if the entire key value is not present in the log records. For these systems, the log must record full before and after data record images. The method of this embodiment tracks the method of FIG. 4, except an image (pre-build image) of the recovery log is recorded prior to scanning data and an image (post-build image) of the recovery log is recorded after the index building operation. The images are then compared to simulate inserts and deletes (thereby bringing the data to consistency).

[0027] FIG. 6 illustrates an alternative embodiment wherein the system extracts the before and after key values and writes them to a pseudo log as if the index were being maintained automatically. The index key/RID/LRSN/operation sets are then sorted by key and LRSN, and then the key/RID insert or delete operations are applied to the index as needed. When all the log records are processed, then the operation is iterated from the last point in the recovery log to the current point until the system estimates that the next set of records can be processed within a user-specified time interval (δt). At this point, the insert, update, and delete operations on the data is halted until the last set is processed; then, the insert, update, and delete operations are resumed with automatic maintenance of the indexes enabled.

[0028] One key advantage is that log records are scanned for data changes after the fact. From the data changes, it is inferred that the changes that need to be made to update the index are built separately from the engine's ongoing index maintenance. The algorithm can run independently of the engine and is the approach which would be used by tool vendors. The present invention's solution avoids run-time memory logging.

[0029] FIG. 7 illustrates an apparatus 700 associated with the exemplary embodiment of the present invention, wherein apparatus 700 provides for building one or more indexes on data concurrent with the manipulation of data. The apparatus 700 comprises index builder 702 and index manipulations simulator 704. Index builder 702 performs an index build operation without restricting the manipulation of data and index manipulations simulator 704 brings the index to consistency with the data based upon processing recovery log records to simulate index manipulations during data operations.

[0030] Index builder 702 further comprises: data scanner 706 for scanning data; index key extractor 708 for extracting index keys from the scanned data; paring element 710 for paring the index keys with a record identifier; sorter 712 for sorting the index keys and record identifiers; and index structure builder 714 for building an indexed structure from the sorted index keys and record identifiers.

[0031] Index manipulations simulator 704 provides for: (a) simulating index manipulations during data insert, update, or delete operations 716; and (b) bringing index to consistency 718 using any of the three embodiments described in FIGS. 4-6.

[0032] Additionally, the present invention provides for an article of manufacture comprising computer readable program code contained within, implementing one or more modules for building one or more indexes on data concurrent with the manipulation of data. Furthermore, the present invention includes a computer program code-based product, which is a storage medium having program code stored therein which can be used to instruct a computer to perform any of the methods associated with the present invention. The computer storage medium includes any of, but is not limited to, the following: CD-ROM, DVD, magnetic tape, optical disc, hard drive, floppy disk, ferroelectric memory, flash memory, ferromagnetic memory, optical storage, charge coupled devices, magnetic or optical cards, smart cards, EEPROM, EPROM, RAM, ROM, DRAM, SRAM, SDRAM, or any other appropriate static or dynamic memory or data storage devices.

[0033] Implemented in computer program code-based products are software modules for: (a) performing an index build operation without restricting the manipulation of data; and (b) bringing the index to consistency with said data based upon processing recovery log records to simulate index manipulations during data insert, update, or delete operations.

[0034] A method and apparatus has been shown in the above embodiments for the effective building of one or more indexes on data concurrent with the manipulation of data. While various preferred embodiments have been shown and described, it will be understood that there is no intent to limit the invention by such disclosure but, rather, it is intended to cover all modifications and alternate constructions falling within the spirit and scope of the invention, as defined in the appended claims. For example, the present invention should not be limited by a software/program, or computing environment.

[0035] The above enhancements are implemented in various computing environments. For example, the present invention may be implemented on a conventional IBM PC or equivalent, multi-nodal system (e.g., LAN) or networking system (e.g., Internet, WWW, wireless web). All programming and data related thereto are stored in computer memory, static or dynamic, and may be retrieved by the user in any of: conventional computer storage, display (i.e., CRT) and/or hardcopy (i.e., printed) formats. The programming of the present invention may be implemented by one of skill in the art of database programming.