Convert command into a BULK load operation

Patent metadata

Field	Value
Publication number	US-9600517-B2
Application number	US-201414290013-A
Country	US
Kind code	B2
Filing date	May 29, 2014
Priority date	May 29, 2014
Publication date	Mar 21, 2017
Grant date	Mar 21, 2017

Abstract

A method for inserting rows into a target table can include receiving a database command, the database command associated with a base table with at least one row to copy to a target table; receiving an indication that use of a BULK insert mode is feasible for the database command; based on the indication, and determining that an insert mode for the database command has been converted from a non-bulk insert mode to the BULK insert mode: reading a row from the base table; building the row read from the base table into an allocated row buffer; inserting the row into the target table in the BULK insert mode; and if it is determined that the allocated row buffer is full, updating at least one index in parallel with the inserting.

First claim

What is claimed is:

1. A method comprising:
receiving a database command, the database command associated with a base table with at least one row to copy to a target table;
receiving an indication that use of a BULK insert mode is feasible for the database command, the feasibility determined based on one or more run-time statistics for at least one buffer indicating a threshold number of full row buffers;
based on the indication, converting from a non-bulk insert mode to the BULK insert mode by allocating resources including one or more extents, each extent comprising a block of pages, and one or more threads for index updating; and
based on determining that an insert mode for the database command has been converted from a non-bulk insert mode to the BULK insert mode:
reading a row from the base table;
building the row read from the base table into an allocated row buffer within a block of pages of an extent of the one or more extents; and
based on a determination that the allocated row buffer is full, inserting rows within the allocated row buffer into the target table in the BULK insert mode and updating at least one index in parallel with the inserting, the updating using at least one thread of the one or more threads.

2. The method of claim 1 , further comprising:
beginning execution of a second database command, the second database command associated with the base table with at least one row to copy to the target table;
receiving an indication that the BULK insert mode is not feasible for the second database command;
based on the indication that the bulk insert mode is not feasible, building a row retrieved from the base table into a second allocated row buffer; and
inserting the second row into the target table in a non-BULK insert mode.

3. The method of claim 2 , further comprising:
determining that there are no rows remaining in the base table to be read; and
inserting any remaining rows buffered in the second allocated row buffer in the non-bulk insert mode; and
updating, in a serial mode, at least one index.

4. The method of claim 1 , wherein a row buffer manager allocates the allocated row buffer.

5. The method of claim 4 , wherein the allocated row buffer is a same size as a data page of the target table.

6. The method of claim 1 , further comprising:
allocating another row buffer.

7. The method of claim 6 , further comprising:
reading a second row from the base table;
building the second row read from the base table into the another allocated row buffer;
based on determining that the another allocated buffer is not full, reading a third row from the base table.

8. The method of claim 7 , further comprising:
determining that the base table contains two or more rows before reading the second row.

9. A system comprising:
one or more processors; and
a non-transitory computer-readable medium coupled to the one or more processors, the computer-readable medium comprising one or more modules, which when executed by the one or more processors, configure the one or more processors to perform one or more operations, the one or more modules including:
a command execution module configured to cause the one or more processors to:
initiate execution of a database command, the database command associated with a base table with at least one row to copy to a target table;
receive an indication that use of a BULK insert mode is feasible for the database command, the feasibility determined based on one or more run-time statistics for at least one buffer indicating a threshold number of full row buffers;
a row buffer manager module configured to cause the one or more processors to, based on the indication, and a determination that an insert mode for the database command has been converted from a non-bulk insert mode to the BULK insert mode:
read a row from the base table; and
build the row read from the base table into an allocated row buffer within a block of pages of an extent of one or more extents; and
an insert row module configured to cause the one or more processors to, based on the indication, convert from the non-bulk insert mode to the BULK insert mode by allocating resources including the one or more extents, each extent comprising a block of pages, and one or more threads for index updating, and based on a determination that the allocated row buffer is full:
insert rows within the allocated row buffer into the target table in the BULK insert mode; and
update at least one index in parallel with the inserting, the updating using at least one thread of the one or more threads.

10. The system of claim 9 , wherein:
the command execution module is configured to cause the one or more processors to:
begin execution of a second database command, the second database command associated with the base table with at least one row to copy to the target table; and
receive an indication that the BULK insert mode is not feasible for the second database command;
the row buffer manager is configured to build a row retrieved from the base table into a second allocated row buffer based on the indication that the BULK insert mode is not feasible; and
the insert row module is configured to insert the second row into the target table in a non-BULK insert mode.

11. The system of claim 10 , wherein the command execution module is configured to cause the one or more processors to determine that there are no rows remaining in the base table to be read; and
wherein the insert row module is configured to:
insert any remaining rows buffered in the second allocated row buffer in the non-bulk insert mode; and
update, in a serial mode, at least one index.

12. The system of claim 9 , wherein the row buffer manager module is configured to cause the one or more processors to allocate the allocated row buffer.

13. The system of claim 12 , wherein the allocated row buffer is a same size as a data page of the target table.

14. The system of claim 12 , where the row buffer manager module is configured to cause the one or more processors to allocate another row buffer when the allocated row buffer is full.

15. A non-transitory computer-readable medium comprising instructions, which when executed by at least one processor, configure the at least one processor to perform operations comprising:
initiating execution of a database command, the database command associated with a base table with at least one row to copy to a target table;
receiving an indication that use of a BULK insert mode is feasible for the database command, the feasibility determined based on one or more run-time statistics for at least one buffer indicating a threshold number of full row buffers;
based on the indication, converting from a non-bulk insert mode to the BULK insert mode by allocating resources including one or more extents, each extent comprising a block of pages, and one or more threads for index updating; and
based on determining that an insert mode for the database command has been converted from a non-bulk insert mode to the BULK insert mode:
reading a row from the base table;
building the row read from the base table into an allocated row buffer within a block of pages of an extent of the one or more extents; and
based on a determination that the allocated row buffer is full, inserting rows within the allocated row buffer into the target table in the BULK insert mode and updating at least one index in parallel with the inserting, the updating using at least one thread of the one or more threads.

16. The computer-readable medium of claim 15 , the operations further comprising:
beginn

Assignees

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

Inventors

• Ma Xiaobin
• Mottupalli Srinivasan
• Schneider Peter

Classifications

• G06F17/3038 Primary

  Physics · mapped topic

• G06F16/2386 Primary

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