【产品名称】OB
【产品版本】3.1.2
【问题描述】
现在需要进行并行查询测试，表为非分区表，发现使用文档的parallel hint并没有触发并行查询
启用非分区表并行查询
非分区表本质上是只有 1 个分区的分区表，因此针对非分区表的查询，只能通过添加
PARALLEL
Hint 的方式启动分区内并行查询，否则不会启动并行查询。
如下例所示，创建一个非分区表
stable
，对
stable
进行全表数据的扫描操作，通过
EXPLAIN
命令查看生成的执行计划。
obclient>CREATE TABLE stable(c1 INT, c2 INT); Query OK, 0 rows affected (0.12 sec) obclient>EXPLAIN SELECT * FROM stable\G; *************************** 1. row *************************** Query Plan: ====================================== |ID|OPERATOR |NAME |EST. ROWS|COST | -------------------------------------- |0 |TABLE SCAN|stable|100000 |68478| ====================================== Outputs & filters: ------------------------------------- 0 - output([stable.c1], [stable.c2]), filter(nil), access([stable.c1], [stable.c2]), partitions(p0)
通过执行计划可以看出，非分区表不使用 Hint 的情况下，不会启动并行查询。
针对非分区表，添加
PARALLEL
Hint 启动分区内并行查询，并指定
dop
值（大于等于 2），通过
EXPLAIN
命令查看生成的执行计划。
obclient>EXPLAIN SELECT /*+ PARALLEL(4)*/ * FROM stable\G; *************************** 1. row *************************** Query Plan: ================================================= |ID|OPERATOR |NAME |EST. ROWS|COST | ------------------------------------------------- |0 |EXCHANGE IN DISTR | |100000 |77944| |1 | EXCHANGE OUT DISTR|:EX10000|100000 |68478| |2 | PX BLOCK ITERATOR| |100000 |68478| |3 | TABLE SCAN |stable |100000 |68478| ================================================= Outputs & filters: ------------------------------------- 0 - output([stable.c1], [stable.c2]), filter(nil) 1 - output([stable.c1], [stable.c2]), filter(nil), dop=4 2 - output([stable.c1], [stable.c2]), filter(nil) 3 - output([stable.c1], [stable.c2]), filter(nil), access([stable.c1], [stable.c2]), partitions(p0)
----------------------------------------------------------------------------------------
以下为我测试的步骤：
create table t1(c1 int,c2 int);
Query OK, 0 rows affected (0.144 sec)
> explain select * from t1;
±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ====================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
------------------------------------
|0 |TABLE SCAN|t1 |100000 |64066|
====================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1], [t1.c2]), filter(nil),
access([t1.c1], [t1.c2]), partitions(p0)
|
±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.021 sec)
> explain select /*+ parallel(64)*/ * from t1;
±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan |
±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ====================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
------------------------------------
|0 |TABLE SCAN|t1 |100000 |64066|
====================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1], [t1.c2]), filter(nil),
access([t1.c1], [t1.c2]), partitions(p0)
|
±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)