Try out operational OLAP|V5.0.1|OceanBase Database|docs|OceanBase Documentations

Try out operational OLAP

Last Updated：2026-09-04 11:28:23 Updated

OceanBase Database can handle hybrid workloads. Because OceanBase Database adopts a distributed architecture based on peer nodes, it can not only handle high-concurrency and scalable online transaction processing (OLTP) tasks, but also perform parallel computing for online analytical processing (OLAP) tasks based on the massively parallel processing (MPP) architecture in the same data engine, without the need to maintain two sets of data.

In OceanBase Database, you can not only directly perform parallel analysis on a large amount of online business data, but also use the parallel DML (PDML) capability to execute large transactions that write data in batches in a concurrent, fast, and secure manner. In addition, all these are achieved under the premise of strictly ensuring transaction consistency.

Let's manually run the Transaction Processing Performance Council Benchmark H (TPC-H) benchmark test to demonstrate the characteristics and usage of OceanBase Database in operational OLAP scenarios. TPC-H is a commonly used benchmark for decision support systems. It evaluates the analysis and decision support capabilities of a database system through a series of complex queries executed on a large amount of data. For more information, see the official website of TPC.

Note

On May 20, 2021, OceanBase Database set a new TPC-H world record with a result of 15.26 million QphH. It is also the only database that has set new records in both TPC-C and TPC-H, demonstrating its capability to handle both online transaction processing and real-time analytics scenarios. For more information, see TPC-H Result.

Manually run the TPC-H benchmark test

The following describes how to manually run the TPC-H benchmark test step by step based on the official TPC-H tools provided by TPC. Manual testing can help you better learn about and understand OceanBase Database, especially the settings of some parameters.

Step 1: Create a test tenant

Note

The OceanBase cluster environment for this test is deployed in the 1:1:1 mode.

Execute the following statements in the system tenant (the sys tenant) to create a test tenant:

Create a resource unit named mysql_box.

CREATE RESOURCE UNIT mysql_box
   MAX_CPU 28,
   MEMORY_SIZE '200G',
   MIN_IOPS 200000,
   MAX_IOPS 12800000,
   LOG_DISK_SIZE '300G';

Create a resource pool named mysql_pool.

CREATE RESOURCE POOL mysql_pool
   UNIT = 'mysql_box',
   UNIT_NUM = 1,
   ZONE_LIST = ('z1','z2','z3');

Create a MySQL-mode tenant named mysql_tenant.

CREATE TENANT mysql_tenant
   RESOURCE_POOL_LIST = ('mysql_pool'),
   PRIMARY_ZONE = RANDOM,
   LOCALITY = 'F@z1,F@z2,F@z3'
   SET VARIABLES ob_compatibility_mode='mysql', ob_tcp_invited_nodes='%', secure_file_priv = "/";

Step 2: Optimize the test environment

Optimize OceanBase Database parameters.

Execute the following statements in the system tenant (the sys tenant) to configure the relevant parameters:

ALTER SYSTEM FLUSH PLAN CACHE GLOBAL;
ALTER SYSTEM SET enable_sql_audit = false;
SELECT sleep(5);
ALTER SYSTEM SET enable_perf_event = false;
ALTER SYSTEM SET syslog_level = 'PERF';
ALTER SYSTEM SET enable_record_trace_log = false;
ALTER SYSTEM SET data_storage_warning_tolerance_time = '300s';
ALTER SYSTEM SET _data_storage_io_timeout = '600s';
ALTER SYSTEM SET trace_log_slow_query_watermark = '7d';
ALTER SYSTEM SET large_query_threshold = '0ms';
ALTER SYSTEM SET enable_syslog_recycle = 1;
ALTER SYSTEM SET max_syslog_file_count = 300;

Optimize tenant parameters.

Execute the following statements in the test tenant (user tenant) to configure the relevant parameters:

SET GLOBAL NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SET GLOBAL NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
SET GLOBAL NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF TZR TZD';

SET GLOBAL ob_query_timeout = 10800000000;
SET GLOBAL ob_trx_timeout = 10000000000;

SET GLOBAL ob_sql_work_area_percentage = 50;
ALTER SYSTEM SET default_table_store_format = 'column' ;
ALTER SYSTEM SET ob_enable_batched_multi_statement = 'true';
ALTER SYSTEM SET _io_read_batch_size = '128k';
ALTER SYSTEM SET _io_read_redundant_limit_percentage = 50;
SET GLOBAL parallel_degree_policy = AUTO;
SET GLOBAL parallel_servers_target = 10000;

SET GLOBAL collation_connection = utf8mb4_bin;
SET GLOBAL collation_database = utf8mb4_bin;
SET GLOBAL collation_server = utf8mb4_bin;

SET GLOBAL autocommit = 1;

ALTER SYSTEM SET ob_enable_batched_multi_statement = 'true';

Step 3: Install the TPC-H tools

Download the TPC-H tools. For more information, see the TPC-H Tools Download page.

Unzip the downloaded package and go to the TPC-H directory.

[wieck@localhost ~] $ unzip 7e965ead-8844-4efa-a275-34e35f8ab89b-tpc-h-tool.zip
[wieck@localhost ~] $ cd TPC-H_Tools_v3.0.0

Copy the Makefile.suite file.

[wieck@localhost TPC-H_Tools_v3.0.0] $ cd dbgen/
[wieck@localhost dbgen] $ cp Makefile.suite Makefile

Modify the definitions of parameters such as CC, DATABASE, MACHINE, and WORKLOAD in the Makefile file.

[wieck@localhost dbgen] $ vim Makefile

CC      = gcc
# Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)
#                                  SQLSERVER, SYBASE, ORACLE, VECTORWISE
# Current values for MACHINE are:  ATT, DOS, HP, IBM, ICL, MVS,
#                                  SGI, SUN, U2200, VMS, LINUX, WIN32
# Current values for WORKLOAD are:  TPCH
DATABASE= MYSQL
MACHINE = LINUX
WORKLOAD = TPCH

Modify the tpcd.h file and add new macro definitions.

