INDEX UPDATES USING PARALLEL AND HYBRID EXECUTION

• US 20160012083A1
• Filed: 07/08/2014
• Published: 01/14/2016
• Est. Priority Date: 07/08/2014
• Status: Active Grant

• Current Assignee: Sybase Incorporated (SAP SE)
• Original Assignee: Sybase Incorporated (SAP SE)
• Inventors: Mottupalli, Srinivasan, Ma, Xiaobin, Schneider, Peter
• Granted Patent: US 9,684,684 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2228 Indexing structures
• CPC Class Codes: G06F 16/2386 Bulk updating operations data conversion details G06F16/258

Abstract

A method can include receiving a request to execute a database command identifying a target table; identifying a plurality of rows to insert into the target table based in part on the database command; writing rows, from the plurality of rows, into a data page until the data page is full; determining, by an index thread manager, a number of threads to use for updating indexes defined for the target table; and upon determining the data page is full, updating, in parallel, the indexes defined for the target table using the number of threads.

Claims

1. A method comprising:

receiving a request to execute a database command identifying a target table;

identifying a plurality of rows to insert into the target table based in part on the database command;

writing rows, from the plurality of rows, into a data page until the data page is full;

determining, by an index thread manager, a number of threads to use for updating indexes defined for the target table; and

upon determining the data page is full, updating, in parallel, the indexes defined for the target table using the number of threads.

2. The method of claim 1, wherein determining, by the index thread manager, the number of threads to use for updating indexes defined for the target table comprises:

determining a number of available threads for updating the indexes defined for the target table;

allocating a number of the available threads as threads for updating the indexes defined for the target table, wherein the number of the allocated threads is equal to the number of indexes defined for the target table when the number of available threads is equal to or greater than the number of indexes defined for the target table; and

for each particular respective thread of the allocated threads, assigning, by the index thread manager, a respective index of the indexes defined for the target table.

3. The method of claim 1, wherein determining, by the index thread manager, the number of threads to use for updating indexes defined for the target table comprises:

determining a number of available threads for updating the indexes defined for the target table;

allocating a number of the available threads for updating the indexes defined for the target table, wherein the number of the allocated threads is less than the number of indexes defined for the target table when the number of available threads is less than the number of indexes defined for the target table; and

for each particular respective thread of the allocated threads, assigning, by the index thread manager, one or more indexes of the indexes defined for the target table.

4. The method of claim 3, further comprising:

accessing a configuration file to determine a maximum number of threads to use for a single database command; and

limiting the number of allocated threads to the maximum number of threads to use for a single database command.

5. The method of claim 3, further comprising:

passing an identifier of the data page to the index thread manager; and

passing the identifier of the data page from the index thread manager to a thread of the allocated threads, the thread using the identifier to access the rows in the data page to update the one or more indexes assigned to the thread.

6. The method of claim 1, further comprising, writing a second of set of the plurality of rows into a second data page concurrently with the indexes defined for the target table being updated.

7. The method of claim 1, further comprising:

determining that the plurality of rows are inserted into the target table using a bulk insert mode before updating the indexes using the number of threads.

8. A system comprising:

at least one processor; and

a storage device with instructions stored thereon, which when executed by the least one processor implement;

a command execution module configured to receive a request to execute a database command identifying a target table;

a scanning module configured to identify a plurality of rows to insert into the target table based in part on the database command;

an insert module configured to write rows, from the plurality of rows, into a data page until the data page is full;

an index thread manager configured to determine a number of threads to use for updating indexes defined for the target table; and

wherein the number of threads update, in parallel, the indexes defined for the target table when the data page is full.

9. The system of claim 8, wherein to determine a number of threads to use for updating indexes defined for the target table, the index thread manager is configured to:

determine a number of available threads for updating the indexes defined for the target table;

allocate a number of the available threads as threads for updating the indexes defined for the target table, wherein the number of the allocated threads is equal to the number of indexes defined for the target table when the number of available threads is equal to or greater than the number of indexes defined for the target table; and

for each particular respective thread of the allocated threads, assign a respective index of the indexes defined for the target table.

10. The system of claim 8, wherein to determine a number of threads to use for updating indexes defined for the target table, the index thread manager is configured to:

determine a number of available threads for updating the indexes defined for the target table;

allocate a number of the available threads for updating the indexes defined for the target table, wherein the number of the allocated threads is less than the number of indexes defined for the target table when the number of available threads is less than the number of indexes defined for the target table; and

for each particular respective thread of the allocated threads, assign one or more indexes of the indexes defined for the target table.

11. The system of claim 10, wherein the index thread manager is further configured to:

access a configuration file to determine a maximum number of threads to use for a single database command; and

limit the number of allocated threads to the maximum number of threads to use for a single database command.

12. The system of claim 10, wherein the index thread manager is further configured to:

receive an identifier of the data page; and

pass the identifier of the data page from the index thread manager to a thread of the allocated threads, the thread using the identifier to access the rows in the data page to update the one or more indexes assigned to the thread.

13. The system of claim 8, wherein the insert module is configured to write a second of set of the plurality of rows into a second data page concurrently with the indexes defined for the target table being updated.

14. The system of claim 8, wherein the command execution module is configured to determine that the plurality of rows are inserted into the target table using a bulk insert mode before the indexes are updated using the number of threads.

15. A non-transitory computer-readable medium comprising instructions, which when executed by one or more processors, configure the one or more processors to perform operations comprising:

receiving a request to execute a database command identifying a target table;

identifying a plurality of rows to insert into the target table based in part on the database command;

writing rows, from the plurality of rows, into a data page until the data page is full;

determining, by an index thread manager, a number of threads to use for updating indexes defined for the target table; and

upon determining the data page is full, updating, in parallel, the indexes defined for the target table using the number of threads.

16. The non-transitory computer-readable medium of claim 15, wherein the operation for determining, by the index thread manager, the number of threads to use for updating indexes defined for the target table comprises:

determining a number of available threads for updating the indexes defined for the target table;

allocating a number of the available threads as threads for updating the indexes defined for the target table, wherein the number of the allocated threads is equal to the number of indexes defined for the target table when the number of available threads is equal to or greater than the number of indexes defined for the target table; and

for each particular respective thread of the allocated threads, assigning, by the index thread manager, a respective index of the indexes defined for the target table.

17. The non-transitory computer-readable medium of claim 15, wherein the operation of determining, by the index thread manager, the number of threads to use for updating indexes defined for the target table comprises:

determining a number of available threads for updating the indexes defined for the target table;

allocating a number of the available threads for updating the indexes defined for the target table, wherein the number of the allocated threads is less than the number of indexes defined for the target table when the number of available threads is less than the number of indexes defined for the target table; and

for each particular respective thread of the allocated threads, assigning, by the index thread manager, one or more indexes of the indexes defined for the target table.

18. The non-transitory computer-readable medium of claim 17, wherein the instructions configure the one or more processors to perform operations comprising:

accessing a configuration file to determine a maximum number of threads to use for a single database command; and

limiting the number of allocated threads to the maximum number of threads to use for a single database command.

19. The non-transitory computer-readable medium of claim 17, wherein the instructions configure the one or more processors to perform operations comprising:

passing an identifier of the data page to the index thread manager; and

passing the identifier of the data page from the index thread manager to a thread of the allocated threads, the thread using the identifier to access the rows in the data page to update the one or more indexes assigned to the thread.

20. The non-transitory computer-readable medium of claim 15, wherein the instructions configure the one or more processors to perform operations comprising:

writing a second of set of the plurality of rows into a second data page concurrently with the indexes defined for the target table being updated.