Generally, you can use row-based storage in your database to adapt to high-frequency short queries in online transaction processing (OLTP) business and use columnar storage to adapt to large-scale data scans and analytics in online analytical processing (OLAP) business. When your business involves both frequent short queries and complex analytical queries involving a large amount of data, you can deploy two database systems, synchronize data between the two systems through extract-transform-load (ETL), change data capture (CDC), or stream computing, and use row-based storage and columnar storage in the two systems to meet TP and AP query requirements, respectively. You must maintain the two systems, tolerate data delays between them, and manually select a system for each query.
OceanBase Database supports hybrid rowstore-columnstore tables. It meets your TP and AP query requirements with one system without data delays. In addition, the optimizer can automatically select row-based storage or columnar storage. The syntax for creating a hybrid rowstore-columnstore table is as follows:
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, c3 INT, c4 VARCHAR(100)) WITH COLUMN GROUP (ALL COLUMNS, EACH COLUMN)
The preceding statement creates a hybrid rowstore-columnstore table named t1.
A typical TP SQL query statement is as follows:
UPDATE t1 SET c2 = 3, c4 = 'abc' WHERE c1 = 20445883;
The SQL optimizer chooses to read data from the row-based storage.
A typical AP SQL query statement is as follows:
SELECT SUM(c3) FROM t1 GROUP BY c2;
The SQL optimizer chooses to read data from the columnar storage.