非分区表并行查询问题

【产品名称】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)

问题已解决,需要在连接的时候加上-c参数,否则hint不生效,obclient和mysql两个客户端都需要加。

1 个赞

mysql 也是这样的, 我们这个行为和mysql 是一致的.

嗯嗯