[wieck@localhost dbgen] $ vim tpcd.h

#ifdef MYSQL
#define GEN_QUERY_PLAN ""
#define START_TRAN "START TRANSACTION"
#define END_TRAN "COMMIT"
#define SET_OUTPUT ""
#define SET_ROWCOUNT "limit %d;\n"
#define SET_DBASE "use %s;\n"
#endif

Compile the files.

make

The result is as follows:

gcc  -g -DDBNAME=\"dss\" -DLINUX -DMYSQL -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o build.o build.c
gcc  -g -DDBNAME=\"dss\" -DLINUX -DMYSQL -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o driver.o driver.c
gcc  -g -DDBNAME=\"dss\" -DLINUX -DMYSQL -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o bm_utils.o bm_utils.c
gcc  -g -DDBNAME=\"dss\" -DLINUX -DMYSQL -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o rnd.o rnd.c
gcc  -g -DDBNAME=\"dss\" -DLINUX -DMYSQL -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o print.o print.c
gcc  -g -DDBNAME=\"dss\" -DLINUX -DMYSQL -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o load_stub.o load_stub.c
gcc  -g -DDBNAME=\"dss\" -DLINUX -DMYSQL -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o bcd2.o bcd2.c
gcc  -g -DDBNAME=\"dss\" -DLINUX -DMYSQL -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o speed_seed.o speed_seed.c
gcc  -g -DDBNAME=\"dss\" -DLINUX -DMYSQL -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o text.o text.c
gcc  -g -DDBNAME=\"dss\" -DLINUX -DMYSQL -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o permute.o permute.c
gcc  -g -DDBNAME=\"dss\" -DLINUX -DMYSQL -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o rng64.o rng64.c
gcc  -g -DDBNAME=\"dss\" -DLINUX -DMYSQL -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64  -O -o dbgen build.o driver.o bm_utils.o rnd.o print.o load_stub.o bcd2.o speed_seed.o text.o permute.o rng64.o -lm
gcc  -g -DDBNAME=\"dss\" -DLINUX -DMYSQL -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o qgen.o qgen.c
gcc  -g -DDBNAME=\"dss\" -DLINUX -DMYSQL -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64    -c -o varsub.o varsub.c
gcc  -g -DDBNAME=\"dss\" -DLINUX -DMYSQL -DTPCH -DRNG_TEST -D_FILE_OFFSET_BITS=64  -O -o qgen build.o bm_utils.o qgen.o rnd.o varsub.o text.o bcd2.o permute.o speed_seed.o rng64.o -lm

This generates the dbgen file for subsequent data generation, and the qgen and dists.dss files for SQL generation.

Step 4: Generate data

You can generate 10 GB, 100 GB, or 1 TB of TPC-H data based on your actual environment. This topic uses the generation of 100 GB of data as an example.

./dbgen -s 100
mkdir tpch100
mv *.tbl tpch100

To generate 1 TB of data with multiple threads, note that OceanBase Database supports bypass import, so you can import data from multiple files into tables at the same time:

#!/bin/bash  
  
SCALE_FACTOR=1000  
CHUNK_COUNT=20  
for ((i=1; i<=CHUNK_COUNT; i++))  
do   
   CMD="./dbgen -s ${SCALE_FACTOR} -C ${CHUNK_COUNT} -S ${i} -vf"   
   $CMD &  
done  
wait  
echo "All data generation tasks completed."

Step 5: Generate SQL query statements

Note

You can generate the SQL query statements by following the steps described in this section and then adjust them, or you can directly use the SQL query statements provided on GitHub. If you use the SQL query statements provided on GitHub, you must change the value of cpu_num in the SQL statements to the actual number of concurrent threads.

Use the built-in tools of TPC-H to generate the SQL query statements. The steps are as follows:

Copy the dbgen/qgen and dbgen/dists.dss files to the mysql_sql directory.

In the mysql_sql directory, create a gen.sh script to generate the SQL query statements.

vim gen.sh

#!/usr/bin/bash
for i in {1..22}
do  
./qgen -d $i -s 100 > db"$i".sql
done

Modify the SQL query statements based on the actual number of concurrent threads.

In the sys tenant, run the following command to view the total number of available CPU cores of the tenant:

select sum(max_cpu) from DBA_OB_UNITS;

Take Q1 as an example. The modified SQL statement is as follows:

SELECT /*+    parallel(96) */   ---Add parallel execution
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
FROM
lineitem
WHERE
l_shipdate <= date '1998-12-01' - interval '90' day 
GROUP BY
l_returnflag,
l_linestatus
ORDER BY
l_returnflag,
l_linestatus;

Step 6: Create tables

For 100 GB of data, create a table schema file named create_tpch_mysql_table_part.ddl.

drop tablegroup IF EXISTS tpch_tg_SF_TPC_USER_lineitem_order_group;
drop tablegroup IF EXISTS  tpch_tg_SF_TPC_USER_partsupp_part;
create tablegroup tpch_tg_SF_TPC_USER_lineitem_order_group binding true partition by key 1 partitions 256;
create tablegroup tpch_tg_SF_TPC_USER_partsupp_part binding true partition by key 1 partitions 256;


DROP TABLE IF EXISTS LINEITEM;
CREATE TABLE lineitem (
   l_orderkey int(11) NOT NULL,
   l_partkey int(11) NOT NULL,
   l_suppkey int(11) NOT NULL,
   l_linenumber int(11) NOT NULL,
   l_quantity decimal(15,2) NOT NULL,
   l_extendedprice decimal(15,2) NOT NULL,
   l_discount decimal(15,2) NOT NULL,
   l_tax decimal(15,2) NOT NULL,
   l_returnflag char(1) DEFAULT NULL,
   l_linestatus char(1) DEFAULT NULL,
   l_shipdate date NOT NULL,
   l_commitdate date DEFAULT NULL,
   l_receiptdate date DEFAULT NULL,
   l_shipinstruct varchar(25) DEFAULT NULL,
   l_shipmode varchar(10) DEFAULT NULL,
   l_comment varchar(44) DEFAULT NULL,
primary key(l_shipdate, l_orderkey, l_linenumber)
)row_format = condensed
tablegroup = tpch_tg_SF_TPC_USER_lineitem_order_group
partition by key (l_orderkey) partitions 256 with column group(each column);
alter table lineitem CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

