oceanbase-oracle模式 ， 会话级临时表批量插入数据特别慢

OceanBase 技术问题
#1

oceanbase版本 ： 4.2.1.6

INSERT INTO T_BUDGET_TEMP_DIM_GROUP
(ID,DIM_TYPE_COLUMN, DIM_OBJECT_ID, DIM_OBJECT_PATH, DIM_OBJECT_CODE)
SELECT lower(sys_guid()) AS ID,
TY.CODE AS DIM_TYPE_COLUMN,
TD.OBJECT_ID AS DIM_OBJECT_ID,
TD.PATH AS DIM_OBJECT_PATH,
TD.CODE AS DIM_OBJECT_CODE
FROM T_DIM_OBJECT TD
LEFT JOIN T_DIM_OBJECT TY ON TD.TYPE_ID = TY.OBJECT_ID
WHERE (

                    TD.PATH LIKE ('10015,00001,00004,00020') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00021') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00022') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00023') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00001') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00006') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00028') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00007') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00029') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00008') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00009') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00024') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00002') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00025') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00003') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00004') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00026') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00027') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00005') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00003') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00031') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00010') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00032') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00033') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00011') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00034') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00012') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00030') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00017') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00018') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00019') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00035') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00013') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00014') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00036') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00015') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00037') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00038') || '%'
                 OR 
                    TD.PATH LIKE ('10015,00001,00004,00016') || '%'
                
            
                 OR 
                    TD.PATH LIKE ('10002,00006,00007,255807') || '%'
                 OR 
                    TD.PATH LIKE ('10002,00006,00006,395733') || '%'
                 OR 
                    TD.PATH LIKE ('10002,00006,00006,395732') || '%'
                 OR 
                    TD.PATH LIKE ('10002,00006,00007,255809') || '%'
                 OR 
                    TD.PATH LIKE ('10002,00006,00006,395734') || '%'
                 OR 
                    TD.PATH LIKE ('10002,00006,00006,395731') || '%'
                 OR 
                    TD.PATH LIKE ('10002,00006,00008,310643') || '%'
                
            
            )

该sql执行相应查询大约3s左右 ， 执行insert却要20多s ； 按理说oracle和mysql是秒级呢 ；
#3

看看后台执行日志,学习下

#4

建议看看select部分语句执行计划，这个语句慢肯定也是慢在select上面。

#5

你好，你提的这个技术问题牵涉到OceanBase企业版范围内的功能细节；针对此类问题，建议你通过以下方式寻求帮助：

  1. 如你所在的企业客户已签署OceanBase企业版销售合同，请你联系客户经理；

  2. 如你所在的企业客户尚未签署OceanBase企业版销售合同，你可通过OceanBase官网商务咨询页面留下你的联系方式，OceanBase企业版的业务顾问会在一个工作日内与你联系。

另外，我们欢迎你使用社区版，并在论坛/社群中分享你对社区版本的想法、经验和问题，与其他社区成员共同交流。

#6

select不慢 ， 2s ， 整个插入后30多s

#7

select语句之前的部分改为这样试试

INSERT /*+ parallel(4) enable_parallel_dml */
INTO T_BUDGET_TEMP_DIM_GROUP (ID, DIM_TYPE_COLUMN, DIM_OBJECT_ID, DIM_OBJECT_PATH, DIM_OBJECT_CODE)

1 个赞
#8

请问 ， 有没有不开这种并行方式的解决方案

#9

企业版本完整处理方案建议咨询工单