Storage model for large object columns

• US 7,853,619 B2
• Filed: 09/23/2004
• Issued: 12/14/2010
• Est. Priority Date: 09/23/2004
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Chang, Thomas, Muralidhar, Subramanian, Yu, Qin, Kotsovolos, Susan, Krishnaprasad, Muralidhar
• Primary Examiner(s): Rones; Charles
• Assistant Examiner(s): Hotelling; Harold A
• Application Number:
  US10/949,895
• Publication Number:
  US 20060075006A1
• Time in Patent Office: 2,273 Days
• Field of Search: 707/205, 707/201
• US Class Current: 707/795
• CPC Class Codes: G06F 16/2219 Large Object storage; Management thereof
• CPC Class Codes: G06F 16/289 Object oriented databases

Abstract

Methods and apparatus for enabling a plurality of table columns arranged to be associated with large objects (LOBs) to share a single LOB data segment within a database are disclosed. According to one aspect of the present invention, a method for allocating space associated with at least a first column arranged to accommodate LOB data and a second column arranged to accommodate LOB data includes allocating a first LOB data segment. The method also includes associating both the first column and the second column with the first LOB data segment.

Claims

1. A computer-implemented method for allocating space associated with at least one table of a computing system, the at least one table having at least a first column arranged to accommodate large object (LOB) data and a second column arranged to accommodate LOB data, the at least one table being arranged within a database, the method comprising:

allocating a first LOB data segment associated with the at least one table, wherein the first LOB data segment is arranged within the database;

associating the first column with the first LOB data segment;

after the associating the first column with the first LOB data segment, when a size of a first LOB is greater than a first threshold size, storing the first LOB, associated with the first column, in the first LOB data segment at a first location;

associating the second column with the first LOB data segment; and

after the associating the second column with the first LOB data segment, when a size of a second LOB is greater than a second threshold size, storing the second LOB, associated with the second column, in the first LOB data segment at a second location subsequent to the first location, wherein the second LOB is different from the first LOB.

2. The method of claim 1 further comprising:

when the size of the first LOB is not greater than the first threshold size, storing the first LOB, associated with the first column, in-line in the first column; and

when the size of the second LOB is not greater than the second threshold size, storing the second LOB, associated with the second column, in-line in the second column.

3. The method of claim 1 wherein associating the second column with the first LOB data segment includes causing a LOB associated with the second column to be arranged to be stored wholly in the first LOB data segment.

4. The method of claim 1 wherein the at least one table includes a first table, and the first column and the second column are included in the first table, wherein the first column and second column are part of the first table not as a result of a database join operation.

5. The method of claim 1 wherein the at least one table includes a first table and a second table, and the first column is a part of the first table and the second column is a part of the second table.

6. The method of claim 1 wherein the first column is associated with a first partition of the at least one table and the second column is associated with a second partition of the at least one table,

wherein each partition comprises two or more records, and the first and second partitions are associated with the first LOB data segment.

7. The method of claim 1 wherein the storing the first LOB, associated with the first column, in the first LOB data segment at a first location comprises storing a first indicator in the first column that points to the first location, and

wherein the storing the second LOB, associated with the second column, in the first LOB data segment at a second location comprises storing a second indicator in the second column that points to the second location.

8. The method of claim 1 wherein the first LOB, associated with the first column, and the second LOB, associated with the second column, are both stored in the first LOB data segment.

9. A computer program product for allocating space associated with at least one table of a computing system, the at least one table having at least a first column arranged to accommodate large object (LOB) data and a second column arranged to accommodate LOB data, the at least one table being arranged within a database, the computer program product comprising:

computer code that causes a first LOB data segment associated with the at least one table to be allocated, wherein the first LOB data segment is arranged within the database;

computer code that causes the first column to be associated with the first LOB data segment;

computer code that causes the second column to be associated with the first LOB data segment;

computer code that causes a first LOB to be received, the first LOB being associated with the first column;

computer code that causes a second LOB to be received, the second LOB being associated with the second column;

computer code that causes a size of the first LOB to be determined;

computer code that causes the first LOB, associated with the first column, to be stored in the first LOB data segment when it is determined that the size of the first LOB exceeds a first threshold;