DROP TABLE IF EXISTS ORDERS;
CREATE TABLE orders (
   o_orderkey int(11) NOT NULL,
   o_custkey int(11) NOT NULL,
   o_orderstatus varchar(1) DEFAULT NULL,
   o_totalprice decimal(15,2) DEFAULT NULL,
   o_orderdate date NOT NULL,
   o_orderpriority varchar(15) DEFAULT NULL,
   o_clerk varchar(15) DEFAULT NULL,
   o_shippriority int(11) DEFAULT NULL,
   o_comment varchar(79) DEFAULT NULL,
PRIMARY KEY (o_orderkey, o_orderdate)
) row_format = condensed
tablegroup = tpch_tg_SF_TPC_USER_lineitem_order_group
partition by key(o_orderkey) partitions 256 with column group(each column);
alter table orders CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

DROP TABLE IF EXISTS PARTSUPP;
CREATE TABLE partsupp (
   ps_partkey int(11) NOT NULL,
   ps_suppkey int(11) NOT NULL,
   ps_availqty int(11) DEFAULT NULL,
   ps_supplycost decimal(15,2) DEFAULT NULL,
   ps_comment varchar(199) DEFAULT NULL,
   PRIMARY KEY (ps_partkey, ps_suppkey)) row_format = condensed
tablegroup tpch_tg_SF_TPC_USER_partsupp_part
partition by key(ps_partkey) partitions 256 with column group(each column);
alter table partsupp CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

DROP TABLE IF EXISTS PART;
CREATE TABLE part (
p_partkey int(11) NOT NULL,
p_name varchar(55) DEFAULT NULL,
p_mfgr varchar(25) DEFAULT NULL,
p_brand varchar(10) DEFAULT NULL,
p_type varchar(25) DEFAULT NULL,
p_size int(11) DEFAULT NULL,
p_container varchar(10) DEFAULT NULL,
p_retailprice decimal(12,2) DEFAULT NULL,
p_comment varchar(23) DEFAULT NULL,
PRIMARY KEY (p_partkey)) row_format = condensed
tablegroup tpch_tg_SF_TPC_USER_partsupp_part
partition by key(p_partkey) partitions 256 with column group(each column);
alter table part CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

DROP TABLE IF EXISTS CUSTOMER;
CREATE TABLE customer (
c_custkey int(11) NOT NULL,
c_name varchar(25) DEFAULT NULL,
c_address varchar(40) DEFAULT NULL,
c_nationkey int(11) DEFAULT NULL,
c_phone varchar(15) DEFAULT NULL,
c_acctbal decimal(15,2) DEFAULT NULL,
c_mktsegment char(10) DEFAULT NULL,
c_comment varchar(117) DEFAULT NULL,
PRIMARY KEY (c_custkey)) row_format = condensed
partition by key(c_custkey) partitions 256 with column group(each column);
alter table customer CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

DROP TABLE IF EXISTS SUPPLIER;
CREATE TABLE supplier (
s_suppkey int(11) NOT NULL,
s_name varchar(25) DEFAULT NULL,
s_address varchar(40) DEFAULT NULL,
s_nationkey int(11) DEFAULT NULL,
s_phone varchar(15) DEFAULT NULL,
s_acctbal decimal(15,2) DEFAULT NULL,
s_comment varchar(101) DEFAULT NULL,
PRIMARY KEY (s_suppkey)
) row_format = condensed partition by key(s_suppkey) partitions 256 with column group(each column);
alter table supplier CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

DROP TABLE IF EXISTS NATION;
CREATE TABLE nation (
n_nationkey int(11) NOT NULL,
n_name varchar(25) DEFAULT NULL,
n_regionkey int(11) DEFAULT NULL,
n_comment varchar(152) DEFAULT NULL,
PRIMARY KEY (n_nationkey)
) row_format = condensed with column group(each column);
alter table nation CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

DROP TABLE IF EXISTS REGION;
CREATE TABLE region (
r_regionkey int(11) NOT NULL,
r_name varchar(25) DEFAULT NULL,
r_comment varchar(152) DEFAULT NULL,
PRIMARY KEY (r_regionkey)
) row_format = condensed with column group(each column);
alter table region CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

CREATE VIEW revenue0 AS 
SELECT l_suppkey as supplier_no,
         SUM(l_extendedprice * ( 1 - l_discount )) as total_revenue
            FROM   lineitem
            WHERE  l_shipdate >= DATE '1996-01-01'
                     AND l_shipdate < DATE '1996-04-01'
            GROUP  BY l_suppkey;

For 1 TB of data, create a table schema file named create_tpch_mysql_table_part_1000G.ddl.

drop tablegroup IF EXISTS tpch_tg_SF_TPC_USER_lineitem_order_group_1000;
drop tablegroup IF EXISTS  tpch_tg_SF_TPC_USER_partsupp_part_1000;
create tablegroup tpch_tg_SF_TPC_USER_lineitem_order_group_1000 binding true partition by key 1 partitions 256;
create tablegroup tpch_tg_SF_TPC_USER_partsupp_part_1000 binding true partition by key 1 partitions 256;


DROP TABLE IF EXISTS LINEITEM;
CREATE TABLE lineitem (
   l_orderkey bigint NOT NULL,
   l_partkey int(32) NOT NULL,
   l_suppkey int(32) NOT NULL,
   l_linenumber int(32) NOT NULL,
   l_quantity decimal(32,2) NOT NULL,
   l_extendedprice decimal(32,2) NOT NULL,
   l_discount decimal(15,2) NOT NULL,
   l_tax decimal(15,2) NOT NULL,
   l_returnflag varchar(64) DEFAULT NULL,
   l_linestatus varchar(64) DEFAULT NULL,
   l_shipdate date NOT NULL,
   l_commitdate date DEFAULT NULL,
   l_receiptdate date DEFAULT NULL,
   l_shipinstruct varchar(64) DEFAULT NULL,
   l_shipmode varchar(64) DEFAULT NULL,
   l_comment varchar(64) DEFAULT NULL,
primary key(l_shipdate, l_orderkey, l_linenumber)
)row_format = condensed
tablegroup = tpch_tg_SF_TPC_USER_lineitem_order_group_1000
partition by key (l_orderkey) partitions 256 with column group(each column);
alter table lineitem CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

