返回首页

gbase数据、南大通用产品文档：GBase8aEXTENDED 输出

更新日期：2024年09月11日

EXPLAIN 后边有EXTENDED 时，显示查询计划的扩展输出格式。

示例1：
EXPLAIN EXTENDED SELECT x1.id2 FROM x1, x2 WHERE x1.id2 = x2.id3
AND x1.id3 IN (SELECT id2 FROM x2 WHERE x2.id4 > 10);
查询计划：
QueryPlan:
Uncorrelated SUB plan
==============================================

GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
1153
==============================================
QueryPlan:
++++++++++++++++++++++++++++++++++++++++++++++
Leaf type: REGULAR STEP
【一般步骤，与汇总步骤对应】
Need combiner: false【不需要汇总】
Target temp table: _tmp_2030479552_19_t160_1_1496193667_s 【目标临时
表名】
Temp
table
definition:
CREATE
TABLE
`gctmpdb`._tmp_2030479552_19_t160_1_1496193667_s
AS
SELECT
/*192.168.6.121_19_15_2017-05-31_09:24:31*/ /*+ TID('131280') */ DISTINCT
`regress_db_link.x2`.`id2`
AS
`id2`
FROM
`regress_db_link`.`x2`
`regress_db_link.x2` WHERE (`regress_db_link.x2`.`id4` > 10) LIMIT 0【创建目
标表的SQL】
Optimization:
Query
String:
SELECT
/*192.168.6.121_19_15_2017-05-31_09:24:31*/ /*+ TID('131280') */ DISTINCT
`regress_db_link.x2`.`id2`
AS
`id2`
FROM
`regress_db_link`.`x2`
`regress_db_link.x2` WHERE (`regress_db_link.x2`.`id4` > 10)【查询语句】
【索引内容】May use index: `regress_db_link`.`x2`.`id4`{Smart Index}
【代价内容】
CostInfo: Start(0), Run(0.11), Selectivity(0.578947), Width(8),
Rows(11)
==============================================
end SUB plan
++++++++++++++++++++++++++++++++++++++++++++++
Leaf type: REGULAR STEP
Need combiner: false
Target temp table: _tmp_2030479552_19_t160_2_1496193667_s
Temp
table
definition:
CREATE
TABLE
`gctmpdb`._tmp_2030479552_19_t160_2_1496193667_s
AS
SELECT
/*192.168.6.121_19_15_2017-05-31_09:24:31*/
/*+
TID('131280')
*/
`regress_db_link.x1`.`id2`
AS
`id2`
FROM
`regress_db_link`.`x1`
`regress_db_link.x1`
WHERE
`regress_db_link.x1`.`id3`
IN
(SELECT
`id2`
FROM `gctmpdb`.`_tmp_2030479552_19_t160_1_1496193667_s`) LIMIT 0
Optimization:
Query
String:
SELECT
/*192.168.6.121_19_15_2017-05-31_09:24:31*/
/*+
TID('131280')
*/

GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
1154
`regress_db_link.x1`.`id2`
AS
`id2`
FROM
`regress_db_link`.`x1`
`regress_db_link.x1`
WHERE
`regress_db_link.x1`.`id3`
IN
(SELECT
`id2`
FROM `gctmpdb`.`_tmp_2030479552_19_t160_1_1496193667_s`)
【代价内容】
CostInfo: Start(0), Run(0.07), Selectivity(0.291667), Width(8),
Rows(7)
++++++++++++++++++++++++++++++++++++++++++++++
Leaf type: REGULAR STEP
Need combiner: false
Target temp table: _tmp_2030479552_19_t160_3_1496193667_s
Temp
table
definition:
CREATE
TABLE
`gctmpdb`._tmp_2030479552_19_t160_3_1496193667_s
AS
SELECT
/*192.168.6.121_19_15_2017-05-31_09:24:31*/
/*+
TID('131280')
*/
`_tmp_2030479552_19_t160_2_1496193667_s`.`id2`
AS
`id2`
FROM
`gctmpdb`._tmp_2030479552_19_t160_2_1496193667_s
INNER
JOIN
`regress_db_link`.`x2`
`regress_db_link.x2`
ON
(`_tmp_2030479552_19_t160_2_1496193667_s`.`id2`
=
`regress_db_link.x2`.`id3`) LIMIT 0
Optimization:
Query
String:
SELECT
/*192.168.6.121_19_15_2017-05-31_09:24:31*/
/*+
TID('131280')
*/
`_tmp_2030479552_19_t160_2_1496193667_s`.`id2`
AS
`id2`
FROM
`gctmpdb`._tmp_2030479552_19_t160_2_1496193667_s
INNER
JOIN
`regress_db_link`.`x2`
`regress_db_link.x2`
ON
(`_tmp_2030479552_19_t160_2_1496193667_s`.`id2`
=
`regress_db_link.x2`.`id3`)
【代价内容】
CostInfo:
Start(0.526),
Run(0.52),
Selectivity(0.0789474),
Width(12), Rows(10)
==============================================
Uncorrelated Subplan:
CExecStep
--------------------------
isQueryFinalStep = 0
【不是查询的最后一步】

GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
1155
DestType = 1
【结果发送到所有节点】
aStepDetail
【8a 所有节点属性】
---------------
isProducer = 1
【是生产者】
isConsumer = 1
【是消费者】
producerDistID = 1
【生产者distribution id 为1】
consumerDistID = 1
【消费者distribution id 为1】
isSingleHashNode = 0
【非单节点hash 优化】
isHashRedist = 0
【不是hash 重分布】
isGroupHashRedist = 0
isAllTableAreHashTmpDist = 0
【非所有源表都是HASH 临时表】
isExistsHashReditTable = 0
【源表不存在HASH 临时表】
queryString = SELECT /*192.168.6.121_19_15_2017-05-31_09:24:31*/
/*+
TID('131280')
*/
DISTINCT
`regress_db_link.x2`.`id2`
AS
`id2`
FROM
`regress_db_link`.`x2` `regress_db_link.x2` WHERE (`regress_db_link.x2`.`id4` >
10)
targetTable = `gctmpdb`._tmp_2030479552_19_t160_1_1496193667_s
targetSchema
=
CREATE
TABLE
`gctmpdb`._tmp_2030479552_19_t160_1_1496193667_s
AS
SELECT
/*192.168.6.121_19_15_2017-05-31_09:24:31*/ /*+ TID('131280') */ DISTINCT
`regress_db_link.x2`.`id2`
AS
`id2`
FROM
`regress_db_link`.`x2`
`regress_db_link.x2` WHERE (`regress_db_link.x2`.`id4` > 10) LIMIT 0
CExecStep
--------------------------
isQueryFinalStep = 0
DestType = 1
aStepDetail
---------------
isProducer = 1
isConsumer = 1
producerDistID = 1
consumerDistID = 1
isSingleHashNode = 0
isHashRedist = 0

GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
1156
isGroupHashRedist = 0
isAllTableAreHashTmpDist = 0
isExistsHashReditTable = 0
queryString = SELECT /*192.168.6.121_19_15_2017-05-31_09:24:31*/
/*+
TID('131280') */ `regress_db_link.x1`.`id2` AS `id2` FROM `regress_db_link`.`x1`
`regress_db_link.x1`
WHERE
`regress_db_link.x1`.`id3`
IN
(SELECT
`id2`
FROM `gctmpdb`.`_tmp_2030479552_19_t160_1_1496193667_s`)
targetTable = `gctmpdb`._tmp_2030479552_19_t160_2_1496193667_s
targetSchema
=
CREATE
TABLE
`gctmpdb`._tmp_2030479552_19_t160_2_1496193667_s
AS
SELECT
/*192.168.6.121_19_15_2017-05-31_09:24:31*/
/*+
TID('131280')
*/
`regress_db_link.x1`.`id2`
AS
`id2`
FROM
`regress_db_link`.`x1`
`regress_db_link.x1`
WHERE
`regress_db_link.x1`.`id3`
IN
(SELECT
`id2`
FROM `gctmpdb`.`_tmp_2030479552_19_t160_1_1496193667_s`) LIMIT 0
Step Drop List = `gctmpdb`._tmp_2030479552_19_t160_1_1496193667_s
CExecStep
--------------------------
isQueryFinalStep = 0
DestType = 0
aStepDetail
---------------
isProducer = 1
isConsumer = 0
producerDistID = 1
consumerDistID = 1
isSingleHashNode = 0
isHashRedist = 0
isGroupHashRedist = 0
isAllTableAreHashTmpDist = 0
isExistsHashReditTable = 0
queryString = SELECT /*192.168.6.121_19_15_2017-05-31_09:24:31*/
/*+
TID('131280') */ `_tmp_2030479552_19_t160_2_1496193667_s`.`id2` AS `id2`
FROM `gctmpdb`._tmp_2030479552_19_t160_2_1496193667_s INNER JOIN
`regress_db_link`.`x2`
`regress_db_link.x2`
ON
(`_tmp_2030479552_19_t160_2_1496193667_s`.`id2`
=

GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
1157
`regress_db_link.x2`.`id3`)
Step Drop List = `gctmpdb`._tmp_2030479552_19_t160_2_1496193667_s

