Immutable bookmarks for large objects in a database

• US 7,630,957 B2
• Filed: 06/27/2006
• Issued: 12/08/2009
• Est. Priority Date: 06/27/2006
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Krishnaprasad, Muralidhar, Arora, Geeta
• Primary Examiner(s): Rones; Charles
• Assistant Examiner(s): Quader; Fazlul
• Application Number:
  US11/426,697
• Publication Number:
  US 20070299816A1
• Time in Patent Office: 1,260 Days
• Field of Search: 707/2
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2219 Large Object storage; Management thereof
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing

Abstract

User-level bookmarks are initially created at a specified offset in a large object (LOB) of a database and automatically updated to reflect a new offset, if necessary, after database updates. By logically linking the bookmark to a data element, the bookmark continues to point to the same logical data element as it did before the update. An API provides the functionality of immutable bookmarks by automatically changing the offset of the bookmark following sliding inserts or deletions of data to the database.

Claims

1. In a database that utilizes bookmarks to access user selected data stored in a database, a method for maintaining the association between the bookmarks and the user selected data comprising:

associating a bookmark with an offset of the database;

updating the offset associated with the bookmark to match an offset associated with the user selected data, wherein updating the offset associated with the bookmark comprises associating the offset with the bookmark in accordance with (p−

x+y), where p denotes an initial offset, x denotes the number of deleted bytes, and y denotes the number of inserted bytes between a start of the user selected data and the initial offset associated with the bookmark;

reorganizing the database such that the user selected data is stored at the updated offset; and

associating the bookmark with the updated offset.

2. The method of claim 1 further comprising automatically maintaining association between the bookmark and the updated offset whereby the bookmark is immutable when viewed.

3. The method of claim 1 wherein X is 0 .

4. The method of claim 1 wherein updating the offset associated with the bookmark comprises updating an entry in a bookmark dictionary when reorganizing the database.

5. The method of claim 4 wherein updating the entry in the bookmark dictionary comprises updating a page in a LOB.

6. The method of claim 4 wherein updating the entry in the bookmark dictionary comprises updating a table not visible to a user.

7. The method of claim 1 wherein updating the offset associated with the bookmark comprises updating a hash table.

8. The method of claim 1 wherein Y is at least 1.

9. The method of claim 1 wherein user selected data stored in the database comprises a LOB.

10. The method of claim 9 further comprising updating the offset associated with the bookmark to match a binary offset associated with the user selected data in the LOB.

11. The method of claim 1 wherein user selected data stored in the database comprises a BLOB.

12. The method of claim 11 further comprising updating the offset associated with the bookmark to match a binary offset associated with the user selected data in the BLOB.

13. The method of claim 1 wherein user selected data stored in the database comprises a CLOB.

14. The method of claim 13 further comprising updating the offset associated with the bookmark to match the character offset associated with the user selected data.

15. The method of claim 1 wherein user selected data stored in the database comprises a large varchar.

16. The method of claim 1 further comprising defining a relative bookmark having an offset relative to the bookmark associated with the user selected data.

17. The method of claim 1 further comprising removing the bookmark when a range of data that includes the user selected data is deleted from the database.

18. In a relational database having a plurality of large datatypes and a plurality of bookmarks, each of which point to user selected data stored at a specific offset within the database, a system for updating at least one of the bookmarks in response to a database reorganization, the system comprising:

means for storing the database;

means, coupled to the storing means and operating on a server, for managing the database; and

an application program interface, coupled to the managing means, adapted to create a bookmark logically linked to user selected data stored in one of the plurality of large datatypes and to associate an offset of the database with the bookmark,

wherein the application program interface further comprises means for changing the offset associated with the bookmark during database update in accordance with (p−

x +y), where p denotes an initial offset. x denotes the number of deleted bytes, and y denotes the number of inserted bytes between a start of the user selected data and the initial offset associated with the bookmark.

19. The system of claim 18 wherein the application program interface further comprises means for automatically maintaining association between the bookmark and the undated offset whereby the bookmark is immutable when viewed.

20. The system of claim 18 wherein the application program interface further comprises means for calculating a new offset associated with the bookmark to maintain a logical linkage with user selected data after reorganization of the database.

21. The system of claim 20 wherein X is 0.

22. The system of claim 20 wherein Y is at least 1.

23. The system of claim 18 wherein the user selected data stored in the database comprises a LOB.

24. The system of claim 18 wherein the application program interface further comprises a hash table for changing the offset associated with the bookmark during database update.

25. The system of claim 18 wherein the application program interface further comprises a bookmark dictionary for maintaining the offset associated with the bookmark to maintain the logical linkage with the user selected data following update of the database.

26. The system of claim 18 wherein the application program interface further comprises a plurality of bookmark dictionaries for maintaining the offset associated with a plurality of bookmarks to maintain the logical linkage between each of the plurality of bookmarks with the user selected data during database update, each of said plurality of bookmark dictionaries associated with one of a plurality of LOBs.

