GBase 8s
其他
问答
普通联表查询报错
发表于2025-09-19 09:51:50123次浏览11个评论
为提高效率，提问时请提供以下信息，问题描述清晰可优先响应。
【GBase版本】:v8.8
【问题描述】*:普通的连表查询报错：[IX000][-229] Could not open or create a temporary file. java.sql.SQLException，连表sql涉及到的表字段类型均为varchar/lvarchar，请问如何排查这个报错
评论
登录后才可以发表评论
路路路发表于 10个月前
问题收到，稍等
远方发表于 10个月前
检查一下临时表空间的配置和sql是否有索引
GBase用户8788发表于 10个月前
onstat -d查看临时表空间的大小使用情况
然后再查一下表所在数据库是否带日志模式
然后再查一下表所在数据库是否带日志模式
梦之泪殇严肃版发表于 10个月前
@GBase用户8788：创建数据库带有with log
onstat -d
Your evaluation license will expire on 2026-09-04 00:00:00
On-Line -- Up 01:19:29 -- 597864 Kbytes
Dbspaces
address number flags fchunk nchunks pgsize flags owner name
46fe9028 1 0x70001 1 1 2048 N BA gbasedbt rootdbs
48601028 2 0x60001 2 1 2048 N BA gbasedbt llogdbs
48601258 3 0x70001 3 1 2048 N BA gbasedbt plogdbs
48601488 4 0x68001 4 1 2048 N SBA gbasedbt sbspace1
486016b8 5 0x42001 5 1 16384 N TBA gbasedbt tmpdbs1
486018e8 6 0x42001 6 1 16384 N TBA gbasedbt tmpdbs2
48601b18 7 0x60001 7 2 16384 N BA gbasedbt datadbs1
48601d48 8 0x60001 8 1 16384 N BA gbasedbt datadbs2
48603028 9 0x60001 9 1 16384 N BA gbasedbt datadbs3
48603258 10 0x60001 10 1 16384 N BA gbasedbt datadbs4
48603488 11 0x60001 11 1 16384 N BA gbasedbt datadbs5
486036b8 12 0x60001 12 1 16384 N BA gbasedbt datadbs6
12 active, 2047 maximum
Chunks
address chunk/dbs offset size free bpages flags pathname
46fe9258 1 1 0 102400 30666 PO-B-D /app/GBASE/gbase8s/gbaseserver_dbs/rootdbs
48604028 2 2 0 61440 1387 PO-B-D /app/GBASE/gbase8s/gbaseserver_dbs/llogdbs
48605028 3 3 0 61440 1747 PO-B-D /app/GBASE/gbase8s/gbaseserver_dbs/plogdbs
48606028 4 4 0 61440 57229 57229 POSB-D /app/GBASE/gbase8s/gbaseserver_dbs/sbspace1
Metadata 4158 3094 4158
48607028 5 5 0 3840 3787 PO-B-- /app/GBASE/gbase8s/gbaseserver_dbs/tmpdbs1
4860d028 6 6 0 3840 3787 PO-B-- /app/GBASE/gbase8s/gbaseserver_dbs/tmpdbs2
4860e028 7 7 0 6400 5817 PO-BED /app/GBASE/gbase8s/gbaseserver_dbs/datadbs1_1
4860f028 8 8 0 6400 6347 PO-BED /app/GBASE/gbase8s/gbaseserver_dbs/datadbs2_1
48610028 9 9 0 6400 6347 PO-BED /app/GBASE/gbase8s/gbaseserver_dbs/datadbs3_1
48611028 10 10 0 6400 6347 PO-BED /app/GBASE/gbase8s/gbaseserver_dbs/datadbs4_1
48612028 11 11 0 6400 6347 PO-BED /app/GBASE/gbase8s/gbaseserver_dbs/datadbs5_1
48613028 12 12 0 6400 6347 PO-BED /app/GBASE/gbase8s/gbaseserver_dbs/datadbs6_1
48614028 13 7 0 655360 655357 PO-B-D /app/GBASE/gbase8s/gbaseserver_dbs/datadbs1_2
13 active, 32766 maximum
NOTE: The values in the "size" and "free" columns for DBspace chunks are
displayed in terms of "pgsize" of the DBspace to which they belong.
Expanded chunk capacity mode: always
onstat -d
Your evaluation license will expire on 2026-09-04 00:00:00
On-Line -- Up 01:19:29 -- 597864 Kbytes
Dbspaces
address number flags fchunk nchunks pgsize flags owner name
46fe9028 1 0x70001 1 1 2048 N BA gbasedbt rootdbs
48601028 2 0x60001 2 1 2048 N BA gbasedbt llogdbs
48601258 3 0x70001 3 1 2048 N BA gbasedbt plogdbs
48601488 4 0x68001 4 1 2048 N SBA gbasedbt sbspace1
486016b8 5 0x42001 5 1 16384 N TBA gbasedbt tmpdbs1
486018e8 6 0x42001 6 1 16384 N TBA gbasedbt tmpdbs2
48601b18 7 0x60001 7 2 16384 N BA gbasedbt datadbs1
48601d48 8 0x60001 8 1 16384 N BA gbasedbt datadbs2
48603028 9 0x60001 9 1 16384 N BA gbasedbt datadbs3
48603258 10 0x60001 10 1 16384 N BA gbasedbt datadbs4
48603488 11 0x60001 11 1 16384 N BA gbasedbt datadbs5
486036b8 12 0x60001 12 1 16384 N BA gbasedbt datadbs6
12 active, 2047 maximum
Chunks
address chunk/dbs offset size free bpages flags pathname
46fe9258 1 1 0 102400 30666 PO-B-D /app/GBASE/gbase8s/gbaseserver_dbs/rootdbs
48604028 2 2 0 61440 1387 PO-B-D /app/GBASE/gbase8s/gbaseserver_dbs/llogdbs
48605028 3 3 0 61440 1747 PO-B-D /app/GBASE/gbase8s/gbaseserver_dbs/plogdbs
48606028 4 4 0 61440 57229 57229 POSB-D /app/GBASE/gbase8s/gbaseserver_dbs/sbspace1
Metadata 4158 3094 4158
48607028 5 5 0 3840 3787 PO-B-- /app/GBASE/gbase8s/gbaseserver_dbs/tmpdbs1
4860d028 6 6 0 3840 3787 PO-B-- /app/GBASE/gbase8s/gbaseserver_dbs/tmpdbs2
4860e028 7 7 0 6400 5817 PO-BED /app/GBASE/gbase8s/gbaseserver_dbs/datadbs1_1
4860f028 8 8 0 6400 6347 PO-BED /app/GBASE/gbase8s/gbaseserver_dbs/datadbs2_1
48610028 9 9 0 6400 6347 PO-BED /app/GBASE/gbase8s/gbaseserver_dbs/datadbs3_1
48611028 10 10 0 6400 6347 PO-BED /app/GBASE/gbase8s/gbaseserver_dbs/datadbs4_1
48612028 11 11 0 6400 6347 PO-BED /app/GBASE/gbase8s/gbaseserver_dbs/datadbs5_1
48613028 12 12 0 6400 6347 PO-BED /app/GBASE/gbase8s/gbaseserver_dbs/datadbs6_1
48614028 13 7 0 655360 655357 PO-B-D /app/GBASE/gbase8s/gbaseserver_dbs/datadbs1_2
13 active, 32766 maximum
NOTE: The values in the "size" and "free" columns for DBspace chunks are
displayed in terms of "pgsize" of the DBspace to which they belong.
Expanded chunk capacity mode: always
liaosnet发表于 10个月前
@梦之泪殇严肃版：48607028 5 5 0 3840 3787 PO-B-- /app/GBASE/gbase8s/gbaseserver_dbs/tmpdbs1
4860d028 6 6 0 3840 3787 PO-B-- /app/GBASE/gbase8s/gbaseserver_dbs/tmpdbs2
这两个空间是临时空间，看着的确比较小啊。。你这个是典型安装的。默认空间都比较小。
在sysadmin库执行
execute function task('modify chunk extendable on',5); --使用 chunk号
execute function task('modify chunk extend',5,2048000)； -- 使用chunk号，大小的单位是KB
execute function task('modify chunk extendable on',6); --使用 chunk号
execute function task('modify chunk extend',6,2048000)； -- 使用chunk号，大小的单位是KB，这里是增加2GB
4860d028 6 6 0 3840 3787 PO-B-- /app/GBASE/gbase8s/gbaseserver_dbs/tmpdbs2
这两个空间是临时空间，看着的确比较小啊。。你这个是典型安装的。默认空间都比较小。
在sysadmin库执行
execute function task('modify chunk extendable on',5); --使用 chunk号
execute function task('modify chunk extend',5,2048000)； -- 使用chunk号，大小的单位是KB
execute function task('modify chunk extendable on',6); --使用 chunk号
execute function task('modify chunk extend',6,2048000)； -- 使用chunk号，大小的单位是KB，这里是增加2GB
梦之泪殇严肃版发表于 10个月前
@liaosnet：查询不报错了，但是非常的慢，需要增加什么配置，具体的执行计划：
select e.id as eid,
e.plan_id,
e.record_id,
e.com_name,
e.com_address,
e.com_man,
e.com_man_job,
e.com_man_phone,
e.address,
e.record_sig,
e.comp_sig,
e.end_time,
e.record_type,
e.com_id,
e.com_and_man,
e.com_and_position,
e.record_state_info,
e.submit_type,
e.start_time_qua,
e.end_time_qua,
e.change_time,
h.id as hid,
h.hidden_text,
h.src,
h.record_id as hrecordid,
h.rec_type as hrectype,
h.com_type as hcomtype,
h.com_id as hcomid,
h.rev_src as revsrc,
h.hidden_type as hiddentype,
h.hidden_people as hiddenpeople,
h.finish_type as finishType
from experts_record e
LEFT JOIN experts_record_hidden h ON e.ID = h.record_id
where e.com_id = '130e5cf49de54cd2b39a17768794c883'
and e.plan_id = '3b3ebd6db7414337a0d46afece751449';
执行计划：
QUERY: (OPTIMIZATION TIMESTAMP: 09-19-2025 15:06:11)
------
select e.id as eid,
e.plan_id,
e.record_id,
e.com_name,
e.com_address,
e.com_man,
e.com_man_job,
e.com_man_phone,
e.address,
e.record_sig,
e.comp_sig,
e.end_time,
e.record_type,
e.com_id,
e.com_and_man,
e.com_and_position,
e.record_state_info,
e.submit_type,
e.start_time_qua,
e.end_time_qua,
e.change_time,
h.id as hid,
h.hidden_text,
h.src,
h.record_id as hrecordid,
h.rec_type as hrectype,
h.com_type as hcomtype,
h.com_id as hcomid,
h.rev_src as revsrc,
h.hidden_type as hiddentype,
h.hidden_people as hiddenpeople,
h.finish_type as finishType
from experts_record e
LEFT JOIN experts_record_hidden h ON e.ID = h.record_id
where e.com_id = '130e5cf49de54cd2b39a17768794c883'
and e.plan_id = '3b3ebd6db7414337a0d46afece751449'
Estimated Cost: 22697
Estimated # of Rows Returned: 6519
1) gbasedbt.e: SEQUENTIAL SCAN
Filters: (gbasedbt.e.plan_id = '3b3ebd6db7414337a0d46afece751449' AND gbasedbt.e.com_id = '130e5cf49de54cd2b39a17768794c883' )
2) gbasedbt.h: SEQUENTIAL SCAN
DYNAMIC HASH JOIN
Dynamic Hash Filters: gbasedbt.e.id = gbasedbt.h.record_id
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 e
t2 h
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 1 29 2922 00:00.93 1308
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 22479 22479 22479 00:00.76 5732
type rows_prod est_rows rows_bld rows_prb novrflo time est_cost
------------------------------------------------------------------------------
hjoin 6 6519 22479 1 11 02:26.14 22697
QUERY: (OPTIMIZATION TIMESTAMP: 09-19-2025 15:09:27)
------
select e.id as eid,
e.plan_id,
e.record_id,
e.com_name,
e.com_address,
e.com_man,
e.com_man_job,
e.com_man_phone,
e.address,
e.record_sig,
e.comp_sig,
e.end_time,
e.record_type,
e.com_id,
e.com_and_man,
e.com_and_position,
e.record_state_info,
e.submit_type,
e.start_time_qua,
e.end_time_qua,
e.change_time,
h.id as hid,
h.hidden_text,
h.src,
h.record_id as hrecordid,
h.rec_type as hrectype,
h.com_type as hcomtype,
h.com_id as hcomid,
h.rev_src as revsrc,
h.hidden_type as hiddentype,
h.hidden_people as hiddenpeople,
h.finish_type as finishType
from experts_record e
LEFT JOIN experts_record_hidden h ON e.ID = h.record_id
where e.com_id = '130e5cf49de54cd2b39a17768794c883'
and e.plan_id = '3b3ebd6db7414337a0d46afece751449'
Estimated Cost: 22697
Estimated # of Rows Returned: 6519
1) gbasedbt.e: SEQUENTIAL SCAN
Filters: (gbasedbt.e.plan_id = '3b3ebd6db7414337a0d46afece751449' AND gbasedbt.e.com_id = '130e5cf49de54cd2b39a17768794c883' )
2) gbasedbt.h: SEQUENTIAL SCAN
DYNAMIC HASH JOIN
Dynamic Hash Filters: gbasedbt.e.id = gbasedbt.h.record_id
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 e
t2 h
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 1 29 2922 00:00.12 1308
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 22479 22479 22479 00:00.49 5732
type rows_prod est_rows rows_bld rows_prb novrflo time est_cost
------------------------------------------------------------------------------
hjoin 6 6519 22479 1 11 00:50.09 22697
select e.id as eid,
e.plan_id,
e.record_id,
e.com_name,
e.com_address,
e.com_man,
e.com_man_job,
e.com_man_phone,
e.address,
e.record_sig,
e.comp_sig,
e.end_time,
e.record_type,
e.com_id,
e.com_and_man,
e.com_and_position,
e.record_state_info,
e.submit_type,
e.start_time_qua,
e.end_time_qua,
e.change_time,
h.id as hid,
h.hidden_text,
h.src,
h.record_id as hrecordid,
h.rec_type as hrectype,
h.com_type as hcomtype,
h.com_id as hcomid,
h.rev_src as revsrc,
h.hidden_type as hiddentype,
h.hidden_people as hiddenpeople,
h.finish_type as finishType
from experts_record e
LEFT JOIN experts_record_hidden h ON e.ID = h.record_id
where e.com_id = '130e5cf49de54cd2b39a17768794c883'
and e.plan_id = '3b3ebd6db7414337a0d46afece751449';
执行计划：
QUERY: (OPTIMIZATION TIMESTAMP: 09-19-2025 15:06:11)
------
select e.id as eid,
e.plan_id,
e.record_id,
e.com_name,
e.com_address,
e.com_man,
e.com_man_job,
e.com_man_phone,
e.address,
e.record_sig,
e.comp_sig,
e.end_time,
e.record_type,
e.com_id,
e.com_and_man,
e.com_and_position,
e.record_state_info,
e.submit_type,
e.start_time_qua,
e.end_time_qua,
e.change_time,
h.id as hid,
h.hidden_text,
h.src,
h.record_id as hrecordid,
h.rec_type as hrectype,
h.com_type as hcomtype,
h.com_id as hcomid,
h.rev_src as revsrc,
h.hidden_type as hiddentype,
h.hidden_people as hiddenpeople,
h.finish_type as finishType
from experts_record e
LEFT JOIN experts_record_hidden h ON e.ID = h.record_id
where e.com_id = '130e5cf49de54cd2b39a17768794c883'
and e.plan_id = '3b3ebd6db7414337a0d46afece751449'
Estimated Cost: 22697
Estimated # of Rows Returned: 6519
1) gbasedbt.e: SEQUENTIAL SCAN
Filters: (gbasedbt.e.plan_id = '3b3ebd6db7414337a0d46afece751449' AND gbasedbt.e.com_id = '130e5cf49de54cd2b39a17768794c883' )
2) gbasedbt.h: SEQUENTIAL SCAN
DYNAMIC HASH JOIN
Dynamic Hash Filters: gbasedbt.e.id = gbasedbt.h.record_id
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 e
t2 h
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 1 29 2922 00:00.93 1308
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 22479 22479 22479 00:00.76 5732
type rows_prod est_rows rows_bld rows_prb novrflo time est_cost
------------------------------------------------------------------------------
hjoin 6 6519 22479 1 11 02:26.14 22697
QUERY: (OPTIMIZATION TIMESTAMP: 09-19-2025 15:09:27)
------
select e.id as eid,
e.plan_id,
e.record_id,
e.com_name,
e.com_address,
e.com_man,
e.com_man_job,
e.com_man_phone,
e.address,
e.record_sig,
e.comp_sig,
e.end_time,
e.record_type,
e.com_id,
e.com_and_man,
e.com_and_position,
e.record_state_info,
e.submit_type,
e.start_time_qua,
e.end_time_qua,
e.change_time,
h.id as hid,
h.hidden_text,
h.src,
h.record_id as hrecordid,
h.rec_type as hrectype,
h.com_type as hcomtype,
h.com_id as hcomid,
h.rev_src as revsrc,
h.hidden_type as hiddentype,
h.hidden_people as hiddenpeople,
h.finish_type as finishType
from experts_record e
LEFT JOIN experts_record_hidden h ON e.ID = h.record_id
where e.com_id = '130e5cf49de54cd2b39a17768794c883'
and e.plan_id = '3b3ebd6db7414337a0d46afece751449'
Estimated Cost: 22697
Estimated # of Rows Returned: 6519
1) gbasedbt.e: SEQUENTIAL SCAN
Filters: (gbasedbt.e.plan_id = '3b3ebd6db7414337a0d46afece751449' AND gbasedbt.e.com_id = '130e5cf49de54cd2b39a17768794c883' )
2) gbasedbt.h: SEQUENTIAL SCAN
DYNAMIC HASH JOIN
Dynamic Hash Filters: gbasedbt.e.id = gbasedbt.h.record_id
Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 e
t2 h
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 1 29 2922 00:00.12 1308
type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t2 22479 22479 22479 00:00.49 5732
type rows_prod est_rows rows_bld rows_prb novrflo time est_cost
------------------------------------------------------------------------------
hjoin 6 6519 22479 1 11 00:50.09 22697
liaosnet发表于 10个月前
@梦之泪殇严肃版：确认一下experts_record上com_id,plan_id,id上有没有索引，experts_record_hidden的record_id上有没有索引。
GBase用户28017发表于 9个月前
关联。
崔哥发表于 6个月前
千点寒梅晓角中，一番春信画楼东。收灯庭院迟迟月，落索秋千翦翦风。鱼雁杳，水云重，异乡节序恨匆匆。当歌幸有金陵子，翠斝清尊莫放空。
热门帖子
- 12025-12-01浏览数：182759
- 22023-05-09浏览数：25052
- 42023-09-25浏览数：18521
- 52020-05-11浏览数：17526