Convert Command Into a BULK Load Operation

• US 20150347409A1
• Filed: 05/29/2014
• Published: 12/03/2015
• Est. Priority Date: 05/29/2014
• Status: Active Grant

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

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.

Claims

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;

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.

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 the serial mode, at least one index.

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

5. The method of claim 4, wherein the allocated row buffer is the size of 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 at least one row before reading the second row.

9. A system comprising:

a command execution module configured 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;

a row buffer manager module configured 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; and

an insert row module configured to, based on the indication;

insert the row into the target table in the BULK insert mode; and

if a determination is made that the allocated row buffer is full, update at least one index in parallel with the inserting.

10. The system of claim 9, wherein:

the command execution unit is configured 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 unit is configured 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 the serial mode, at least one index.

12. The system of claim 9, wherein the row buffer manager is configured to allocate the row buffer.

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

14. The system of claim 12, where the row buffer manger is configured to allocate another row buffer when a 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;

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.

16. The computer-readable medium of claim 15, the operations 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.

17. The computer-readable medium of claim 16, the operations 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 the serial mode, at least one index.

18. The computer-readable medium of claim 15, wherein a row buffer manager allocates the row buffer.

19. The computer-readable medium of claim 18, wherein the allocated row buffer is the size of a data page of the target table.

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

allocating another row buffer.