DROP TABLE IF EXISTS ORDERS;
CREATE TABLE orders (
   o_orderkey bigint NOT NULL,
   o_custkey int(32) NOT NULL,
   o_orderstatus varchar(64) DEFAULT NULL,
   o_totalprice decimal(15,2) DEFAULT NULL,
   o_orderdate date NOT NULL,
   o_orderpriority varchar(15) DEFAULT NULL,
   o_clerk varchar(15) DEFAULT NULL,
   o_shippriority int(32) DEFAULT NULL,
   o_comment varchar(128) DEFAULT NULL,
PRIMARY KEY (o_orderkey, o_orderdate)
) row_format = condensed
tablegroup = tpch_tg_SF_TPC_USER_lineitem_order_group_1000
partition by key(o_orderkey) partitions 256 with column group(each column);
alter table orders CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

DROP TABLE IF EXISTS PARTSUPP;
CREATE TABLE partsupp (
   ps_partkey int(11) NOT NULL,
   ps_suppkey int(11) NOT NULL,
   ps_availqty int(11) DEFAULT NULL,
   ps_supplycost decimal(15,2) DEFAULT NULL,
   ps_comment varchar(199) DEFAULT NULL,
   PRIMARY KEY (ps_partkey, ps_suppkey)) row_format = condensed
tablegroup tpch_tg_SF_TPC_USER_partsupp_part_1000
partition by key(ps_partkey) partitions 256 with column group(each column);
alter table partsupp CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

DROP TABLE IF EXISTS PART;
CREATE TABLE part (
p_partkey int(11) NOT NULL,
p_name varchar(55) DEFAULT NULL,
p_mfgr varchar(25) DEFAULT NULL,
p_brand varchar(10) DEFAULT NULL,
p_type varchar(25) DEFAULT NULL,
p_size int(11) DEFAULT NULL,
p_container varchar(10) DEFAULT NULL,
p_retailprice decimal(12,2) DEFAULT NULL,
p_comment varchar(23) DEFAULT NULL,
PRIMARY KEY (p_partkey)) row_format = condensed
tablegroup tpch_tg_SF_TPC_USER_partsupp_part_1000
partition by key(p_partkey) partitions 256 with column group(each column);
alter table part CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

DROP TABLE IF EXISTS CUSTOMER;
CREATE TABLE customer (
c_custkey int(11) NOT NULL,
c_name varchar(25) DEFAULT NULL,
c_address varchar(40) DEFAULT NULL,
c_nationkey int(11) DEFAULT NULL,
c_phone varchar(15) DEFAULT NULL,
c_acctbal decimal(15,2) DEFAULT NULL,
c_mktsegment char(10) DEFAULT NULL,
c_comment varchar(117) DEFAULT NULL,
PRIMARY KEY (c_custkey)) row_format = condensed
partition by key(c_custkey) partitions 256 with column group(each column);
alter table customer CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

DROP TABLE IF EXISTS SUPPLIER;
CREATE TABLE supplier (
s_suppkey int(11) NOT NULL,
s_name varchar(25) DEFAULT NULL,
s_address varchar(40) DEFAULT NULL,
s_nationkey int(11) DEFAULT NULL,
s_phone varchar(15) DEFAULT NULL,
s_acctbal decimal(15,2) DEFAULT NULL,
s_comment varchar(101) DEFAULT NULL,
PRIMARY KEY (s_suppkey)
) row_format = condensed partition by key(s_suppkey) partitions 256 with column group(each column);
alter table supplier CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

DROP TABLE IF EXISTS NATION;
CREATE TABLE nation (
n_nationkey int(11) NOT NULL,
n_name varchar(25) DEFAULT NULL,
n_regionkey int(11) DEFAULT NULL,
n_comment varchar(152) DEFAULT NULL,
PRIMARY KEY (n_nationkey)
) row_format = condensed with column group(each column);
alter table nation CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

DROP TABLE IF EXISTS REGION;
CREATE TABLE region (
r_regionkey int(11) NOT NULL,
r_name varchar(25) DEFAULT NULL,
r_comment varchar(152) DEFAULT NULL,
PRIMARY KEY (r_regionkey)
) row_format = condensed with column group(each column);
alter table region CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

CREATE VIEW revenue0 AS 
SELECT l_suppkey as supplier_no,
         SUM(l_extendedprice * ( 1 - l_discount )) as total_revenue
            FROM   lineitem
            WHERE  l_shipdate >= DATE '1996-01-01'
                     AND l_shipdate < DATE '1996-04-01'
            GROUP  BY l_suppkey;

Step 7: Load data

You can write a script based on the data and SQL statements generated in the preceding steps. The following example shows how to load data:

Create the load_data.sh script for loading data.

#!/bin/bash
host='$host_ip'   # Note: enter the IP address of the server where an OBServer node, such as OBServer A, resides. We recommend that you also store the data files on this server.
port='$host_port' # The port number of OBServer A.
user='$user'      #  The username.
tenant='$tenant_name'  # The tenant name.
password='$password'   # The password.
database='$db_name'    #  The database name.
data_path='$data_file' # Note: enter the path of the data files (.tbl) generated in the data generation step on the server where an OBServer node, such as OBServer A, resides.