computer code that causes a size of the second LOB to be determined;

computer code that causes the second LOB, associated with the second column, to be stored in the first LOB data segment when it is determined that the size of the second LOB exceeds a second threshold; and

a computer-readable medium that stores the computer codes.

10. The computer program product of claim 9 wherein the computer code that causes the first column to be associated with the first LOB data segment includes computer code that causes the first LOB, associated with the first column, to be arranged to be stored wholly in the first LOB data segment.

11. The computer program product of claim 9 further comprising:

computer code that causes the first LOB, associated with the first column, to be stored in in-line in the first column when it is determined that the size of the first LOB does not exceed the first threshold; and

computer code that causes the second LOB, associated with the second column, to be stored in-line in the second column when it is determined that the size of the second LOB does not exceed the second threshold.

12. The computer program product of claim 9 wherein the at least one table includes a first table, and the first column and the second column are included in the first table, wherein the first column and second column are part of the first table not as a result of a database join operation.

13. The computer program product of claim 9 wherein the size of the first LOB exceeds the first threshold and the size of the second LOB exceeds the second threshold.

14. The computer program product of claim 9 wherein the first LOB is stored only in the first LOB data segment and not in the first column, associated with the first LOB.

15. The computer program product of claim 14 wherein the second LOB, different from the first LOB, is stored only in the first LOB data segment and not in the second column, associated with the second LOB.

16. A database in a computing system, the database comprising:

at least one table stored in a memory device of the computing system, the at least one table having a first large object (LOB) column and a second LOB column; and

a first LOB data segment, the first LOB data segment being arranged to store data associated with the at least one table, both the first LOB column and the second LOB column being mapped to the first LOB data segment;

first data, associated with the first LOB column, having a size that exceeds a first threshold amount and being arranged to be stored in the first LOB data segment; and

second data, associated with the second LOB column, having a size that exceeds a second threshold amount and being arranged to be stored in the first LOB data segment where the first data is stored.

17. The database of claim 16 wherein the first data is stored in a first location in the first LOB data second segment, the second data is stored in a second location in the first LOB data segment, and the database further comprises:

third data, associated with the first LOB column, having a size that exceeds the first threshold amount and being arranged to be stored in a third location in the first LOB data segment, wherein the second location is between the first and third locations in the first LOB data segment.

18. The database of claim 17 wherein the first threshold amount is user-configurable to be different from a default value for the first threshold amount.

19. The database of claim 16 wherein the first threshold amount is the same as the second threshold amount.

20. The database of claim 16 wherein the first threshold amount is different from the second threshold amount.

21. The database of claim 16 wherein the second data is stored as a LOB wholly in the first LOB data segment.

22. The database of claim 16 wherein the at least one table includes a first table, and the first LOB column and the second LOB column are each associated with the first table.

23. The database of claim 16 wherein the at least one table includes a first table and a second table, the first LOB column being associated with the first table and the second LOB column being associated with the second table.

24. The database of claim 16 wherein the first LOB column is associated with a first partition of the at least one table and the second LOB column is associated with a second partition of the at least one table.

25. The database of claim 16 wherein the first data is different from the second data.

26. The database of claim 16 wherein the first data is stored starting at a first position in the first LOB data segment, and the second data is stored starting at a second position in the first LOB data segment, further comprising:

a first indicator stored in the first column, the first indicator being arranged to identify the first LOB stored at the first position in the LOB data segment; and

a second indicator stored in the second column, the second indicator being arranged to identify the second LOB stored at the second position in the LOB data segment.

27. The database of claim 26 wherein the first indicator is stored in the first column and not stored in the second column.

28. A computer-implemented method for storing data associated with at least a first column arranged to accommodate large object (LOB) data and a second column arranged to accommodate LOB data, the first column and the second column each being associated with a shared LOB data segment, the first column, the second column, and the shared LOB data segment being arranged within a database, the method comprising:

receiving a first set of data to be stored, the first set of data being associated with the first column;

determining when the first set of data is of a size that exceeds a first threshold;

storing the first set of data in the shared LOB data segment when it is determined that the first set of data is of the size that exceeds the first threshold;

storing the first set of data in-line in the first column when it is determined that the first set of data is not of the size that exceeds the first threshold;