27. A method for adding bookmarks to a relational database having a plurality of large objects (LOBs), the method comprising:

defining a bookmark by associating the bookmark at an initial offset in a LOB;

in response to a database reorganization, calculating an updated offset of the database for the bookmark; and

updating the initial offset associated with the bookmark to reflect the updated offset, wherein updating the initial offset associated with the bookmark comprises associating the offset with the bookmark in accordance with (p −

x +y), where p denotes the initial offset, x denotes the number of deleted bytes, and y denotes the number of inserted bytes between a start of the LOB and the initial offset associated with the bookmark.

28. The method of claim 27 further comprising logically linking the bookmark with user specified data.

29. The method of claim 28 wherein the user specified data comprises an Extensible Markup Language (XML) document.

30. The method of claim 27 wherein the bookmark is immutable.

31. A machine-readable medium including instructions executable by one or more processors, the machine-readable medium comprising:

one or more instructions for defining a bookmark by associating the bookmark at an initial offset in a database;

one or more instructions for calculating an updated offset for the bookmark; and

one or more instructions for updating the initial offset associated with the bookmark to reflect the updated offset, wherein updating the initial offset associated with the bookmark comprises associating the initial offset with the bookmark in accordance with (p −

x +y), where p denotes the initial offset. x denotes the number of deleted bytes, and y denotes the number of inserted bytes between a start of a LOB and the initial offset associated with the bookmark.

32. An information storage medium having a plurality of instructions adapted to direct an information processing device to perform a set of steps including:

defining an immutable bookmark by associating the bookmark with data stored at a location in a database;

updating the database such that the data is stored at an updated location in the database; and

associating the bookmark with the updated location,

wherein associating the bookmark with the undated location comprises associating an initial offset with the bookmark in accordance with (p −

x +y), where p denotes the initial offset, x denotes the number of deleted bytes, and y denotes the number of inserted bytes between a start of the data stored and the initial offset associated with the bookmark.

33. The information storage medium of claim 32, wherein the bookmark association comprises associating the bookmark with an offset in a large object (LOB).

34. The information storage medium of claim 32, wherein the bookmark association comprises associating the bookmark with an offset in a character large object (CLOB).

35. The information storage medium of claim 32 having a plurality of instructions further adapted to updating the offset associated with the bookmark, said adapting comprising updating an entry in a bookmark dictionary.

36. A method comprising:

permitting access to a first data stored in a database by using a first bookmark, where the first bookmark is linked to the first data through a first offset value relative to a reference position in the database;

reorganizing the database so that the first data moves to a second offset value relative to the reference position; and

after the reorganizing the database, updating the first bookmark to associate with the second offset value instead of the first offset value,

wherein updating the first bookmark comprises associating the second offset with the first bookmark in accordance with (p −

x +y). where p denotes the first offset, x denotes the number of deleted bytes, and y denotes the number of inserted bytes between a start of the first data and the first offset associated with the first bookmark.

37. The method of claim 36 comprising:

storing the first bookmark in a bookmark dictionary of the database, wherein the bookmark dictionary comprises an entry having a bookmark identifier and an associated offset value; and

after the reorganizing the database, updating each entry of the bookmark dictionary.

38. The method of claim 36 wherein X is 0.

39. The method of claim 36 wherein Y is at least 1.

40. The method of claim 37 wherein the bookmark dictionary is stored in the database.

41. The method of claim 36 wherein the first data is positioned in the database relative to the reference position at the first offset value.

42. The method of claim 36 wherein the reorganizing the database comprises inserting a second data into the database between the reference position and the first offset value, and after the inserting a second data, the second offset value is larger than the first offset value.

43. The method of claim 36 wherein the reorganizing the database comprises inserting a second data into the database after reference position and the first offset value, and after the inserting a second data, the second offset value is the same as the first offset value.

44. The method of claim 36 wherein the reorganizing the database comprises removing a second data from the database that is between the reference position and the first offset value, and after the removing a second data, the second offset value is smaller than the first offset value.

45. The method of claim 36 wherein the reorganizing the database comprises removing a second data from the database that is after the reference position and the first offset value, and after the removing a second data, the second offset value is the same as the first offset value.

46. The method of claim 36 wherein the database is a relational database on a file server, the first data comprises position in a large object of the database, the reorganizing the database comprises recording the changes to the large object in a large object dictionary and the method further comprises:

after the recording the changes to the large object, rewriting the large object to include the changes recorded in the large object dictionary, wherein the updating the first bookmark occurs after the rewriting the large object.

47. The method of claim 46 wherein when the first data is deleted from the large object, deleting the first bookmark before the rewriting the large object.

48. The method of claim 46 wherein the recording the changes to the large object comprises an insertion of second data into the large object, and the method further comprises:

before the rewriting the large object, permitting access to the second data by using a second bookmark, wherein the second bookmark is linked to the second data through a third offset value relative to the reference position in the database.