function load_data
{
   remote_user="$user"         # The username used to log in to the OBServer node that stores the data.
   table_name=${1}
   if [[ ${password} == "" ]];then
      obclient_conn="obclient -h${host} -P${port} -u${user} -D${database} -A -c"
   else
      obclient_conn="obclient -h${host} -P${port} -u${user} -D${database} -p${password} -A -c"
   fi
   table_list=$(ssh "${remote_user}@${host}" "ls ${data_path}/${table_name}.tbl* 2>/dev/null")  
   echo "$table_list"

   IFS=$'\n' read -d '' -r -a table_files <<< "$table_list" 
   table_files_comma_separated=$(IFS=,; echo "${table_files[*]}")
   echo "${table_files_comma_separated}"
   echo `date "+[%Y-%m-%d %H:%M:%S]"` "----------------------Importing the data files of the ${table_name} table----------------------"

   # Use bypass import to import the data. You can also change this to another import method.
   echo "load data /*+ parallel(80) direct(true,0) */ infile '${table_files_comma_separated}' into table ${table_name} fields terminated by '|';" | ${obclient_conn}

}

starttime=`date +%s%N`
for table in "nation" "region" "customer" "lineitem" "orders" "partsupp" "part" "supplier"
do
   load_data "${table}"
done
end_time=`date +%s%N`
totaltime=`echo ${end_time} ${starttime} | awk '{printf "%0.2f\n", ($1 - $2) / 1000000000}'`
echo `date "+[%Y-%m-%d %H:%M:%S]"` "load data cost ${totaltime}s"

After the data is loaded, you need to perform a major compaction and collect statistics.

Perform a major compaction.

Execute the following statement in the test tenant to perform a major compaction:
```
ALTER SYSTEM MAJOR FREEZE;
```
Check whether the major compaction is complete.

In the sys tenant, run the following command to check whether the major compaction is complete:
```
SELECT dt.TENANT_NAME, cc.FROZEN_SCN, cc.LAST_SCN
FROM oceanbase.DBA_OB_TENANTS dt, oceanbase.CDB_OB_MAJOR_COMPACTION cc
WHERE dt.TENANT_ID = cc.TENANT_ID
AND dt.TENANT_NAME = 'mysql_tenant';
```
Note

The major compaction is complete when the values of all FROZEN_SCN and LAST_SCN are equal.

Collect statistics.

Create a file named analyze_table.sql for collecting statistics.

call dbms_stats.gather_table_stats(NULL, 'part', degree=>128, granularity=>'AUTO', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'lineitem', degree=>128, granularity=>'AUTO', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'customer', degree=>128, granularity=>'AUTO', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'orders', degree=>128, granularity=>'AUTO', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'partsupp', degree=>128, granularity=>'AUTO', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'supplier', degree=>128, granularity=>'AUTO', method_opt=>'FOR ALL COLUMNS SIZE 128');

source analyze_table.sql

Step 8: Run the test

You can write a script based on the data and SQL statements generated in the preceding steps. The following example shows how to run the test:

Write the test script tpch.sh.

#!/bin/bash
host='$host_ip'   # Note: enter the IP address of the server where an OBServer node, such as OBServer A, resides.
port='$host_port' # The port number of OBServer A.
user='$user'      #  The username.
tenant='$tenant_name'  # The tenant name.
password='$password'   # The password.
database='$db_name'    #  The database name.
if [[ ${password} == "" ]];then
TPCH_TEST="obclient -h${host} -P${port} -u${user}@{$tenant} -D${database} -A -c"
else
TPCH_TEST="obclient -h${host} -P${port} -p${password} -u${user}@{$tenant} -D${database} -A -c"
fi


