Index updates using parallel and hybrid execution

Patent metadata

Field	Value
Publication number	US-9684684-B2
Application number	US-201414326157-A
Country	US
Kind code	B2
Filing date	Jul 8, 2014
Priority date	Jul 8, 2014
Publication date	Jun 20, 2017
Grant date	Jun 20, 2017

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.

First claim

What is claimed is:

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 by:
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; 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 , 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.

3. The method of claim 1 , 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.

4. 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.

5. 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.

6. 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 by:
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; and

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

7. The system of claim 6 , 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.

8. The system of claim 6 , 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.

9. The system of claim 6 , 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.

10. The system of claim 6 , 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.

11. 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 by:
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; and

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

12. The non-transitory computer-readable medium of claim 11 , 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.

13. The non-transitory computer-readable medium of claim 11 , 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.

14. The non-transitory computer-readable medium of claim 11 , 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.

15. 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 by:
determining a number of available threads for updating the indexes def

Assignees

• Mottupalli Srinivasan
• Ma Xiaobin
• Schneider Peter
• Sybase Inc

Inventors

• Mottupalli Srinivasan
• Ma Xiaobin
• Schneider Peter

Classifications

• G06F16/2386

  Bulk updating operations (data conversion details G06F16/258) · CPC title

• G06F16/2228 Primary

  Indexing structures · CPC title

• G06F17/3038

  Physics · mapped topic

• G06F17/30321 Primary

  Physics · mapped topic