Database system with methods for appending data records by partitioning an object into multiple page chains

• US 5,717,919 A
• Filed: 10/02/1995
• Issued: 02/10/1998
• Est. Priority Date: 10/02/1995
• Status: Expired due to Term

• Current Assignee: Sybase Incorporated (SAP SE)
• Original Assignee: Sybase Incorporated (SAP SE)
• Inventors: Chatterjee, Sumanta, Joshi, Ashok Madhukar, McCready, Bruce, Kodavalla, Hanuma
• Primary Examiner(s): Black, Thomas G.
• Assistant Examiner(s): ROBINSON, GRETA LEE
• Application Number:
  US08/537,020
• Time in Patent Office: 862 Days
• Field of Search: 395/608, 395/617, 395/610, 395/761, 395/792, 395/772, 395/770, 364/225.8
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: Y10S 707/99938 Concurrency, e.g. lock management in shared database
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users

Abstract

A Client/Server Database System with improved methods for appending items to an object, such as appending data records to a database table, in the context of a multi-user environment is described. The system includes one or more Clients (e.g., Terminals or PCs) connected via a Network to a Server. The Clients store data in and retrieve data from one or more database tables resident on the Server by submitting SQL commands, some of which specify insert or append operations, for appending records to a table. For enhancing the speed in which multiple appenders (i.e., Clients) can append records, the operation of the server is modified to store an object (e.g., table) as multiple (physical) page chains. From the logical viewpoint, a single (logical) page chain of data pages is presented to each client or user. From the perspective of inserting records, however, the system has multiple page chains to insert into, thereby removing contention among multiple appenders for the last page.

Claims

1. In a database system storing information in a database table, said database table comprising rows of data records stored on data pages, a method for appending data records from a client to said database table comprising:

linking said data pages together to form a single physical page chain in memory, said page chain having a first page and a last page;

receiving a request from said client for dividing said database table into a number of partitions;

in response to said request, dividing said page chain into a number of partitions, each partition comprising a physical page chain having a last page so that multiple last pages are available to the system for appending data records;

receiving a request for appending records to the end of said database table;

in response to said request, selecting one of said partitions for appending records to the end of said database table; and

appending said data records from said client to the last page of said selected partition, while at the same time other partitions remain available to other clients for appending records.

2. The method of claim 1, wherein said appending step includes:

locking said last page while appending said data records to said selected partition, so that other clients cannot append records concurrently to the last page of the same partition.

3. The method of claim 1, wherein said request from said client for dividing said database table into a number of partitions comprises an SQL ALTER request to alter a table.

4. The method of claim 1, wherein said step of selecting one of said partitions for appending records comprises:

selecting at random one of said partitions for appending records.

5. The method of claim 1, wherein said request for appending records comprises a database transaction, wherein said step of selecting one of said partitions for appending records comprises selecting at random one of said partitions for appending records, and wherein said step of appending said data records comprises appending to the last page of said randomly selected partition.

6. The method of claim 5, wherein said randomly selected partition is employed for appending records throughout the duration of said transaction.

7. The method of claim 1, wherein said step of selecting one of said partitions for appending records comprises:

selecting in a round-robin fashion one of said partitions for appending records.

8. The method of claim 1, wherein said step of dividing said page chain into a number of partitions includes:

creating for each particular partition a control page describing where the last page is located for the page chain of the particular partition.

9. The method of claim 8, further comprising:

storing the control page for said selected partition in a cache memory.

10. The method of claim 8, further comprising:

updating said control page stored in the cache memory, when a new last page is allocated for said selected partition.

11. The method of claim 1, wherein said number of partitions is configurable by a user of the client.

12. The method of claim 1, further comprising:

receiving a request from the client to scan said database table; and

in response to said request to scan said database table, scanning the page chain of each partition successively in turn, until all page chains for said database table have been scanned.

13. The method of claim 12, wherein said request from said client for scanning said database table comprises a standard SQL request, so that said scanning of the page chain of each partition successively in turn remains transparent to said client.

14. The method of claim 1, wherein said database table is logically indexed by at least one index.

15. The method of claim 14, wherein said at least one index comprises a B-tree index storing record pointers at its leaf-level nodes.

16. The method of claim 1, wherein said request for appending records to the end of said database table comprises a standard SQL INSERT request, so that appending said data records from said client to the last page of said selected partition remains transparent to said client.

17. The method of claim 1, further comprising:

allocating a new last page for said selected partition, when the existing last page for said selected partition is full; and

linking said new last page into the particular page chain for said selected partition.

18. The method of claim 1, further comprising:

receiving information specifying storage devices available for storing said database table; and

wherein said step of dividing said page chain into a number of partitions comprises mapping a particular page chain of each partition to a particular device.

19. The method of claim 18, wherein said mapping step comprises:

mapping a particular page chain of each partition to a particular device based on how many devices are available for storage of the database table and how many partitions are created.

20. The method of claim 1, further comprising:

receiving a request from the client for determining whether said database table is empty;

in response to said request for determining whether said database table is empty, examining the first page of the page chain of each partition successively in turn, until either a row is found on the first page of one of the page chains or until the first page of each page chain has been examined.

21. In a multi-user computer system where contention exists for appending items to one end of an object, said object storing items in a plurality of data pages, an improved method for appending items to the object comprising:

storing said object as a single logical page chain comprising multiple individual physical page chains, each individual physical page chain having a last page for insertion of items;

receiving a first request from a first client for appending an item to said object;

selecting a first one of said individual physical page chains;

receiving a second request from a second client for appending an item to said object;

selecting a second one of said individual physical page chains; and

appending items from said first client to the last page of first selected page chain while concurrently appending items from said second client to the last page of second selected page chain.

22. The method of claim 21, wherein said appending step includes:

locking said last page of said first selected page chain by said first client and locking said last page of said second selected page chain by said second client, so that each client has exclusive access to the last page of the page chain to which it is appending records.

23. The method of claim 21, wherein said first selected page chain and said second selected page chain are selected randomly by the system.

24. The method of claim 21, wherein said first selected page chain and said second selected page chain are selected in a round-robin manner by the system.

25. The method of claim 21, wherein said object includes a database table.

26. The method of claim 25, wherein said database table comprises a log storing records which are sequentially appended to one end of the table.

27. The method of claim 21, further comprising:

receiving a request to scan the data pages of the object;

in response to said request to scan said data pages of the object, scanning each page chain of said multiple individual physical page chains, successively in turn, until all page chains for said object have been scanned.

28. A database management system for storing and processing a database table comprising:

a computer having a processor, a memory, and a plurality of storage devices;

means for dividing said database table into a number of partitions, each partition comprising a physical page chain having a last page so that multiple last pages are available to the system for appending data records, each physical page chain being stored on a particular one of said plurality of storage devices;

input means for inputting a request from a client for appending records to the end of said database table;

means, responsive to said request, for selecting one of said partitions for appending records to the end of said database table; and

means for appending said data records from said client to the last page of said selected partition, while at the same time other partitions remain available to other clients for appending records.

29. The system of claim 28, wherein said means for dividing said database table into a number of partitions includes:

device affinity means for assigning each partition to a particular one of said storage devices based on how many storage devices are available for storage of the database table and how many partitions are created.

30. The system of claim 29, wherein said device affinity means includes:

means for assigning each partition to a particular one of said storage devices in round-robin manner until all partitions have been assigned.