示例2：
explain extended select x1.id2 from x1, x2, x3 where x1.id2 = x2.id3 and x1.id3 =
x3.id2;
查询计划：
QueryPlan:
++++++++++++++++++++++++++++++++++++++++++++++
Leaf type: REGULAR STEP
Need combiner: false
Target temp table: _tmp_2030479552_19_t161_1_1496193667_s
Temp table definition: CREATE TABLE `gctmpdb`._tmp_2030479552_19_t16
1_1_1496193667_s AS SELECT /*192.168.6.121_19_16_2017-05-31_09:56:34
*/ /*+ TID('131281') */ `regress_db_link.x3`.`id2` AS `id2` FROM `regress_d
b_link`.`x3` `regress_db_link.x3` LIMIT 0
Optimization:
Query String: SELECT /*192.168.6.121_19_16_2017-05-31_09:56:3
4*/ /*+ TID('131281') */ `regress_db_link.x3`.`id2` AS `id2` FROM `regress_
db_link`.`x3` `regress_db_link.x3`
CostInfo: Start(0), Run(0.13), Selectivity(1), Width(4), Rows(13)
++++++++++++++++++++++++++++++++++++++++++++++
Leaf type: REGULAR STEP
Need combiner: false
Target temp table: _tmp_rht_2030479552_19_t161_2_1496193667_s
Temp table definition: CREATE TABLE `gctmpdb`._tmp_rht_2030479552_19_
t161_2_1496193667_s AS SELECT /*192.168.6.121_19_16_2017-05-31_09:56:
34*/ /*+ TID('131281') */ `regress_db_link.x1`.`id2` AS `id2` FROM `regress
_db_link`.`x1` `regress_db_link.x1` INNER JOIN `gctmpdb`._tmp_2030479552
_19_t161_1_1496193667_s ON (`regress_db_link.x1`.`id3` = `_tmp_203047955
2_19_t161_1_1496193667_s`.`id2`) LIMIT 0
Optimization: {hash redist} 【是HASH 重分布步骤】
Hash Redist Indexes: 1【HASH 重分布下标(从1 开始)】
Query String: SELECT /*192.168.6.121_19_16_2017-05-31_09:56:3
4*/ /*+ TID('131281') */ `regress_db_link.x1`.`id2` AS `id2` FROM `regress_

GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
1158
db_link`.`x1` `regress_db_link.x1` INNER JOIN `gctmpdb`._tmp_2030479552_
19_t161_1_1496193667_s ON (`regress_db_link.x1`.`id3` = `_tmp_2030479552
_19_t161_1_1496193667_s`.`id2`)
CostInfo: Start(0.812), Run(0.74), Selectivity(0.0608974), Width(1
2), Rows(19)
++++++++++++++++++++++++++++++++++++++++++++++
Leaf type: REGULAR STEP
Need combiner: false
Target temp table: _tmp_rht_2030479552_19_t161_3_1496193667_s
Temp table definition: CREATE TABLE `gctmpdb`._tmp_rht_2030479552_19_
t161_3_1496193667_s AS SELECT /*192.168.6.121_19_16_2017-05-31_09:56:
34*/ /*+ TID('131281') */ `regress_db_link.x2`.`id3` AS `id3` FROM `regress
_db_link`.`x2` `regress_db_link.x2` LIMIT 0
Optimization: {hash redist}
Hash Redist Indexes: 1
Query String: SELECT /*192.168.6.121_19_16_2017-05-31_09:56:3
4*/ /*+ TID('131281') */ `regress_db_link.x2`.`id3` AS `id3` FROM `regress_
db_link`.`x2` `regress_db_link.x2`
CostInfo: Start(0), Run(0.19), Selectivity(1), Width(4), Rows(19)
++++++++++++++++++++++++++++++++++++++++++++++
Leaf type: REGULAR STEP
Need combiner: false
Target temp table: _tmp_2030479552_19_t161_4_1496193667_s
Temp table definition: CREATE TABLE `gctmpdb`._tmp_2030479552_19_t16
1_4_1496193667_s AS SELECT /*192.168.6.121_19_16_2017-05-31_09:56:34
*/ /*+ TID('131281') */ `_tmp_rht_2030479552_19_t161_2_1496193667_s`.`id2
` AS `id2` FROM `gctmpdb`._tmp_rht_2030479552_19_t161_2_1496193667_s
INNER JOIN `gctmpdb`._tmp_rht_2030479552_19_t161_3_1496193667_s ON
(`_tmp_rht_2030479552_19_t161_2_1496193667_s`.`id2` = `_tmp_rht_203047
9552_19_t161_3_1496193667_s`.`id3`) LIMIT 0
Optimization:
Query String: SELECT /*192.168.6.121_19_16_2017-05-31_09:56:3
4*/ /*+ TID('131281') */ `_tmp_rht_2030479552_19_t161_2_1496193667_s`.`id
2` AS `id2` FROM `gctmpdb`._tmp_rht_2030479552_19_t161_2_1496193667_
s INNER JOIN `gctmpdb`._tmp_rht_2030479552_19_t161_3_1496193667_s O
N (`_tmp_rht_2030479552_19_t161_2_1496193667_s`.`id2` = `_tmp_rht_20304

GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
1159
79552_19_t161_3_1496193667_s`.`id3`)
CostInfo: Start(1.40733), Run(1.31), Selectivity(0.0789474), Wi
dth(16), Rows(28)

