This topic demonstrates how to join the `orders` table with itself to generate a Cartesian product of 20,000 × 20,000 rows. It compares the running times with and without parallel execution to validate the real-time analytics capabilities of the OceanBase database.
Concepts
Online Analytical Processing (OLAP)
Online Analytical Processing (OLAP) is a technology for multi-dimensional data analysis. It is designed to process large, multi-dimensional datasets to support data analytics, data mining, and business decision-making.
Parallel execution
In the OceanBase database, parallel execution is a form of concurrent processing that leverages its distributed architecture. The database uses a distributed architecture that partitions data and stores the shards on different nodes. This approach uses data and task partitioning to enable parallel processing for both data and computation. Each node in OceanBase can execute tasks independently and communicate with other nodes over a high-speed network for data interaction and synchronization.
PARALLEL hint syntax
The `PARALLEL` hint is a statement-level hint that specifies the degree of parallelism for the optimizer to use in a parallel operation. You can use hints or the `-c` option in the MySQL client without affecting your services or causing errors. This hint overrides the `PARALLEL_DEGREE_POLICY` initialization parameter. You can use this hint in `SELECT`, `INSERT`, `UPDATE`, and `DELETE` statements, and as part of a table scan. The `PARALLEL` hint is ignored if any parallel restrictions are violated. For more information, see Hints related to parallel execution. Hints allow you to guide the optimizer toward a specific execution plan. For more information, see Hint overview. The syntax for the `PARALLEL` hint is as follows:
/*+ PARALLEL(n) */In the `PARALLEL` hint, the parameter `n` specifies the degree of parallelism. If a sorting or grouping operation is also performed, the number of available threads is twice the value specified in the `PARALLEL` hint.
Cartesian product
A Cartesian product combines all rows from two tables to create a new table. For example, if you have Table A and Table B with the following data:
Table A:
Name
Price
Apple
5
Banana
3
Table B:
Color
Inventory
Red
100
Yellow
200
The Cartesian product of A and B is a new table that contains the following rows:
Name
Price
Color
Inventory
Apple
5
Red
100
Apple
5
Yellow
200
Banana
3
Red
100
Banana
3
Yellow
200
Syntax
The `JOIN` clause combines rows from two or more tables based on a specified join condition. The result set can be saved as a new table or used as a temporary one. For more information, see JOIN clause.
Procedure
Open a new SQL window and click Select Database to select the `tutorial_database` database.
Run the following command to join the `orders` table with itself and compute the Cartesian product of 20,000 × 20,000 rows.
select count(1) from orders a join (select * from orders) b;The running time is 4.25 s. This value is for reference only.
Open a new SQL window and click Select Database to select the `tutorial_database` database.
Run the following command to enable eight parallel executions and speed up the computation.
select /*+parallel(8)*/ count(1) from orders a join (select * from orders) b;The running time is 1.99 s. This value is for reference only.
Analysis of analytics capabilities
Without parallel execution, the running time is about 4.25 s. With eight parallel executions enabled, the running time is reduced to 1.99 s. OceanBase is built on a distributed architecture. It delivers high-performance transactional processing (TP) while also handling analytics scenarios, such as real-time analytics and batch processing. It operates without affecting Online Transactional Processing (OLTP) capabilities. This design also minimizes data redundancy and helps businesses significantly reduce their total costs.
The preceding data is for reference only. Your actual results may vary.