Combined row and columnar storage for in-memory databases for OLTP and analytics workloads
Columns of a table are stored in either row-major format or column-major format in an in-memory DBMS. For a given table, one set of columns is stored in column-major format; another set of columns for a table are stored in row-major format. This way of storing columns of a table is referred to herein as dual-major format. In addition, a row in a dual-major table is updated “in-place”, that is, updates are made directly to column-major columns without creating an interim row-major form of the column-major columns of the row. Users may submit database definition language (“DDL”) commands that declare the row-major columns and column-major columns of a table.
1. A method, said method comprising steps of:
a database server receiving a data definition language (DDL) statement that declares a table in a database and table columns of said table;
wherein said database includes a database dictionary that defines database objects of said database;
wherein said DDL statement declares:
one or more column-major columns of said table columns, and
row-major columns of said table columns;
in response to receiving said DDL statement, creating said table by at least generating metadata in said database dictionary that defines:
said table,
said one or more column-major columns of said table, and
said row-major columns of said table;
storing rows in said table, wherein storing rows in said table comprises storing said one or more column-major columns of said rows in column-major format and storing said row-major columns of said rows in row-major format;
receiving a data manipulation language (DML) statement requesting to update a column value of a column-major column of said one or more column-major columns; and
in response to receiving said DML statement, updating in said database said column-major column in-place to a new value.
2. The method of claim 1 ,
wherein storing comprises:
storing said row-major columns in a row partition comprising row pages;
storing said one or more column-major columns in one or more column partitions, each column partition of said one or more column partitions comprising column pages;
wherein the method further comprises:
executing a database transaction, wherein executing said database transaction comprises said updating;
committing said database transaction; and
wherein said updating comprises storing said new value in a column page of said one or more column partitions before committing said database transaction.
3. The method of claim 1 ,
wherein storing comprises:
storing said row-major columns in a row partition comprising row pages, each row page of said row pages comprising a plurality of row slots;
storing said one or more column-major columns in one or more column partitions, each column partition of said one or more column partitions comprising column pages, each column page of said column pages comprising column slots;
wherein based on a functional mapping, a row slot in said row partition has a one-to-one association with a column slot in each column partition of said one or more column partitions.
4. The method of claim 3 , the steps further comprising:
determining a location of a row in said row partition; and
based on said functional mapping and said location of said row in said row partition, for each column partition of said one or more column partitions, determining a location of a column value of said row in said each column partition.
5. The method of claim 3 , the method further including:
determining a location of a row in a column partition of said one or more column partitions; and
based on said functional mapping and said location of said row in said column partition, determining a location of said row in said row partition.
6. The method of claim 3 , wherein at least one column partition of said one or more column partitions is compressed using run-length encoding.
7. The method of claim 6 , where the steps further comprise: based on a location of a row in said row partition, traversing an index that indexes said at least one column partition to determine a location of an encoded run in said at least one column partition corresponding to a column of said row.
8. The method of claim 7 , the steps further comprising:
determining a location of a column value in said at least one column partition; and
based on said index and said location of said column, determining a location of a row holding said column value.
9. The method of claim 3 , wherein for one or more column pages of said one or more column partitions, each column page of said one or more column pages is associated with a Bloom filter derived from column values in said each column page.
10. One or more non-transitory computer-readable media storing sequences of instructions, which, when executed by one or more processors, cause:
a database server receiving a data definition language (DDL) statement that declares a table in a database and table columns of said table;
wherein said database includes a database dictionary that defines database objects of said database;
wherein said DDL statement declares:
one or more column-major columns of said table columns, and
row-major columns of said table columns;
in response to receiving said DDL statement, creating said table by at least generating metadata in said database dictionary that defines:
said table,
said one or more column-major columns of said table, and
said row-major columns of said table;
storing rows in said table, wherein storing rows in said table comprises storing said one or more column-major columns of said rows in column-major format and storing said row-major columns of said rows in row-major format;
receiving a DML statement requesting to update a column value of a column-major column of said one or more column-major columns; and
in response to receiving said DML statement, updating in said database said column-major column in-place to a new value.
11. The one or more non-transitory computer-readable media of claim 10 ,
wherein storing comprises:
storing said row-major columns in a row partition comprising row pages;
storing said one or more column-major columns in one or more column partitions, each column partition of said one or more column partitions comprising column pages;
wherein the sequences of instructions include instructions, that when executed by said one or more processors, cause:
executing a database transaction, wherein executing said database transaction comprises said updating;
committing said database transaction; and
wherein said updating comprises storing said new value in a column page of said one or more column partitions before committing said database transaction.
12. The one or more non-transitory computer-readable media of claim 10 ,
wherein storing comprises:
storing said row-major columns in a row partition comprising row pages, each row page of said row pages comprising a plurality of row slots;
storing said one or more column-major columns in one or more column partitions, each column partition of said one or more column partitions comprising column pages, each column page of said column pages comprising column slots;
wherein based on a functional mapping, a row slot in said row partition has a one-to-one association with a column slot in each column partition of said one or more column partitions.
13. The one or more non-transitory computer-readable media of claim 12 , the sequences of instructions including instructions, that when executed by said one or more processors, cause:
determining a location of a row in said row partition; and
based on said functional mapping and said location of said row in said row partition, for each column partition of said one or more column partitions, determining a location of a column value of said row in said each column partition.
14. The one or more non-transitory computer-readable media of claim 12 , the sequences of instructions including instructions, that when executed by said one or more processors, cause:
determining a location of a row in a column partition of said one or more column partitions; and
based on said functional mapping and said location of said row in said column partition, determining a location of said row in said row partition.
15. The one or more non-transitory computer-readable media of claim 12 , wherein at least one column partition of said one or more column partitions is compressed using run-length encoding.
16. The one or more non-transitory computer-readable media of claim 15 , the sequences of instructions including instructions, that when executed by said one or more processors, cause: based on a location of a row in said row partition, traversing an index that indexes said at least one column partition to determine a location of an encoded run in said at least one column partition corresponding to a column of said row.
17. The one or more non-transitory computer-readable media of claim 16 , the sequences of instructions including instructions, that when executed by said one or more processors, cause:
determining a location of a column value in said at least one column partition; and
based on said index and said location of said column, determining a location of a row holding said column value.
18. The one or more non-transitory computer-readable media of claim 12 , wherein for one or more column pages of said one or more column partitions, each column page of said one or more column pages is associated with a Bloom filter derived from column values in said each column page.