function clear_kvcache
{
   if [[ ${password_sys} == "" ]];then
      obclient_sys="obclient -h${host} -P${port} -uroot@sys -Doceanbase -A -c"
   else
      obclient_sys="obclient -h${host} -P${port} -uroot@sys -Doceanbase -p${password_sys} -A -c"
   fi
   tenant_name=${user#*@}
   echo "alter system flush kvcache ;" | ${obclient_sys}
   echo "alter system flush kvcache tenant '${tenant_name}' cache 'user_row_cache';" | ${obclient_sys}
   sleep 3s
}

function do_explain
{
#Execute execution plans
echo `date  '+[%Y-%m-%d %H:%M:%S]'` "BEGIN EXPLAIN ALL TPCH PLAN"
for i in {1..22}
do
   sql_explain="source explain_mysql/${i}.sql"
   echo `date  '+[%Y-%m-%d %H:%M:%S]'` "BEGIN EXPLAIN Q${i}:"
   echo ${sql_explain} | ${TPCH_TEST} | sed 's/\\n/\n/g' |tee explain_log/${i}.exp
   echo `date  '+[%Y-%m-%d %H:%M:%S]'` "Q${i} END"
done
}

function do_warmup
{
#Warmup
totaltime=0
for i in {1..22}
do
      starttime=`date +%s%N`
      echo `date  '+[%Y-%m-%d %H:%M:%S]'` "BEGIN prewarm Q${i}"
      sql1="source mysql_sql/${i}.sql"
      echo ${sql1}| ${TPCH_TEST} > mysql_log/${i}_prewarm.log  || ret=1
      stoptime=`date +%s%N`
      costtime=`echo ${stoptime} ${starttime} | awk '{printf "%0.2f\n", ($1 - $2) / 1000000000}'`
      first_array[$i]=$(echo "scale=2; ${first_array[$i]} + $costtime" | bc)  
      echo `date  '+[%Y-%m-%d %H:%M:%S]'` "END,COST ${costtime}s"
      totaltime=`echo ${totaltime} ${costtime} | awk '{printf "%0.2f\n", ($1 + $2)}'`
done
echo "total cost:${totaltime}s"
}

function hot_run
{
#Formal execution
for j in {1..10}
do
totaltime=0
for i in {1..22}
do
      starttime=`date +%s%N`
      echo `date  '+[%Y-%m-%d %H:%M:%S]'` "BEGIN BEST Q${i} (hot run)"
      sql1="source mysql_sql/${i}.sql"
      echo ${sql1}| ${TPCH_TEST} > mysql_log/${i}.log  || ret=1
      stoptime=`date +%s%N`
      costtime=`echo ${stoptime} ${starttime} | awk '{printf "%0.2f\n", ($1 - $2) / 1000000000}'`
      hot_array[$i]=$(echo "scale=2; ${hot_array[$i]} + $costtime" | bc)  
      echo `date  '+[%Y-%m-%d %H:%M:%S]'` "END,COST ${costtime}s"
      totaltime=`echo ${totaltime} ${costtime} | awk '{printf "%0.2f\n", ($1 + $2)}'`
done
echo "total cost:${totaltime}s"
done
}

function cold_run
{
#Formal execution
for j in {1..3}
do
totaltime=0
for i in {1..22}
do
      clear_kvcache
      starttime=`date +%s%N`
      echo `date  '+[%Y-%m-%d %H:%M:%S]'` "BEGIN BEST Q${i} (cold run)"
      sql1="source mysql_sql/${i}.sql"
      echo $sql1| $TPCH_TEST > mysql_log/${i}_cold.log  || ret=1
      stoptime=`date +%s%N`
      costtime=`echo $stoptime $starttime | awk '{printf "%0.2f\n", ($1 - $2) / 1000000000}'`
      cold_array[$i]=$(echo "scale=2; ${cold_array[$i]} + $costtime" | bc)
      echo `date  '+[%Y-%m-%d %H:%M:%S]'` "END,COST ${costtime}s"
      totaltime=`echo ${totaltime} ${costtime} | awk '{printf "%0.2f\n", ($1 + $2)}'`
done
echo "total cost:${totaltime}s"
done
}

do_explain

do_warmup

hot_run

cold_run

Run the test script.
```
sh tpch.sh
```

FAQ

Failed to import data. The error message is as follows:
```
ERROR 1017 (HY000) at line 1: File not exist
```
The tbl file must be placed in a directory on the server that hosts the connected OceanBase Database, because data loading must be performed locally.
An error occurred while viewing data. The error message is as follows:
```
ERROR 4624 (HY000): No memory or reach tenant memory limit
```
The memory is insufficient. We recommend that you increase the memory of the tenant.
An error occurred while importing data. The error message is as follows:
```
ERROR 1227 (42501) at line 1: Access denied
```
Grant the access privilege to the user. Run the following command to grant the privilege:
```
grant file on *.* to tpch_100g_part;
```

Try out operational OLAP

Through the preceding operations, you have obtained a TPC-H test environment. Now, let's manually run the test to explore the capabilities and features of OceanBase Database in OLAP scenarios. First, use OBClient to log in to the database. If you do not have OBClient installed, you can also use the mysql client.

obclient -h127.0.0.1 -P2881 -uroot@test  -Dtest -A -p -c

Before you start, set the degree of parallelism (DOP) based on the configurations of the OceanBase cluster and the tenant. We recommend that you set the DOP to be no more than twice the number of CPU cores of your tenant. For example, if your tenant has a maximum of 8 CPU cores, set the DOP to 16:

MySQL [test]> SET GLOBAL parallel_servers_target=16;
Query OK, 0 rows affected 

MySQL [test]> SET GLOBAL parallel_max_servers=16;
Query OK, 0 rows affected

OceanBase Database is compatible with most internal views of MySQL databases. Run the following command to query the sizes of tables in the current environment:

MySQL [test]> SELECT table_name, table_rows, CONCAT(ROUND(data_length/(1024*1024*1024),2),' GB')  table_size FROM information_schema.TABLES WHERE table_schema = 'test' order by table_rows desc;
+------------+------------+------------+
| table_name | table_rows | table_size |
+------------+------------+------------+
| lineitem   |    6001215 | 0.37 GB    |
| orders     |    1500000 | 0.08 GB    |
| partsupp   |     800000 | 0.04 GB    |
| part       |     200000 | 0.01 GB    |
| customer   |     150000 | 0.01 GB    |
| supplier   |      10000 | 0.00 GB    |
| nation     |         25 | 0.00 GB    |
| region     |          5 | 0.00 GB    |
+------------+------------+------------+
8 rows in set

Now, let's try the Q1 query of the TPC-H benchmark test to explore the query capability of OceanBase Database. The Q1 query summarizes and analyzes the prices, discounts, shipments, and quantities of various products within a specified period of time on the largest table named lineitem. This query reads all the data in the entire table and performs partitioning, sorting, and aggregation.

Execute the query without enabling parallel queries

First, run the query without enabling parallel queries:

select 
 l_returnflag,
 l_linestatus,
 sum(l_quantity) as sum_qty,
 sum(l_extendedprice) as sum_base_price,
 sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
 sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
 avg(l_quantity) as avg_qty,
 avg(l_extendedprice) as avg_price,
 avg(l_discount) as avg_disc,
 count(*) as count_order
from
 lineitem
where
 l_shipdate <= date '1998-12-01' - interval '90' day
group by
 l_returnflag,
 l_linestatus
order by
 l_returnflag,
 l_linestatus;

In the test environment of this example, the execution result is as follows:

+--------------+--------------+----------+----------------+----------------+--------------+---------+------------+----------+-------------+
| l_returnflag | l_linestatus | sum_qty  | sum_base_price | sum_disc_price | sum_charge   | avg_qty | avg_price  | avg_disc | count_order |
+--------------+--------------+----------+----------------+----------------+--------------+---------+------------+----------+-------------+
| A            | F            | 37734107 |    56586577106 |    56586577106 |  56586577106 | 25.5220 | 38273.1451 |   0.0000 |     1478493 |
| N            | F            |   991417 |     1487505208 |     1487505208 |   1487505208 | 25.5165 | 38284.4806 |   0.0000 |       38854 |
| N            | O            | 74476040 |   111701776272 |   111701776272 | 111701776272 | 25.5022 | 38249.1339 |   0.0000 |     2920374 |
| R            | F            | 37719753 |    56568064200 |    56568064200 |  56568064200 | 25.5058 | 38250.8701 |   0.0000 |     1478870 |
+--------------+--------------+----------+----------------+----------------+--------------+---------+------------+----------+-------------+
4 rows in set (6.791 sec)

Enable parallel queries

The operational OLAP capability of OceanBase Database is based on one set of data and the execution engine, without the need to synchronize or maintain heterogeneous data. Now, add a parallel hint to the query statement to set the DOP to 8 and execute the statement again:

select /*+parallel(8) */
 l_returnflag,
 l_linestatus,
 sum(l_quantity) as sum_qty,
 sum(l_extendedprice) as sum_base_price,
 sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
 sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
 avg(l_quantity) as avg_qty,
 avg(l_extendedprice) as avg_price,
 avg(l_discount) as avg_disc,
 count(*) as count_order
from
 lineitem
where
 l_shipdate <= date '1998-12-01' - interval '90' day
group by
 l_returnflag,
 l_linestatus
order by
 l_returnflag,
 l_linestatus;

In the same environment and datasets, the execution result is as follows:

+--------------+--------------+----------+----------------+----------------+--------------+---------+------------+----------+-------------+
| l_returnflag | l_linestatus | sum_qty  | sum_base_price | sum_disc_price | sum_charge   | avg_qty | avg_price  | avg_disc | count_order |
+--------------+--------------+----------+----------------+----------------+--------------+---------+------------+----------+-------------+
| A            | F            | 37734107 |    56586577106 |    56586577106 |  56586577106 | 25.5220 | 38273.1451 |   0.0000 |     1478493 |
| N            | F            |   991417 |     1487505208 |     1487505208 |   1487505208 | 25.5165 | 38284.4806 |   0.0000 |       38854 |
| N            | O            | 74476040 |   111701776272 |   111701776272 | 111701776272 | 25.5022 | 38249.1339 |   0.0000 |     2920374 |
| R            | F            | 37719753 |    56568064200 |    56568064200 |  56568064200 | 25.5058 | 38250.8701 |   0.0000 |     1478870 |
+--------------+--------------+----------+----------------+----------------+--------------+---------+------------+----------+-------------+
4 rows in set (1.197 sec)

As you can see, compared with the execution time without parallel queries, the query speed is increased by nearly 6 times with parallel queries. If you run the EXPLAIN command to view the execution plan, you can also see the DOP (line 18, operator 1, dop=8):

===============================================================
|ID|OPERATOR                      |NAME    |EST. ROWS|COST    |
---------------------------------------------------------------
|0 |PX COORDINATOR MERGE SORT     |        |6        |13507125|
|1 | EXCHANGE OUT DISTR           |:EX10001|6        |13507124|
|2 |  SORT                        |        |6        |13507124|
|3 |   HASH GROUP BY              |        |6        |13507107|
|4 |    EXCHANGE IN DISTR         |        |6        |8379337 |
|5 |     EXCHANGE OUT DISTR (HASH)|:EX10000|6        |8379335 |
|6 |      HASH GROUP BY           |        |6        |8379335 |
|7 |       PX BLOCK ITERATOR      |        |5939712  |3251565 |
|8 |        TABLE SCAN            |lineitem|5939712  |3251565 |
===============================================================

Outputs & filters:
-------------------------------------
  0 - output([lineitem.l_returnflag], [lineitem.l_linestatus], [T_FUN_SUM(T_FUN_SUM(lineitem.l_quantity))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice * 1 - lineitem.l_discount))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice * 1 - lineitem.l_discount * 1 + lineitem.l_tax))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_quantity)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_quantity)), DECIMAL(20, 0))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_extendedprice)), DECIMAL(20, 0))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_discount)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_discount)), DECIMAL(20, 0))], [T_FUN_COUNT_SUM(T_FUN_COUNT(*))]), filter(nil), sort_keys([lineitem.l_returnflag, ASC], [lineitem.l_linestatus, ASC])
  1 - output([lineitem.l_returnflag], [lineitem.l_linestatus], [T_FUN_SUM(T_FUN_SUM(lineitem.l_quantity))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice * 1 - lineitem.l_discount))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice * 1 - lineitem.l_discount * 1 + lineitem.l_tax))], [T_FUN_COUNT_SUM(T_FUN_COUNT(*))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_quantity)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_quantity)), DECIMAL(20, 0))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_extendedprice)), DECIMAL(20, 0))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_discount)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_discount)), DECIMAL(20, 0))]), filter(nil), dop=8
  2 - output([lineitem.l_returnflag], [lineitem.l_linestatus], [T_FUN_SUM(T_FUN_SUM(lineitem.l_quantity))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice * 1 - lineitem.l_discount))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice * 1 - lineitem.l_discount * 1 + lineitem.l_tax))], [T_FUN_COUNT_SUM(T_FUN_COUNT(*))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_quantity)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_quantity)), DECIMAL(20, 0))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_extendedprice)), DECIMAL(20, 0))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_discount)) / cast(T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_discount)), DECIMAL(20, 0))]), filter(nil), sort_keys([lineitem.l_returnflag, ASC], [lineitem.l_linestatus, ASC])
  3 - output([lineitem.l_returnflag], [lineitem.l_linestatus], [T_FUN_SUM(T_FUN_SUM(lineitem.l_quantity))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice * 1 - lineitem.l_discount))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice * 1 - lineitem.l_discount * 1 + lineitem.l_tax))], [T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_quantity))], [T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_extendedprice))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_discount))], [T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_discount))], [T_FUN_COUNT_SUM(T_FUN_COUNT(*))]), filter(nil),
      group([lineitem.l_returnflag], [lineitem.l_linestatus]), agg_func([T_FUN_SUM(T_FUN_SUM(lineitem.l_quantity))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice * 1 - lineitem.l_discount))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_extendedprice * 1 - lineitem.l_discount * 1 + lineitem.l_tax))], [T_FUN_COUNT_SUM(T_FUN_COUNT(*))], [T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_quantity))], [T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_extendedprice))], [T_FUN_SUM(T_FUN_SUM(lineitem.l_discount))], [T_FUN_COUNT_SUM(T_FUN_COUNT(lineitem.l_discount))])
  4 - output([lineitem.l_returnflag], [lineitem.l_linestatus], [T_FUN_SUM(lineitem.l_quantity)], [T_FUN_SUM(lineitem.l_extendedprice)], [T_FUN_SUM(lineitem.l_extendedprice * 1 - lineitem.l_discount)], [T_FUN_SUM(lineitem.l_extendedprice * 1 - lineitem.l_discount * 1 + lineitem.l_tax)], [T_FUN_COUNT(lineitem.l_quantity)], [T_FUN_COUNT(lineitem.l_extendedprice)], [T_FUN_SUM(lineitem.l_discount)], [T_FUN_COUNT(lineitem.l_discount)], [T_FUN_COUNT(*)]), filter(nil)
  5 - (#keys=2, [lineitem.l_returnflag], [lineitem.l_linestatus]), output([lineitem.l_returnflag], [lineitem.l_linestatus], [T_FUN_SUM(lineitem.l_quantity)], [T_FUN_SUM(lineitem.l_extendedprice)], [T_FUN_SUM(lineitem.l_extendedprice * 1 - lineitem.l_discount)], [T_FUN_SUM(lineitem.l_extendedprice * 1 - lineitem.l_discount * 1 + lineitem.l_tax)], [T_FUN_COUNT(lineitem.l_quantity)], [T_FUN_COUNT(lineitem.l_extendedprice)], [T_FUN_SUM(lineitem.l_discount)], [T_FUN_COUNT(lineitem.l_discount)], [T_FUN_COUNT(*)]), filter(nil), dop=8
  6 - output([lineitem.l_returnflag], [lineitem.l_linestatus], [T_FUN_SUM(lineitem.l_quantity)], [T_FUN_SUM(lineitem.l_extendedprice)], [T_FUN_SUM(lineitem.l_extendedprice * 1 - lineitem.l_discount)], [T_FUN_SUM(lineitem.l_extendedprice * 1 - lineitem.l_discount * 1 + lineitem.l_tax)], [T_FUN_COUNT(lineitem.l_quantity)], [T_FUN_COUNT(lineitem.l_extendedprice)], [T_FUN_SUM(lineitem.l_discount)], [T_FUN_COUNT(lineitem.l_discount)], [T_FUN_COUNT(*)]), filter(nil),
      group([lineitem.l_returnflag], [lineitem.l_linestatus]), agg_func([T_FUN_SUM(lineitem.l_quantity)], [T_FUN_SUM(lineitem.l_extendedprice)], [T_FUN_SUM(lineitem.l_extendedprice * 1 - lineitem.l_discount)], [T_FUN_SUM(lineitem.l_extendedprice * 1 - lineitem.l_discount * 1 + lineitem.l_tax)], [T_FUN_COUNT(*)], [T_FUN_COUNT(lineitem.l_quantity)], [T_FUN_COUNT(lineitem.l_extendedprice)], [T_FUN_SUM(lineitem.l_discount)], [T_FUN_COUNT(lineitem.l_discount)])
  7 - output([lineitem.l_returnflag], [lineitem.l_linestatus], [lineitem.l_quantity], [lineitem.l_extendedprice], [lineitem.l_discount], [lineitem.l_extendedprice * 1 - lineitem.l_discount], [lineitem.l_extendedprice * 1 - lineitem.l_discount * 1 + lineitem.l_tax]), filter(nil)
  8 - output([lineitem.l_returnflag], [lineitem.l_linestatus], [lineitem.l_quantity], [lineitem.l_extendedprice], [lineitem.l_discount], [lineitem.l_extendedprice * 1 - lineitem.l_discount], [lineitem.l_extendedprice * 1 - lineitem.l_discount * 1 + lineitem.l_tax]), filter([lineitem.l_shipdate <= ?]),
      access([lineitem.l_shipdate], [lineitem.l_returnflag], [lineitem.l_linestatus], [lineitem.l_quantity], [lineitem.l_extendedprice], [lineitem.l_discount], [lineitem.l_tax]), partitions(p[0-15])

In this example, OceanBase Database is deployed on a single server. It is worth noting that the most prominent feature of the parallel execution framework of OceanBase Database lies in that it can concurrently execute analytical queries on large amounts of data on multiple servers. For example, if a table has hundreds of millions of data rows across multiple OBServer nodes, the distributed execution framework generates a distributed parallel execution plan and uses the resources of multiple nodes for analytical queries. Therefore, OceanBase Database has high scalability. In addition, you can set the DOP in multiple dimensions, such as SQL statement, session, and table.

Use obd to run the TPC-H benchmark test

Applicability

This section applies only to OceanBase Database Community Edition. OceanBase Database Enterprise Edition does not support OceanBase Deployer (obd).

In addition to the dataset generation tools provided on the official TPC website, you can also use obd to conveniently generate datasets, create tables, import data, and automatically execute 22 SQL statements for the TPC-H benchmark test. Before you use obd to run the TPC-H benchmark test, install the obtpch component on the server where OceanBase Database and obd are deployed:

sudo yum install obtpch

Then, run the following command to start the TPC-H benchmark test, with a dataset size of 1 GB. The entire process includes dataset generation, schema import, and automatic test execution. In this example, it is assumed that your test environment is deployed in the same way as that in Quickly experience OceanBase Database. You can modify the cluster name, password, installation directory, etc. based on your actual situation.

Notice

Make sure that your disk space is sufficient to store the dataset files, so as to avoid filling up the space and causing system errors.

In this example, the /tmp directory is used for the test.

cd /tmp
obd test tpch obtest --tenant=test -s 1 --password='******' --remote-tbl-dir=/tmp/tpch1

After the preceding command is executed, obd starts to run the test, and you can see each step of the test process:

obd

sql

After the data import is completed, obd automatically executes 22 SQL statements and prints the execution time for each SQL statement as well as the total execution time.

OceanBase

Customer Stories

Documentation

Try out operational OLAP

Note

Manually run the TPC-H benchmark test

Step 1: Create a test tenant

Note

Step 2: Optimize the test environment

Step 3: Install the TPC-H tools

Step 4: Generate data

Step 5: Generate SQL query statements

Note

Step 6: Create tables

Step 7: Load data

Note

Step 8: Run the test

FAQ

Try out operational OLAP

Execute the query without enabling parallel queries

Enable parallel queries

Use obd to run the TPC-H benchmark test

Applicability

Notice