receiving a second set of data to be stored, the second set of data being associated with the second column, wherein the second set of data to be stored is different from the first set of data;

determining when the second set of data is of a size that exceeds a second threshold;

storing the second set of data in the shared LOB data segment when it is determined that the second set of data is of the size that exceeds the second threshold; and

storing the second set of data in-line in the second column when it is determined that the second set of data is not of the size that exceeds the second threshold.

29. The method of claim 28 wherein storing the first set of data in the shared LOB data segment includes storing a first indicator in the first column that is arranged to identify the first set of data and storing the second set of data in the shared LOB data segment includes storing a second indicator in the second column that is arranged to identify the second set of data.

30. The method of claim 28 comprising:

when the first set of data is of the size that exceeds the first threshold, storing the first set of data in the shared LOB data segment in a first memory location that is open;

receiving the second set of data to be stored after the first set of data has been stored in the shared LOB data segment; and

while the first set of data is stored in the shared LOB data segment, determining that the second set of data is of the size that exceeds the second threshold, and storing the second set of data in the shared LOB data segment, where the first set of data is stored, in a second memory location that is open, the second memory location being different from and after the first memory location of the shared LOB data segment.

31. The method of claim 28 wherein the first threshold and the second threshold are a same size value.

32. The method of claim 28 wherein the first column and the second column are part of a first table.

33. The method of claim 28 wherein the storing the first set of data in the shared LOB data segment when it is determined that the first set of data is of the size that exceeds the threshold and the storing the second set of data in the shared LOB data segment when it is determined that the second set of data is of the size that exceeds the second threshold comprises

not storing any of the first or second set of data in-line in the first or second columns.

34. The method of claim 28 wherein the storing the first set of data in the shared LOB data segment when it is determined that the first set of data is of the size that exceeds the threshold and the storing the second set of data in the shared LOB data segment when it is determined that the second set of data is of the size that exceeds the second threshold comprises

storing the first set of data entirely in the LOB data segment and storing the second set of data, associated with the second column, entirely in the LOB data segment along with the first set of data, which is associated with the first column.

35. The method of claim 28 wherein the storing the first set of data in the shared LOB data segment when it is determined that the first set of data is of the size that exceeds the threshold comprises

storing the first set of data starting with the LOB data segment, and

wherein storing the second set of data in-line in the second column when it is determined that the second set of data is not of the size that exceeds the second threshold comprises

not storing any of the second set of data in the LOB data segment.

36. The method of claim 35 comprising:

receiving a third set of data to be stored, the third set of data being associated with the second column, wherein the third set of data to be stored is different from the first and second sets of data;

determining when the third set of data is of a size that exceeds the second threshold;

storing the third set of data, associated with the second column, in the shared LOB data segment, along with the first set of data associated with the first column, when it is determined that the third set of data is of the size that exceeds the second threshold,

wherein the second set of data, associated with the second column, is not stored in the shared LOB data with the first and third sets of data.

37. A computer-implemented method for allocating space associated with at least one table of a computing system, the at least one table having at least a first column and a second column, the at least one table being arranged within a database, the method comprising:

allocating a first data segment, associated with the at least one table, to store data associated with the first and second columns, wherein the first data segment is arranged within the database;

associating the first column with the first data segment;

associating the second column with the first data segment;

receiving a first LOB to be stored, the first LOB having a first size greater than a first threshold;

receiving a second LOB to be stored, the second LOB having a second size greater than a second threshold; and

storing both the first LOB, associated with the first column, and the second LOB, associated with the second column, in the first LOB data segment.

38. The method of claim 37 wherein the first LOB is stored in a first position in the first LOB data segment, further comprising:

receiving a request to modify the first LOB;

modifying the first LOB according to the request;

if a size of the modified first LOB is less than the first threshold size, storing the modified first LOB in-line in the first column; and

if the size of the modified first LOB is not less than the first threshold size, determining if there is available space in the first data segment to store the modified first LOB, wherein

if there is available space in the first data segment to store the modified first LOB, storing the modified first LOB at a second position in the first LOB data segment, the second position different from the first position, and

if there is not available space in the first data segment to store the modified first LOB, determining if a size of the first data segment can be increased.