函数说明
返回从hour、minute、second 计算得到的时间值。

GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
802
示例
示例1：返回“12,15,30”对应的时分秒的值。
gbase> SELECT MAKETIME(12,15,30) FROM dual;
+--------------------+
| MAKETIME(12,15,30) |
+--------------------+
| 12:15:30
|
+--------------------+
1 row in set

CentOS/RHEL 7.2 或Kylin-Server-20200711 及之后版本的操作系统中，systemd-logind
服务引入一个新特性——RemoveIPC，表现为：用户登录后创建的文件，在logout 后会被自
动删除。该特性由/etc/systemd/logind.conf 参数文件中RemoveIPC 选项来控制。详细信息请
执行man logind.conf(5)命令查看。
当使用默认值（即RemoveIPC=yes）的情况，当用户退出时，操作系统会crash 掉使用
了Shared Memory Segment (SHM) or Semaphores (SEM)的应用程序，导致GBase 8c 数据库
进程将会被中断。
因此在CentOS 7.2+、RHEL 7.2+或Kylin-Server-20200711+操作系统环境中，需检查
RemoveIPC 参数，避免发生此类问题。操作步骤如下：
步骤1 查看RemoveIPC 参数值是否为yes
[root@gbase8c ~]# loginctl show-session | grep RemoveIPC
[root@gbase8c ~]# systemctl show systemd-logind | grep RemoveIPC
如果为yes，则需要修改；如果为no，则无需继续执行后续步骤。
步骤2 修改RemoveIPC 参数值。需要在集群每个节点服务器上执行以下操作：

修改/etc/systemd/logind.conf 配置文件
[root@gbase8c ~]# vim /etc/systemd/logind.conf
将RemoveIPC 参数值设置为no，键入“:wq”保存并退出。

修改/usr/lib/systemd/system/systemd-logind.service 配置文件，将RemoveIPC 参数值设置
为no。
[root@gbase8c ~]# vim /usr/lib/systemd/system/systemd-logind.service
将RemoveIPC 参数值设置为no，键入“:wq”保存并退出。
步骤3 重新加载配置文件，执行以下命令：

GBase 8c V5 安装部署手册（主备式）
南大通用数据技术股份有限公司
33
[root@gbase8c ~]# systemctl daemon-reload
[root@gbase8c ~]# systemctl restart systemd-logind
再次检查是否生效即可。