Column smart mechanism for column based database

• US 10,108,668 B2
• Filed: 12/28/2012
• Issued: 10/23/2018
• Est. Priority Date: 12/14/2012
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP SE
• Inventors: Liu, Yingqiao, Zhu, Lin, Jiang, Huayang
• Primary Examiner(s): Syed, Farhan M
• Application Number:
  US13/729,632
• Publication Number:
  US 20140172776A1
• Time in Patent Office: 2,125 Days
• CPC Class Codes: G06F 16/221 Column-oriented storage; Management thereof
• CPC Class Codes: G06F 16/24552 Database cache management

Abstract

Embodiments of the present disclosure may provide a system and method for processing an online transactional processing (OLTP) transaction on a column-based storage of a database. The method may include receiving a request of the OLTP transaction to access data on the column-based storage. A determination may be made whether a cache associated with the database includes column information for the OLTP transaction. If the cache includes the column information for the OLTP transaction, the method may include processing the OLTP transaction with the column information in the cache. If the cache does not include the column information for the OLTP transaction, the method may include selecting columns from the column-based storage of the database.

Claims

1. A method comprising:

receiving a request for an online transactional processing (OLTP) transaction through an input device to access data in a column-based storage of a database contained in a data storage device, the OLTP transaction including a query to access a first set of columns in the column-based storage;

in the event a cache includes column information for the OLTP transaction, entering a learned mode;

responsive to entering the learned mode;

determining a second set of columns that are needed by the OLTP transaction, the determining including comparing the OLTP transaction to a plurality of previously processed OLTP transactions stored in the cache;

optimizing the query of the OLTP transaction to access the second set of columns in the cache, wherein the second set of columns is smaller than the first set of columns; and

processing the OLTP transaction with the column information in the cache based on the optimized query;

in the event the cache does not include the column information for the OLTP transaction, entering a learning mode and selecting columns from the column-based storage of the database, caching column names or indices relating to the selected columns, and entering the learned mode and processing the OLTP transaction with the column information in the cache,

wherein the selection of columns is based on the OLTP transaction and includes only columns necessary for the OLTP transaction; and

outputting at least a portion of a result of the OLTP transaction to an output device.

2. The method of claim 1, wherein the cache is associated with the database and the method further comprises determining whether the cache includes the column information for the OLTP transaction.

3. The method of claim 1, further comprising receiving a request for an online analytical processing (OLAP) transaction to access data in the column-based storage of the database.

4. The method of claim 1, wherein the selected columns from the column-based storage of the database includes at least one column needed with the OLTP transaction and at least one column not needed for the transaction.

5. The method of claim 1, wherein the cache includes column information from at least one previous request.

6. The method of claim 1, further comprises caching column names or indices of the columns selected from the column-based storage of the database.

7. The method of claim 2, wherein determining whether the cache associated with the database includes the column information for the OLTP transaction includes comparing the OLTP transaction with previously processed OLTP transactions.

14. The method of claim 1, wherein the data structure contains columns not needed for the current transaction, wherein such columns are filled with at least one of random, fake, or default values.

15. The method of claim 1, wherein the request comprises a plurality of transactions, wherein each transaction can be processed individually or as a batch of transactions.

16. The method of claim 1, wherein the database is a relational database, wherein the relational database has a relational database engine.

17. The method of claim 1, wherein the transaction accesses data in a plurality of databases.

18. The method of claim 17, wherein a common cache is utilized for the plurality of databases.

19. The method of claim 1, wherein the database includes the cache.

8. A non-transitory computer readable storage medium storing one or more programs configured to be executed by a processor, the one or more programs comprising instructions for:

receiving a request for an online transactional processing (OLTP) transaction to access data in a column-based storage of a database, the OLTP transaction including a query to access a first set of columns in the column-based storage;

determining whether a cache associated with the database includes column information for the OLTP transaction;

in the event the cache includes the column information for the OLTP transaction, entering a learned mode;

responsive to entering the learned mode;

determining a second set of columns that are needed by the OLTP transaction, the determining including comparing the OLTP transaction to a plurality of previously processed OLTP transactions stored in the cache;

optimizing the query of the OLTP transaction to access the second set of columns in the cache, wherein the second set of columns is smaller than the first set of columns; and

processing the OLTP transaction with the column information in the cache based on the optimized query;

in the event the cache does not include the column information for the OLTP transaction, entering a learning mode and selecting columns from the column-based storage of the database, caching column names or indices relating to the selected columns, and entering the learned mode and processing the OLTP transaction with the column information in the cache,

wherein the selection of columns is based on the OLTP transaction and includes only columns necessary for the OLTP transaction.

9. The computer readable storage medium of claim 8, further comprising instructions for receiving a request for an online analytical processing (OLAP) transaction to access data in the column-based storage of the database.

10. The computer readable storage medium of claim 8, wherein the selected columns from the column-based storage of the database includes at least one column needed with the OLTP transaction and at least one column not needed for the transaction.

11. The computer readable storage medium of claim 8, wherein the cache includes column information from at least one previous request.

12. The computer readable storage medium of claim 8, wherein determining whether the cache associated with the database includes the column information for the OLTP transaction includes comparing the OLTP transaction with previously processed OLTP transactions.

13. A system, comprising:

one or more processors; and

memory storing one or more programs for execution by the one or more processors, the one or more programs including instructions for;

receiving a request for an online analytical processing (OLAP) transaction to access data in a column-based storage of a database;

receiving a request for an online transactional processing (OLTP) transaction, the OLTP transaction including a query to access a first set of columns in the column-based storage;

determining whether a cache associated with the database includes column information for the OLTP transaction;

in the event the cache includes the column information for the OLTP transaction, entering a learned mode;

responsive to entering the learned mode,

determining a second set of columns that are needed by the OLTP transaction, the determining including comparing the OLTP transaction to a plurality of previously processed OLTP transactions stored in the cache;

optimizing the query of the OLTP transaction to access the second set of columns in the cache, wherein the second set of columns is smaller than the first set of columns; and

processing the OLTP transaction with the column information in the cache based on the optimized query;

in the event the cache does not include the column information for the OLTP transaction, entering a learning mode and selecting columns from the column-based storage of the database, caching column names or indices of the columns selected from the column-based storage of the database, and entering the learned mode and processing the OLTP transaction with the column information in the cache,

wherein the selection of columns is based on the OLTP transaction and includes only columns necessary for the OLTP transaction.