TECHNIQUES FOR PROCESSING OPERATIONS ON COLUMN PARTITIONS IN A DATABASE

• US 20120166400A1
• Filed: 12/28/2010
• Published: 06/28/2012
• Est. Priority Date: 12/28/2010
• Status: Abandoned Application

• Current Assignee: Teradata US, Inc. (Teradata Corporation)
• Original Assignee: Teradata US, Inc. (Teradata Corporation)
• Inventors: Sinclair, Paul, Pederson, Donald R.
• Application Number:
  US12/979,526
• Publication Number:
  US 20120166400A1
• Time in Patent Office: Days
• US Class Current: 707/692
• CPC Class Codes: G06F 16/221 Column-oriented storage; Management thereof
• CPC Class Codes: G06F 16/23 Updating

Abstract

Techniques for processing operations on column partitions of a table in a database are provided. A table includes a control column partition. Each delete container of the control column partition representing multiple rows in the table (or a row partition, if any), and each row represented by a bit flag within a bit string. Rows of the table set for deletion have their corresponding bits within a particular delete container set to indicate those rows are deleted.

Claims

1. A method implemented and programmed within a non-transitory computer-readable storage medium and processed by a processor, the processor configured to execute the method, comprising:

receiving, via the processor, a row identifier for a row to delete within a table of a database;

ensuring, via the processor, that the row identifier is represented within a control column partition associated with the table; and

setting, via the processor, a bit flag for the row within the control column partition to indicate that the row is deleted from the table.

2. The method of claim 1, wherein receiving further includes identifying the table as being row partitioned and the row is deleted within a row partition of the table and a delete container is one of several delete containers for that row partition.

3. The method of claim 1, wherein ensuring further includes creating a new delete container within the control column partition, the new delete container including the row identifier and the bit flag.

4. The method of claim 1, wherein ensuring further includes identifying an existing delete container within the control column partition having a different row identifier, the existing delete container representing an existing row within the table previously set for deletion, and the row identifier is within a predetermined range of the different row identifier and adding the bit flag to an existing bit string for the existing container to represent the row, a location for the bit flag within the existing bit string identifying the row.

5. The method of claim 4, wherein ensuring further includes padding the existing bit string with unset values beginning at an offset location representing the existing row and including one of the unset values for each intermediate row that exists between the existing row and the row.

6. The method of claim 5, wherein padding further includes prefixing the unset values to the existing bit string when the row precedes the existing row within the table.

7. The method of claim 6, wherein padding further includes appending the unset values to the existing bit string when the row follows the existing row within the partition.

8. The method of claim 1, wherein ensuring further includes identifying an existing delete container within the control column partition having a different row identifier, the existing delete container includes representing an existing row within the table previously set for deletion, and setting the bit for the row to be deleted causes the delete container to exceed the maximum container size and splitting an existing bit string associated with the existing delete container into two different containers having the existing container with a modified bit string and a new container including part of the existing bit string with the bit flag representing the row set for deletion.

9. The method of claim 1 further comprising, adjusting, adding, or removing, via the processor, run length fields for a delete container to reflect frequency counts in a compressed version of the data for the delete container when the row is deleted for the table.

10. The method of claim 1 further comprising, creating, via the processor, run length fields for the delete container associated with the row to reflect frequency counts in a compressed version of the delete container when no run length field existed and still useful when the row is deleted for the table.

11. A method implemented and programmed within a non-transitory computer-readable storage medium and processed by a processor, the processor configured to execute the method, comprising:

establishing, via the processor, a control column partition, the control column partition includes zero or more delete containers where each container represents multiple rows for a table of a database, each single delete container including a bit string where each bit represents a particular row within the table that is set for deletion;

receiving, via the processor, a select operation for processing against the partition; and

accessing, via the processor, delete containers of the control column partition and the bit strings to determine whether to process the select operation against a row of the table.

12. The method of claim 11, wherein establishing further includes setting a particular bit of a particular bit string within a particular single delete container when a delete for a particular row of the table is received.

13. The method of claim 11, wherein accessing further includes determining via the delete containers and their bit strings whether a target row associated with the select operation is set for deletion before processing the select operation.

14. The method of claim 13, wherein accessing further includes using a target row identifier for the target row to access a particular single delete container and scanning the corresponding bit string of that particular single delete container for a particular bit representing the target row within the bit string, when the particular bit is set the target row is set for deletion.

15. The method of claim 14, wherein using further includes processing the select operation when the particular bit is unset.

16. The method of claim 15, wherein processing further includes setting the particular bit within the particular bit string to indicate that the target row is to be set for deletion as part of other operations associated with processing the select operation.

17. A processor-implemented system, comprising:

a column partition controller programmed within a non-transitory computer-readable medium and to execute on a processor; and

a control column partition residing within and accessible from a non-transitory computer-readable medium;

the column partition controller configured to represent deletions for multiple rows of a table within a single delete container of the control column partition, each row within the single delete container identified via a single bit flag in a bit string and each single bit flag is set when the row to which it relates is deleted.

18. The system of claim 17, wherein the column partition controller is configured to split a single delete container when the delete container reaches a predefined maximum size into two delete containers, each delete container representing a different set of the multiple rows.

19. The system of claim 18, wherein the column partition controller is configured to dynamically create a single delete container when a first row is initially set for deletion.

20. The system of claim 17, wherein the column partition controller is configured to dynamically grow the bit string when additional bits set for rows are added to a single delete container.

21. The system of claim 17, wherein the column partition controller is configured to pad the bit string with unset bits when intermediate rows of the multiple rows are not set for deletion.