Buffered data-loading in column-partitioned database tables
A system and method for loading data into column-partitioned database tables. The system and method incorporate a mechanism for buffering data extracted from the rows of a source table in column-oriented fashion within an in-buffer memory, enabling an efficient bulk-write of large arrays of values from the buffer into column-partitioned database tables. The system and method may also include optimizations for grouping columns according to data types and altering the order in which columns are inserted into the database tables.
1. In a database system including a processor and data storage devices, said database system employing a column-oriented storage model whereby data is saved within column-partitioned database tables, a method of loading data into a column-partitioned database table, the method comprising:
reading, by a processor, rows from a row-oriented source table,
extracting, by said processor, individual column data values from said rows,
writing, by said processor, said column data values in a column-first order to a buffer memory;
allocating, by said processor, said column data values contained within said buffer memory to column containers;
determining, by said processor, an optimized sequence in which said column containers are to be written to said column-partitioned database table; and
writing, by said processor, said column containers to said column-partitioned database table in accordance with said optimized sequence.
2. The method of claim 1 , wherein said optimized sequence in which said column containers are written to said column-partitioned database table is determined by evaluating column types associated with said column data values written to said buffer memory.
3. The method of claim 2 , wherein said column types include fixed-length columns and variable-length columns.
4. The method of claim 2 , wherein said column types include compressed data columns and uncompressed data columns.
5. The method of claim 1 , wherein said column containers are written to said column-partitioned database table when said buffer memory attains a full state.
6. In a database system including a processor and data storage devices, said database system employing a column-oriented storage model whereby data is saved within column-partitioned database tables, a method of loading data into a column-partitioned database table, the method comprising:
reading, by a processor, rows from a row-oriented source table;
identifying, by said processor, individual column data values from said rows;
writing, by said processor, references to said column data values to a buffer memory, said references written to said buffer memory in a column-first order;
using said references to said column data values, obtaining, by said processor, said column data values from said row-oriented source table;
allocating, by said processor, said column data values to column containers;
determining, by said processor, an optimized sequence in which said column containers are to be written to said column-partitioned database table; and
writing said column containers to said column partitioned database table within a data storage device in accordance with said optimized sequence.
7. The method of claim 6 , wherein said optimized sequence in which said column containers are written to said column-partitioned database table is determined by evaluating column types associated with said references to column data values written to said buffer memory.
8. The method of claim 6 , wherein:
said step of identifying individual column data values from said rows includes identifying column data values which are short values; and
said step of writing references to said column data values to a buffer memory includes, for said short values, writing the column data values associated with the short values to said buffer memory.
9. A database system including:
a storage device configured to store a plurality of database tables, wherein at least a portion of the database tables are column-partitioned database tables;
a processor in communication with the storage device, said processor executing a data loading procedure for:
reading rows from a row-oriented source table;
extracting individual column data values from said rows;
writing said column data values in a column-first order to a buffer memory;
allocating said column data values contained within said buffer memory to column containers;
determining an optimized sequence in which said column containers are to be written to said column-partitioned database table; and
writing said column containers to said column-partitioned database table in accordance with said optimized sequence.
10. The database system of claim 9 , wherein said optimized sequence in which said column containers are written to said column-partitioned database table is determined by evaluating column types associated with said column data values written to said buffer memory.
11. The database system of claim 10 , wherein said column types include fixed-length columns and variable-length columns.
12. The database system of claim 10 , wherein said column types include compressed data columns and uncompressed data columns.
13. The database system of claim 9 , wherein said column containers are written to said column-partitioned database table when said buffer memory attains a full state.
14. A database system including:
a storage device configured to store a plurality of database tables, wherein at least a portion of the database tables are column-partitioned database tables;
a processor in communication with the storage device, said processor executing a data loading procedure to:
read rows from a row-oriented source table;
identify individual column data values from said rows;
write references to said column data values to a buffer memory, said references written to said buffer memory in a column-first order;
using said references to said column data values, obtain said column data values from said row-oriented source table;
allocate said column data values to column containers;
determine an optimized sequence in which said column containers are to be written to said column-partitioned database table; and
write said column containers to said column partitioned database table within a data storage device in accordance to said optimized sequence.
15. The database system of claim 14 , wherein said optimized sequence in which said column containers are written to said column-partitioned database table is determined by evaluating column types associated with said references to column data values written to said buffer memory.
16. The database system of claim 14 , wherein:
said identifying individual column data values from said rows includes identifying column data values which are short values; and
writing references to said column data values to a buffer memory includes, for said short values, writing the column data values associated with the short values to said buffer memory.