Convert command into a BULK load operation

• US 9,600,517 B2
• Filed: 05/29/2014
• Issued: 03/21/2017
• Est. Priority Date: 05/29/2014
• Status: Active Grant

• Current Assignee: Sybase Incorporated (SAP SE)
• Original Assignee: Sybase Incorporated (SAP SE)
• Inventors: Ma, Xiaobin, Mottupalli, Srinivasan, Schneider, Peter
• Primary Examiner(s): Saeed, Usmaan
• Assistant Examiner(s): Bartlett, William P
• Application Number:
  US14/290,013
• Publication Number:
  US 20150347409A1
• Time in Patent Office: 1,027 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, 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:

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

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

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

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

allocating another row buffer.