39. The method of claim 38 wherein the second position in the first data segment is a next open location after the first position in the first data segment.

40. The method of claim 38 further comprising:

if it is determined that the size of the first data segment can be increased, increasing the size of the first data segment; and

if it is determined that the size of the first data segment cannot be increased, throwing an exception.

41. The method of claim 37 wherein the at least one table includes a first table and a second table, and the first column is a part of the first table and the second column is a part of the second table.

42. The method of claim 37 further comprising:

receiving a third LOB to be stored, the third LOB associated with a third column and having a third size greater than a third threshold; and

storing the third LOB, associated with the third column, in the first LOB data segment.

43. A computer program product for storing data associated at least a first column arranged to accommodate large object (LOB) data and a second column arranged to accommodate LOB data, the first column and the second column each being associated with a shared LOB data segment, the first column, the second column, and the shared LOB data segment being arranged within a database, the computer program product comprising:

computer code that causes a first set of data to be stored to be received, the first set of data being associated with the first column;

computer code that causes a determination of when the first set of data is of a size that exceeds a first threshold;

computer code that causes the first set of data to be stored in the shared LOB data segment when it is determined that the first set of data is of the size that exceeds the first threshold;

computer code that causes the first set of data to be stored in-line in the first column when it is determined that the first set of data is not of the size that exceeds the first threshold;

computer code that causes a second set of data to be stored to be received, the second set of data being associated with the second column;

computer code that causes a determination of when the second set of data is of a size that exceeds a second threshold;

computer code that causes the second set of data to be stored in the shared LOB data segment when it is determined that the second set of data is of the size that exceeds the second threshold;

computer code that causes the second set of data to be stored in-line in the second column when it is determined that the second set of data is not of the size that exceeds the second threshold; and

a computer-readable medium that stores the computer code.

44. The computer program product of claim 43 wherein computer code that causes the first set of data to be stored in the shared LOB data segment includes computer code that causes a first indicator in the first column to be stored, the first indicator being arranged to identify the first set of data and computer code that causes the second set of data to be stored in the shared LOB data segment includes computer code that causes a second indicator in the second column to be stored, the second indicator being arranged to identify the second set of data.

45. The computer program product of claim 43 wherein the first threshold and the second threshold are a same size value.

46. The computer program product of claim 43 wherein computer code that causes the first set of data to be stored in the shared LOB data segment when it is determined that the first set of data is of the size that exceeds the first threshold and computer code that causes the second set of data to be stored in the shared LOB data segment when it is determined that the second set of data is of the size that exceeds the second threshold comprises

computer code that causes not storing any of the first or second set of data in-line in the first or second column.

47. The computer program product of claim 43 wherein computer code that causes the first set of data to be stored in the shared LOB data segment when it is determined that the first set of data is of the size that exceeds the first threshold and computer code that causes the second set of data to be stored in the shared LOB data segment when it is determined that the second set of data is of the size that exceeds the second threshold comprises

computer code that causes storing the first set of data entirely in the shared LOB data segment and computer code that causes storing the second set of data, associated with the second column, entirely in the shared LOB data segment along with the first set of data, which is associated with the first column.

48. The computer program product of claim 43 wherein computer code that causes the first set of data to be stored in the shared LOB data segment when it is determined that the first set of data is of the size that exceeds the first threshold comprises

computer code that causes the first set of data to be stored starting with the shared LOB data segment, and

wherein computer code that causes the second set of data to be stored in-line in the second column when it is determined that the second set of data is not of the size that exceeds the second threshold comprises computer code that causes not storing any of the second set of data in the shared LOB data segment.

49. The computer program product of claim 43 wherein the first set of data is of the size that exceeds the first threshold and the second set of data is of the size that exceeds the second threshold.

50. A computing system comprising:

a database, the database including a first table column arranged to be associated with large object (LOB) data and a second table column arranged to be associated with LOB data, the database further including a first LOB data segment;

means for allocating the first LOB data segment to both the first table column and the second table column;

means for storing a LOB associated with the first table column in the first LOB data segment; and

means for storing a LOB associated with the second table column in the first LOB data segment.

51. The computing system of claim 50 wherein the first table column and the second table column are a part of a first table, the first column and second column being part of the first table not as a result of a database join operation.